MySQL JSON with arbitrary keys to table

Question

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?

Answer 1

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.