Search code examples
mysqlarraysflatten

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


Solution

  • 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