Convert JSON array in MySQL to rows

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

I'm loving the new JSON functions in MySQL 5.7, but running into a block trying to merge values from JSON into a normal table structure.

Grabbing JSON, manipulating and extracting arrays from it etc. is simple. JSON_EXTRACT all the way. But what about the inverse, going from a JSON array to rows? Perhaps I am dense on the existing MySQL JSON functionality, but I haven't been able to figure that one out.

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

Or, say I have a JSON array and want to GROUP_CONCAT() it to 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 that hurts my eyes. And my heart.

How can I do something like:

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

... and have it concatenate together the values in the array vs. the JSON array itself?

I guess what I'm looking for here is some sort of JSON_SPLIT along the lines of:

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 (escaping be damned), but that's not available either.

Paroicous

I don't think you can do it, for the same reason you can't do SPLIT_STRING(): A query can't create multiple rows from the same row an input table without a join. – Outlaw

Yeah, you might be right. I had assumed that table valued functions were supported, since pretty much every other DBMS under the sun has them. Apparently MySQL's the odd man out on that. For example, SQL Server has a perfectly good STRING_SPLIT: msdn.microsoft.com/en-us/library/mt684588.aspx. Postgress even has a split by regex in regexp_split_to_table. Ahh, MySQL... – Paroicous

Right. MySQL has no array-like data structure other than tables. The JSON functions should not be considered blanket license to denormalize your schema. – Outlaw

Well, if there were table valued functions like other DBMS have, then it would be a table returned, not some other array-like structure... and you could select FROM the function. – Paroicous

Nope. bugs.mysql.com/bug.php?id=79052 – Whitmire

I just got a requirement to do a report that requires some data filtering from a column where there was a json array on it. I quickly realized that what it is the point of storing a json 'list' in one single column?. We are throwing away the whole point of having a relational model. I had to redesign the model and fix this data into 1 to many relationship. I believe there is no such function since it does not make sense to have one. – Janae

Answer 1

Here's how to do this with JSON_TABLE in MySQL 8+:

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

You can also use this as a general string split function which MySQL otherwise lacks (similar to PG's regexp_split_to_table or MSSQL's STRING_SPLIT) by taking a delimited string and turning it into a JSON string:

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

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

Paroicous

I've been trying to do this but using a json array that exists in one table, it is not working though. SELECT * FROM JSON_TABLE( (SELECT j_array from array_to_col_b where id = 1), '$[*]' COLUMNS(Value INT PATH "$") ) data; Why is it that trying to split from a JSON stored in the db does not work? DO you have any idea? – Inappreciative

Answer 2

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

The trick is to perform a join on a temporary or inline table of indexes, which gives you a row for each non-null value in a JSON array. I.e., if you have a table with values 0, 1, and 2 that you join to a JSON array “fish” with two entries, then fish[0] matches 0, resulting in one row, and fish1 matches 1, resulting in a second row, but fish[2] is null so it doesn't match the 2 and doesn't produce a row in the join. You need as many numbers in the index table as the max length of any array in your JSON data. It's a bit of a hack, and it's about as painful as the OP's example, but it's very handy.

Example (requires MySQL 5.7.8 or later):

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;

The result is:

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

It looks like the MySQL team may add a JSON_TABLE function in MySQL 8 to make all this easier. (http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/) (The MySQL team has added a JSON_TABLE function.)

Piling

Yep, that's basically the same as what I had as the first example in my question. Works, but is ugly and you need to replicate the UNION a bunch of times. Basic problem here is that MySQL doesn't support table valued functions, not built-ins, not user defined. Hoping they add JSON_TABLE and STRING_SPLIT in MySQL 8, and also allow additional user defined table valued functions to fill in the gap. – Paroicous

I am facing a similar situation where I need to convert a JSON array eg.{"2018Apr": "1000", "2018Jun": "7000", "2018May": "2000"} to rows such as : Date Price 2018Apr 1000 2018May 2000 2018Jun 7000 but the issue i face with this is that my array's length is variable (i.e can be 'n' elements in the array). In such a situation what would you recommend ? @Piling – Plebs

@Veer3383, the hack above will work as long as you have enough records in the inline index table to match the maximum length of your JSON array. And you don't have to use an inline table. If there could be, for example, up to 5,000 elements in your array, pre-generate a one-column index table with values from 0 to 4,999 and use that in the JOIN. – Piling

This worked for me on a pre MYSQL 8 environment but I had to add ` and json_type (json_extract(jdoc, '$.fish')) != 'NULL'` to fix cases where there was nothing in the array – Inferno

Answer 3

In 2018. What I do for this case.

1. Prepare a table with just continually number in rows.

   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. Enjoy easy JSON array to rows in the future.

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

For this way. is some kind like 'Chris Hynes' way. but you don't need to know array size.

Good: Clear, short, easy code, no need to know array size, no loop, no invoke other function will be fast.

Bad: You need one more table with enough rows.

Bigmouth

Such "integers" table is always useful in MySQL (as it cannot be generated on the fly like Oracle does AFAIR), but did you find a way to get ride of the CONCAT part too? – Tosspot

@Tosspot So far, No better way, Because that is how it works. JSON_EXTRACT(json_doc, path[, path] ...) – Bigmouth

If you have a large enough table at hand then insert into numbers select @row := @row + 1 from big_table join (select @row:=0) t2 limit 50; will generate the numbers table for you. – Synonym

Here are sql queries for generating these t_list_row tables in different 1000, 10000 and 100000 sizes: gist.github.com/milosb793/812d5e7c33a0bfd37ed2c3dcad0cea1c – Desk

Answer 4

simple example:

select subtotal, sku
from t1,
     json_table(t1.refund_line_items,
                '$[*]' columns (
                    subtotal double path '$.subtotal',
                    sku char(50) path '$.line_item.sku'
                    )
         ) refunds

Marla

even when using json_table, please use the explicit join syntax (t1 join json_table...), not the deprecated comma join – Presidentship

Answer 5

For MySQL 8+, see this answer.

For older versions, this is how I do it:

1. Create a new table pseudo_rows with values from 0 until 99 - these will be used as keys (if your array has more than a hundred values, add more values into pseudo_rows).

NOTE: If you're running MariaDB, you can skip this and simply use pseudo Sequence tables (e.g. seq_0_to_99).

CREATE TABLE `pseudo_rows` (
  `row` int(10) unsigned NOT NULL,
  PRIMARY KEY (`row`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT pseudo_rows VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92), (93), (94), (95), (96), (97), (98), (99)

2. For this example, I'll be using a table events which stores groups of artists:

CREATE TABLE `events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `artists` json DEFAULT NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `events` (`id`, `artists`) VALUES ('1', '[{\"id\": 123, \"name\": \"Pink Floyd\"}]');
INSERT INTO `events` (`id`, `artists`) VALUES ('2', '[{\"id\": 456, \"name\": \"Nirvana\"}, {\"id\": 789, \"name\": \"Eminem\"}]');

The query to get all artists, one per row, is as follows:

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(events.artists, CONCAT('$[', pseudo_rows.row, '].name'))) AS performer
FROM events
JOIN pseudo_rows
HAVING performer IS NOT NULL

And the resultset is:

performer
---------
Pink Floyd
Nirvana
Eminem

Ascogonium

Answer 6

If you can't use the JSON_TABLE function, but can use recursive CTE's, you can do the following:

SET @j = '[1, 2, 3]';
WITH RECURSIVE x AS (
    /* Anchor, start at -1 in case empty array */
    SELECT -1 AS n

    UNION

    /* Append indexes up to the length of the array */
    SELECT x.n + 1
    FROM x
    WHERE x.n < JSON_LENGTH(@j) - 1
)
/* Use the table of indexes to extract each item and do your GROUP_CONCAT */ 
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']')))
FROM x
/* This prevents selecting from empty array */
WHERE x.n >= 0

This generates a table of sequential indexes for each array item, which you can use to grab the value using JSON_EXTRACT.

Tailwind

Answer 7

In My Case, JSON Function was not available so I used a hack. As mentioned by Chris MYSQL do not have STRING_SPLIT but it does have substring_index.

For the input

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

You can use:

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

The output will be:

BARBH17319901529

You can modify according to your requirement.

Peursem

This assumes that requestid will not appear earlier in the serialized data (I wouldn't rely on JSON serializer's field order, aka "source": "AxxxSrequestid" could appear before the "requestId":"BAR..." part and break this quick parser – Tosspot

Answer 8

I was working in a report where there was a big json array list in one column. I modified the datamodel to store the relationship 1 to * instead of storing everything in one single column. For doing this process, I had to use a while in a stored procedure since I do not know the maximum size:

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 ;

Janae