Unnest key value pairs from json object

Question

I have a map with multiple key value pairs and I want to unnest them into separate rows.

"dayValueMap": {
    "2022-06-01": 1,
    "2022-06-02": 1,
    "2022-06-03": 1,
    "2022-06-04": 1,
    "2022-06-05": 1,
    "2022-06-06": 1,
}

I tried JSON_EXTRACT but that only extracts json from particular schema. Here I need to convert these key values as separate rows and in the map keys are dynamic.

Can someone help?

Into row in what table? Do all the json document look like this one? — RiggsFolly, Jan 17 '23 at 13:09
I would be tempted to read these into a program and then fiddle with the json in that and then create the new rows from there — RiggsFolly, Jan 17 '23 at 13:10
If you manage to upgrade your MySQL version, you can do something to solve this problem in a relatively smooth way. As is now, solutions (if any) would be fairly complex. — lemon, Jan 17 '23 at 13:22
Provide sample source data as CREATE TABLE + INSERT INTO scripts. Add desired output as table-formatted text. — Akina, Jan 17 '23 at 13:46
If you can't upgrade to MySQL 8.0, then I would suggest you not store your data as JSON. Store the key-value pairs as discrete values on individual rows. — Bill Karwin, Jan 17 '23 at 15:45
This question is identical to the one you posted yesterday: https://stackoverflow.com/questions/75137315/extract-key-value-pairs-from-mysql-json — Bill Karwin, Jan 17 '23 at 15:46

score 0 · Answer 1 · answered Jan 22 '23 at 15:52

In MySQL 8.0 you can use:

JSON_KEYS: to extract the array of keys from "$.dayValueMap" path
JSON_TABLE: to generate a table containing your extracted keys, one per record
JSON_EXTRACT: to extract the value from your table, using the previously extracted keys

SELECT json_key, 
       JSON_EXTRACT(info, CONCAT('$.dayValueMap.', json_key)) AS json_value
FROM tab,
     JSON_TABLE(
         JSON_KEYS(info, '$.dayValueMap'), '$[*]' 
         COLUMNS(
             json_key JSON PATH '$'
         )
    ) t;

Check the demo here.

Unnest key value pairs from json object

1 Answers1