Convert JSON array in MySQL to strings

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 like the new JSON functions in MySQL 5.7, but I come across a block trying to combine values ​​from JSON into a regular table structure.

Grab JSON, manipulate and extract arrays from it, etc. Very simple. JSON_EXTRACT completely. But what about backtracking from a JSON array to strings? Maybe I understand the existing MySQL JSON functionality, but I could not figure it out.

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

Or, say, I have a JSON array and I want GROUP_CONCAT () to represent it on a single line, separated by commas?

In other words, I know I can do this:

SET @j = '[1, 2, 3]'; SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', xn, ']'))) 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 xn < JSON_LENGTH(@j); 

But it hurts my eyes. And my heart.

How can I do something like:

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

... and does it combine the values ​​in the array with the JSON array itself?

I think I'm looking here for something like JSON_SPLIT according to:

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

If MySQL had the correct table return function STRING_SPLIT (val, 'separator'), I could hack it (avoiding being damned), but it is also not available.

Answer 1

Here's how to do it 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 regular line-splitting function, which MySQL otherwise lacks (like PG regexp_split_to_table or MSSQL 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; 

Answer 2

It is true that it is not recommended to denormalize JSON, but sometimes you need to deal with JSON data and there is a way to extract the JSON array into strings in the request.

The trick is to make a join in a temporary or inline index table, which gives you a row for each non-empty value in the JSON array. That is, if you have a table with values ​​0, 1, and 2 that you attach to the JSON "fish" array with two entries, then fish [0] matches 0, which leads to one row, and fish [1] matches 1 , the result is the second line, but the fish [2] is zero, so it does not match 2 and does not create a line in the connection. You need as many numbers in the index table as the maximum length of any array in your JSON data. It's a bit of a hack, and it's about as painful as an OP example, but it's very convenient.

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; 

Result:

 +---------+-----+---------+ | 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 can add the JSON_TABLE function in MySQL 8 to make it all easier. ( http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/ )

Answer 3

In 2018. What am I doing for this occasion.

1. Prepare a table with a constant 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 a simple JSON array for strings 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. it's something like Chris Hines. but you do not need to know the size of the array.

Good: clear, short, simple code, no need to know the size of the array, without a loop, without calling another function, it will be fast.

Bad: you need another table with enough rows.

Answer 4

In my case, the JSON function is not available, so I used a hack. As already mentioned, Chris MYSQL does not have STRING_SPLIT , but has substring_index .

To enter

 { "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 change according to your requirements.

Answer 5

I worked in a report where in one column there was a large list of json arrays. I modified the datamodel to maintain a 1 to * relationship instead of just storing in one column. To perform this process, I had to use some time in the 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 ;