How the JSON_KEYS() function works in Mariadb?

The JSON_KEYS() function is a JSON function that returns the keys of a JSON object.

Posted on

The JSON_KEYS() function is a JSON function that returns the keys of a JSON object. The function takes a JSON document and an optional path as arguments and returns a JSON array that contains the keys. If the JSON document or the path are invalid, or if the value at the path is not an object or an array, the function returns NULL.

Syntax

The syntax of the JSON_KEYS() function is as follows:

JSON_KEYS(json_doc[, path])

Where json_doc is a valid JSON document and path is a valid JSON path expression.

Examples

Example 1: Getting the keys of a JSON object at the root level

In this example, we create a JSON document that contains an object with some key-value pairs using the JSON_OBJECT() function. Then we use the JSON_KEYS() function to get the keys of the JSON object at the root level.

SELECT JSON_KEYS(JSON_OBJECT('name', 'Alice', 'age', 25)) AS result;

The output is:

+-----------------+
| result          |
+-----------------+
| ["name", "age"] |
+-----------------+

This means that the function returns a JSON array that contains the keys of the JSON object at the root level ($).

Example 2: Getting the keys of a JSON object at a specified path

In this example, we create a JSON document that contains a nested object using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_KEYS() function to get the keys of the JSON object at the path $.hobbies.

SELECT JSON_KEYS(JSON_OBJECT('name', 'Bob', 'age', 30, 'hobbies', JSON_OBJECT('sport', 'soccer', 'music', 'guitar')), '$.hobbies') AS result;

The output is:

+--------------------+
| result             |
+--------------------+
| ["sport", "music"] |
+--------------------+

This means that the function returns a JSON array that contains the keys of the JSON object at the path $.hobbies.

Example 3: Getting the indexes of a JSON array at the root level

In this example, we create a JSON document that contains an array of numbers using the JSON_ARRAY() function. Then we use the JSON_KEYS() function on the JSON array.

SELECT JSON_KEYS(JSON_ARRAY(1, 2, 3)) AS result;

The output is:

+--------+
| result |
+--------+
| NULL   |
+--------+

This means that the the JSON_KEYS() function does not work for JSON arrays.

Example 4: Getting NULL if the JSON document or the path are invalid or not an object or an array

In this example, we use the JSON_KEYS() function to get the keys or indexes of a JSON document at a given path. However, we use an invalid JSON document, an invalid path, or a path that is not an object or an array as arguments. The function returns NULL in these cases.

SELECT JSON_KEYS('{"name": "Dave"', '$.name') AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the JSON document is not valid (it is missing a closing brace).

SELECT JSON_KEYS(JSON_OBJECT('name', 'Eve', 'age', 40), '$**') AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the path $** is not valid (it contains a wildcard token that is not allowed in this situation).

SELECT JSON_KEYS(JSON_OBJECT('name', 'Frank', 'age', 45), '$.name') AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the value at the path $.name is not an object or an array (it is a string).

There are some other JSON functions that are related to the JSON_KEYS() function. Here are some of them:

  • JSON_LENGTH(): This function returns the length of a JSON document. The function takes a JSON document and an optional path as arguments and returns the number of elements in the JSON document or the JSON value at the specified path.
  • JSON_TYPE(): This function returns the type of a JSON value. The function takes a JSON value as an argument and returns a string that indicates the type of the JSON value, such as ARRAY, OBJECT, STRING, NUMBER, BOOLEAN, or NULL.
  • JSON_VALUE(): This function returns a scalar value from a JSON document at a given path. The function takes a JSON document and a path as arguments and returns a string, a number, a boolean, or a null that matches the path. If the JSON document or the path are invalid, or if the value at the path is not a scalar, the function returns NULL.

Conclusion

The JSON_KEYS() function is a useful JSON function that can return the keys of a JSON object or the indexes of a JSON array. The function takes a JSON document and an optional path as arguments and returns a JSON array that contains the keys or indexes. If the JSON document or the path are invalid, or if the value at the path is not an object or an array, the function returns NULL. There are also some other related functions that can measure the length, type, or value of JSON documents, such as JSON_LENGTH(), JSON_TYPE(), and JSON_VALUE().