MySQL JSON_KEYS() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_KEYS() function to retrieve the property names of a JSON object.

Introduction to MySQL JSON_KEYS() function

The JSON_KEYS() function is used to retrieve the keys or property names from a JSON object within a JSON document.

Here’s the syntax of the JSON_KEYS() function:

JSON_KEYS(json_doc, [path])Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_doc: This is the JSON object from which you want to extract the property name.
• path (optional): This is the path that specifies an object within a document that you want to extract the keys.

The JSON_KEYS() function returns a JSON array that contains property names of the json_doc object or the JSON object specified by the path.

The JSON_KEYS() function returns NULL if the json_doc is null or json_doc is not a valid JSON object or the path does not locate an object within the json_doc.

The JSON_KEYS() function issues an error if the json_doc is not a valid JSON document, the path is not valid or contains wildcards (*) or (**).

MySQL JSON_KEYS() function examples

Let’s take some examples of using the JSON_KEYS() function:

1) Basic JSON_KEYS() function usage

Suppose you have the following JSON object:

{
    "name": "John",
    "age": 25,
    "job": "MySQL Developer"
}Code language: SQL (Structured Query Language) (sql)

The following example uses the JSON_KEYS() function to extract all property names of the above JSON object:

SELECT 
  JSON_KEYS(
    '{"name":"John","age":25,"job":"MySQL Developer"}'
  ) properties;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------+
| properties             |
+------------------------+
| ["age", "job", "name"] |
+------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using the JSON_KEYS() function with a path

Suppose you have the following JSON document:

{
    "book": {
        "title": "The Great Gatsby",
        "author": {
            "name": "F. Scott Fitzgerald",
            "birth_year": 1896
        }
    }
}Code language: SQL (Structured Query Language) (sql)

To extract the keys from the author object, you use the following path:

'$.book.author'Code language: SQL (Structured Query Language) (sql)

For example:

SELECT 
  JSON_KEYS(
    '{"book": {"title": "The Great Gatsby", "author": {"name": "F. Scott Fitzgerald", "birth_year": 1896}}}', 
    '$.book.author'
  ) author;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------+
| author                 |
+------------------------+
| ["name", "birth_year"] |
+------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_KEYS() function to retrieve the property names of a JSON object.