1

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Keks
  • 75
  • 4
  • 2
    I don't understand why people use JSON in MySQL. Just about everything gets harder. – Barmar Sep 22 '21 at 16:09
  • 1
    @Barmar Because they want instant gratification. They don't have to think about what attributes they need before they start loading gobs of data into the database. – Bill Karwin Sep 22 '21 at 16:45

2 Answers2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

your doing well and even for enterprise project doing this way

Ashkan Khaleghi
  • 56
  • 9