JSON_KEYS()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns the keys of a JSON object as a JSON array.
USAGE
JSON_KEYS(data[, path])
Argument Name | Description |
|---|---|
| The JSON data to query |
| Optional. The path specification of the desired object |
DETAILS
JSON_KEYS() is a JSON function that extracts the key names from a valid JSON object.
The object to inspect is the top-level object unless the path argument is used to choose an object within the JSON data.
JSON data is valid if it conforms to ECMA-404: The JSON Data Interchange Standard.
A NULL is returned if either argument is NULL, if the indicated path is not found, or if the data being inspected is not a JSON object.
EXAMPLES
With Literal Values
In the following example, JSON_KEYS() is called with a JSON literal value. The result demonstrates how to obtain the keys in a JSON object:
SELECT JSON_KEYS('{"bar": 2, "baz": 3, "foo": 1}') AS result1,
JSON_KEYS('[1, 2, 3, 4]') as result2,
JSON_KEYS('[1, {"foo": 42}, 3]', '$[1]') as result3;
+-----------------------+---------+---------+
| result1 | result2 | result3 |
+-----------------------+---------+---------+
| ["bar", "baz", "foo"] | NULL | ["foo"] |
+-----------------------+---------+---------+
Example Schema and Data
Some of the examples are based on the keys_test table:
CREATE TABLE keys_test (
example JSON
);
INSERT INTO keys_test VALUES
('{"bar": {"deeper": [1, 2, 3]}, "foo": 42}'),
('{"other": 1, "results": 2}'),
('[3.14, {"deeper": "data"}, 9.2]');
Per-row Values
This example returns the key names of each row's JSON object as a JSON array. Note that it does not descend into any deeper objects:
SELECT JSON_KEYS(example)
FROM keys_test;
+----------------------+
| JSON_KEYS(example) |
+----------------------+
| ["bar", "foo"] |
| ["other", "results"] |
| NULL |
+----------------------+
JSON Keys at Specific Path
JSON_KEYS() may be used to find the object keys at a specific path in a JSON document by supplying the optional path argument:
SELECT JSON_KEYS(example, '$[1]')
FROM keys_test
WHERE example like '[%';
+----------------------------+
| JSON_KEYS(example, '$[1]') |
+----------------------------+
| ["deeper"] |
+----------------------------+