MySQL JSON with arbitrary keys to table-CodePudding

There is a map nested in a large json payload like

{
    "map": {
        "key1": "value1",
        "key2": "value2",
        "key3": "value3"
    },
    // more stuff
}

I would like to generate a table like that:

 ------#-------- 
| Key  | Value  |
 ------#-------- 
| key1 | value1 |
| key2 | value2 |
| key3 | value3 |
 ------#--------

The only thing I can think of is writing a stored function that loops over JSON_KEYS to convert all key value pairs into

[{"key":"key1", "value":"value1"}, {"key":"key2", "value":"value2"}, ...]

which makes the task trivial with JSON_TABLE.

Is there a faster and more elegant way?

CodePudding user response：

Here's a solution:

select j.key, json_unquote(json_extract(m.data, concat('$.map.', j.key))) as value from mytable as m
cross join json_table(json_keys(m.data, '$.map'), '$[*]' columns (`key` varchar(10) path '$')) as j

Output with your sample data:

 ------ -------- 
| key  | value  |
 ------ -------- 
| key1 | value1 |
| key2 | value2 |
| key3 | value3 |
 ------ --------

If that query seems inelegant or hard to maintain, you're probably right. You shouldn't store data in JSON if you want simple or elegant queries.

CodePudding user response：

your doing well and even for enterprise project doing this way