CODEWITHSUNDEEP
mysqlarraysflatten
Ijaz Ahmed
Ijaz Ahmed

Reputation: 2370

Mysql Flatten an array

The table contains json data and i wanted to extract labelKey from this json

table: d_json

data
{"tag":null,"options":[{"labelKey":"key10","value":"yes","selected":true},{"labelKey":"key11","value":"no","selected":false}]}
{"tag":null,"options":[{"labelKey":"key20","value":"yes","selected":true},{"labelKey":"key21","value":"no","selected":false},{"labelKey":"key22","value":"no","selected":false}]}

I used following query to extract "labelKey"

SELECT JSON_EXTRACT(JSON_EXTRACT(j.data,'$.options'),'$[*].labelKey') FROM d_json j AS result;

It returns following result

result
["key10", "key12"]
["key20", "key21", "key22"]

However i want result in flat, each row contains one element instead of array, such as

result
"key10"
"key11"
"key21"
"key22"
"key23"

Not getting any clue how to flatten resulted arrays

Upvotes: 1

Views: 2258

Answers (1)

FanoFN
FanoFN

Reputation: 7114

On mysql v8+, you can do that using JSON_TABLE function like this:

SELECT p.*
FROM d_json, 
     JSON_TABLE(data, '$.options[*]' COLUMNS (
                labelKey VARCHAR(40) PATH '$.labelKey')
     ) p;

Result:

labelKey
key10
key11
key20
key21
key22

Here's a demo fiddle

Edit:

On older MySQL version, try this:

SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(val2,',',rn),',',-1))
FROM (SELECT 1 rn UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS r
CROSS JOIN
  (SELECT REPLACE(REPLACE(GROUP_CONCAT(val),'[',''),']','') AS val2
     FROM 
       (SELECT JSON_EXTRACT(JSON_EXTRACT(j.data,'$.options'),'$[*].labelKey') AS val
      FROM d_json j) v1 
    ) v2;

Demo fiddle

The idea is to CROSS JOIN with a sequence of row numbers then use that same sequence to extract from a GROUP_CONCAT values using SUBSTRING_INDEX. In the query example above, I'm using a hardcoded row sequences in the form of:

(SELECT 1 rn UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS r

Ideally, the best approach is to figure out the sequence required and generate it dynamically.

Update:

Generating numbering sequences on older MySQL version is a challenge especially if we're aiming for a dynamic generating. There's a method that is not dynamic but can generate a large numbering sequence from a very long query but if you're planning to be using this sequences for a long time, I suggest you just create a table for it:

CREATE TABLE number_seq (
sequences INT);

INSERT INTO number_seq
SELECT @row := @row + 1 AS rn FROM 
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1 CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2 CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3 CROSS JOIN
(SELECT @row:=0) numbers;

The query above will generate a range of numbers from 1-1000 and insert into a table. Once you have that table, you just need to write your query like this:

SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(val2,',',sequences),',',-1))
FROM (SELECT sequences FROM 
(SELECT (LENGTH(val2)-LENGTH(REPLACE(val2,',','')))+1 AS valLen FROM
(SELECT REPLACE(REPLACE(GROUP_CONCAT(val),'[',''),']','') AS val2 FROM 
       (SELECT JSON_EXTRACT(JSON_EXTRACT(j.data,'$.options'),'$[*].labelKey') AS val
      FROM d_json j) v1 
    ) v2 ) v3 JOIN number_seq t ON sequences <= valLen) r
CROSS JOIN
  (SELECT REPLACE(REPLACE(GROUP_CONCAT(val),'[',''),']','') AS val2
     FROM 
       (SELECT JSON_EXTRACT(JSON_EXTRACT(j.data,'$.options'),'$[*].labelKey') AS val
      FROM d_json j) v1 
    ) v2;

The highlight of change from the previous query is the switch between the hardcoded numbering sequences with a query that basically get the total values separated by comma in the final JSON_EXTRACT and join it against the created number_seq table to get the rows needed. This part here:

SELECT sequences FROM 
(SELECT (LENGTH(val2)-LENGTH(REPLACE(val2,',','')))+1 AS valLen FROM
(SELECT REPLACE(REPLACE(GROUP_CONCAT(val),'[',''),']','') AS val2 FROM 
       (SELECT JSON_EXTRACT(JSON_EXTRACT(j.data,'$.options'),'$[*].labelKey') AS val
      FROM d_json j) v1 
    ) v2 ) v3 JOIN number_seq t ON sequences <= valLen

Here's an updated fiddle for reference https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ace8babce8d7bbb97f7e016a754e93a9

Upvotes: 3

Related Questions