How to Get All Keys in JSON Object from MySQL
We can obtain all keys in a JSON object using JSON_KEYS() on a column in our MySQL table.
Suppose we have a table products with columns id and json_data.
| id | json_data |
|---|---|
0 |
{"a": 1, "b": 2} |
1 |
{"c": 3, "d": 4} |
2 |
{"d": 5, "e": 6} |
We want to obtain the keys in each JSON object.
| id | keys |
|---|---|
0 |
["a", "b"] |
1 |
["c", "d"] |
2 |
["d", "e"] |
We can use JSON_KEYS() to obtain all keys from the top-level of the JSON object as an array.
SELECT JSON_KEYS(json_data) FROM products;
If we want the number of keys in each JSON object, we can wrap it all inside JSON_LENGTH().
SELECT JSON_LENGTH(JSON_KEYS(json_data)) FROM products;