Convertir JSON array en MySQL a filas

[*]Te doy la bienvenida a proyecto on line, en este sitio hallarás la resolución de lo que buscabas.

Solución:

[*]Es true que no es una buena idea desnormalizar a JSON, pero a veces necesita tratar con datos JSON, y hay una manera de extraer un JSON array en filas en una consulta.

[*]El truco consiste en realizar una unión en una tabla de índices temporal o en línea, lo que le da una fila para cada uno que no esnull valor en un JSON array. Es decir, si tiene una tabla con valores 0, 1 y 2 que une a un JSON array “fish” con dos entradas, luego fish[0] coincide con 0, lo que da como resultado una fila, y fish1 coincide con 1, lo que da como resultado una segunda fila, pero fish[2] es null por lo que no coincide con el 2 y no produce una fila en la combinación. Necesita tantos números en la tabla de índice como la longitud máxima de cualquier array en sus datos JSON. Es un poco complicado, y es tan doloroso como el ejemplo del OP, pero es muy útil.

[*]Ejemplo (requiere MySQL 5.7.8 o posterior):

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;

[*]El resultado es:

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

[*]Parece que el equipo de MySQL puede agregar un JSON_TABLE función en MySQL 8 para hacer todo esto más fácil. (http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/) (El equipo de MySQL posee agregó un JSON_TABLE función.)

[*]He aquí cómo hacer esto con JSON_TABLE en MySQL 8+:

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

[*]También puede usar esto como un general string dividir la función de la que MySQL carece de otro modo (similar a PG’s regexp_split_to_table o MSSQL’s STRING_SPLIT) tomando un delimitado string y convertirlo en un JSON string:

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

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

[*]En 2018. Lo que hago por este caso.

1. [*]Prepare una tabla con números continuos en filas.

   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. [*]Disfruta de JSON fácil array a las filas en el futuro.

   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);
   

[*]Por este camino. es una especie de manera 'Chris Hynes'. pero no necesitas saber array Talla.

[*]Bueno: código claro, corto y fácil, sin necesidad de saber array El tamaño, sin bucle, sin invocar otra función será rápido.

[*]Malo: necesitas una tabla más con suficientes filas.

[*]Agradecemos que desees añadir valor a nuestro contenido informacional dando tu veteranía en las interpretaciones.

---

---

Utiliza Nuestro Buscador