Преобразование массива JSON в MySQL в строки

Ответ 1

Вот как это сделать с помощью JSON_TABLE в MySQL 8+:

SELECT *
     FROM
       JSON_TABLE(
         '[5, 6, 7]',
         "$[*]"
         COLUMNS(
           Value INT PATH "$"
         )
       ) data;

Вы также можете использовать это как обычную функцию разделения строк, которой в MySQL в противном случае не хватает (подобно PG regexp_split_to_table или MSSQL STRING_SPLIT), взяв строку с разделителями и превратив ее в строку JSON:

set @delimited = 'a,b,c';

SELECT *
     FROM
       JSON_TABLE(
         CONCAT('["', REPLACE(@delimited, ',', '", "'), '"]'),
         "$[*]"
         COLUMNS(
           Value varchar(50) PATH "$"
         )
       ) data;

Ответ 2

Это правда, что не рекомендуется денормализовать JSON, но иногда вам нужно иметь дело с данными JSON и есть способ извлечь массив JSON в строки в запросе.

Хитрость заключается в выполнении соединения во временной или встроенной таблице индексов, которая дает вам строку для каждого непустого значения в массиве JSON. То есть, если у вас есть таблица со значениями 0, 1 и 2, которые вы присоединяете к массиву JSON "fish" с двумя записями, тогда рыба [0] соответствует 0, что приводит к одной строке, а рыба [1] соответствует 1, в результате получается вторая строка, но рыба [2] равна нулю, поэтому она не соответствует 2 и не создает строку в соединении. Вам нужно столько чисел в таблице индексов, сколько максимальная длина любого массива в ваших данных JSON. Это немного взломать, и это примерно так же болезненно, как пример OP, но это очень удобно.

Пример (требуется MySQL 5.7.8 или новее):

CREATE TABLE t1 (rec_num INT, jdoc JSON);
INSERT INTO t1 VALUES 
  (1, '{"fish": ["red", "blue"]}'), 
  (2, '{"fish": ["one", "two", "three"]}');

SELECT
  rec_num,
  idx,
  JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) AS fishes
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  3
  ) AS indexes
WHERE JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) IS NOT NULL
ORDER BY rec_num, idx;

Результат:

+---------+-----+---------+
| rec_num | idx | fishes  |
+---------+-----+---------+
|       1 |   0 | "red"   |
|       1 |   1 | "blue"  |
|       2 |   0 | "one"   |
|       2 |   1 | "two"   |
|       2 |   2 | "three" |
+---------+-----+---------+

Похоже, команда MySQL может добавить функцию JSON_TABLE в MySQL 8, чтобы сделать все это проще. (http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/)

Ответ 3

В 2018 году. Что я делаю для этого случая.

1. Подготовьте таблицу с постоянным номером в строках.

   CREATE TABLE 't_list_row' (
   '_row' int(10) unsigned NOT NULL,
   PRIMARY KEY ('_row')
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
   
   INSERT t_list_row VALUES (0), (1), (2) .... (65535) big enough;
   

2. Наслаждайтесь простым массивом JSON для строк в будущем.

   SET @j = '[1, 2, 3]';
   SELECT 
   JSON_EXTRACT(@j, CONCAT('$[', B._row, ']'))
   FROM (SELECT @j AS B) AS A
   INNER JOIN t_list_row AS B ON B._row < JSON_LENGTH(@j);
   

Для этого пути. это что-то вроде "Криса Хайнса". но вам не нужно знать размер массива.

Хорошо: понятный, короткий, простой код, не нужно знать размер массива, без цикла, без вызова другой функции будет быстро.

Плохо: вам нужна еще одна таблица с достаточным количеством строк.

Ответ 4

В моем случае JSON Функция недоступна, поэтому я использовал хак. Как уже упоминалось, Chris MYSQL не имеет STRING_SPLIT, но имеет substring_index.

Для ввода

{
    "requestId":"BARBH17319901529",
    "van":"0xxxxx91317508",
    "source":"AxxxS",
    "txnTime":"15-11-2017 14:08:22"
}

Вы можете использовать:

trim(
    replace(
        substring_index(
            substring(input, 
                locate('requestid',input) 
                    + length('requestid') 
                    + 2), ',', 1), '"', '')
) as Requestid`

Выход будет:

BARBH17319901529

Вы можете изменить в соответствии с вашими требованиями.

Ответ 5

Я работал в отчете, где в одном столбце был большой список json-массивов. Я модифицировал datamodel для сохранения отношения 1 к * вместо хранения всего в одном столбце. Для выполнения этого процесса мне пришлось использовать некоторое время в хранимой процедуре, так как я не знаю максимальный размер:

DROP PROCEDURE IF EXISTS `test`;

DELIMITER #

CREATE PROCEDURE `test`()
PROC_MAIN:BEGIN
DECLARE numNotes int;
DECLARE c int;
DECLARE pos varchar(10);

SET c = 0;
SET numNotes = (SELECT 
ROUND (   
        (
            LENGTH(debtor_master_notes)
            - LENGTH( REPLACE ( debtor_master_notes, "Id", "") ) 
        ) / LENGTH("Id")        
    ) AS countt FROM debtor_master
order by countt desc Limit 1);

DROP TEMPORARY TABLE IF EXISTS debtorTable;
CREATE TEMPORARY TABLE debtorTable(debtor_master_id int(11), json longtext, note int);
WHILE(c <numNotes) DO
SET pos = CONCAT('$[', c, ']');
INSERT INTO debtorTable(debtor_master_id, json, note)
SELECT debtor_master_id, JSON_EXTRACT(debtor_master_notes, pos), c+1
FROM debtor_master
WHERE debtor_master_notes IS NOT NULL AND debtor_master_notes like '%[%' AND JSON_EXTRACT(debtor_master_notes, pos) IS NOT NULL AND JSON_EXTRACT(debtor_master_notes, pos) IS NOT NULL;
SET c = c + 1;
END WHILE;
SELECT * FROM debtorTable;
END proc_main #

DELIMITER ;