How to Get All Keys in JSON Object from MySQL

Published Jul 23, 2022

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;