How to Use JSON_KEYS Function in MySQL for JSON Data

In MySQL, use the JSON_KEYS(json_doc[, path]) function to get an array of keys from a JSON object. The optional path lets you specify a location inside the JSON to extract keys from. This helps you explore JSON structure stored in your database.

Syntax

The JSON_KEYS function extracts the keys from a JSON object and returns them as a JSON array. It has two forms:

• JSON_KEYS(json_doc): Returns keys from the top-level JSON object.
• JSON_KEYS(json_doc, path): Returns keys from the JSON object at the specified path.

If the JSON value at the path is not an object, the function returns NULL.

sql

JSON_KEYS(json_doc[, path])

Example

This example shows how to use JSON_KEYS to get keys from a JSON column and from a nested object inside the JSON.

sql

SELECT
  JSON_KEYS('{"name": "Alice", "age": 30, "city": "NY"}') AS top_level_keys,
  JSON_KEYS('{"person": {"name": "Bob", "age": 25}, "city": "LA"}', '$.person') AS nested_keys;

Output

+---------------------+--------------+
| top_level_keys       | nested_keys  |
+---------------------+--------------+
| ["name", "age", "city"] | ["name", "age"] |
+---------------------+--------------+

Common Pitfalls

Common mistakes when using JSON_KEYS include:

• Passing a JSON array instead of an object, which returns NULL.
• Using an incorrect or non-existent path, which also returns NULL.
• Expecting keys from nested arrays instead of objects.

Always ensure the JSON value at the path is an object.

sql

/* Wrong: JSON array instead of object returns NULL */
SELECT JSON_KEYS('["apple", "banana"]') AS keys_from_array;

/* Right: Use an object to get keys */
SELECT JSON_KEYS('{"fruit": "apple", "color": "red"}') AS keys_from_object;

Output

+------------------+
| keys_from_array   |
+------------------+
| NULL             |
+------------------+

+-------------------+
| keys_from_object   |
+-------------------+
| ["fruit", "color"] |
+-------------------+

Quick Reference

Use this quick reference to remember how JSON_KEYS works:

Usage	Description
JSON_KEYS(json_doc)	Returns keys from the top-level JSON object.
JSON_KEYS(json_doc, path)	Returns keys from the JSON object at the specified path.
Returns NULL	If the JSON value at path is not an object or path is invalid.
Works only on JSON objects	Passing arrays or other types returns NULL.

Key Takeaways

Use JSON_KEYS to extract keys from JSON objects stored in MySQL.

The optional path argument lets you target nested JSON objects.

JSON_KEYS returns NULL if the target is not a JSON object.

Always verify your JSON structure to avoid unexpected NULL results.

JSON_KEYS outputs a JSON array of keys for easy further processing.