Rewrite the title as: Convert JSON array to rows in MySQL

Question

Update: This is now possible in MySQL 8 via the JSON_TABLE function: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions. html< /p>

I like the new JSON functions in MySQL 5.7, but I'm having trouble trying to merge values ​​from JSON into a normal table structure.

Getting JSON, manipulating it, extracting arrays from it, and more is easy. JSON_EXTRACT all the way. But what about the other way around, from JSON array to row? Maybe I'm interested in the existing MySQL JSON functionality, but I haven't been able to figure that out.

For example, suppose I have a JSON array and want to insert a row for each element in the array and its value? The only way I've found is to write a bunch of JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') etc and join them together.

Alternatively, suppose I have a JSON array and want to GROUP_CONCAT() it into a single comma-separated string?

In other words, I know I can do this:

SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val
  FROM
  (
    SELECT 0 AS n
    UNION
    SELECT 1 AS n
    UNION
    SELECT 2 AS n
    UNION
    SELECT 3 AS n
    UNION
    SELECT 4 AS n
    UNION
    SELECT 5 AS n
  ) x
WHERE x.n < JSON_LENGTH(@j);

But it hurts my eyes. And my heart.

How can I do something like this:

SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, '$[ * ]'))

...and concatenate the values ​​in the array with the JSON array itself?

I think what I'm looking for here is some kind of JSON_SPLIT , something like:

SET @j = '[1, 2, 3]';

SELECT GROUP_CONCAT(val)
FROM
  JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')

If MySQL had a proper STRING_SPLIT(val, 'separator') table-returning function, I could hack it (the damn escape), but that doesn't work either.

Answer 1

It's true that denormalizing to JSON is not a good idea, but sometimes you need to deal with JSON data, and there is a way to extract a JSON array into rows in a query.

The trick is to perform a join on a temporary or inline index table, which gives you a row for each non-null value in the JSON array. That is, if you have a table with values ​​0, 1, and 2, and you join it to a JSON array "fish" that contains two entries, then Fish[0] matches 0, resulting in a row, and Fish1 matches 1, resulting in the second row, but fish[2] is null, so it does not match 2 and no row is produced in the join. You need as many numbers in the index table as the maximum length of any array in the JSON data. This is a bit of a hack, and as painful as the OP's example, but it's very convenient.

Example (requires MySQL 5.7.8 or higher):

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;

turn out:

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

Looks like the MySQL team may be adding a JSON_TABLE function in MySQL 8 to make this easier. (http://mysqlserverteam.com/mysql-8-0-labs -json-aggregation-functions/) (MySQL Teamhas addedJSON_TABLE function.)

Answer 2

Here's how to use JSON_TABLE in MySQL 8:

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

You can also use it as a general-purpose string splitting function that MySQL lacks (similar to PG's regexp_split_to_table or MSSQL's STRING_SPLIT) by taking the delimited string and converting it to a JSON string:

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

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