JSON_KEYS()¶
JSON_KEYS() returns the keys from the top-level keys of a JSON object as a JSON array, or from a nested object when an optional path argument is provided. Returns NULL if any argument is NULL or the path does not point to a JSON object.
Description¶
JSON_KEYS() extracts the member names from a JSON object. Without a path argument, it returns the keys of the top-level object. With a path argument, it navigates to a nested object and returns its keys.
If the target (either the document itself or the object at the given path) is not a JSON object, the function returns NULL. If the path does not match any value, the function returns NULL. If any argument is NULL, the function returns NULL.
Syntax¶
> JSON_KEYS(json_doc [, path])
Arguments¶
Arguments |
Description |
|---|---|
json_doc |
Required. A JSON document (string, JSON column, or result of a JSON function). |
path |
Optional. A JSON path expression specifying a nested object whose keys to return. |
Examples¶
DROP DATABASE IF EXISTS dbfuncs;
CREATE DATABASE dbfuncs;
USE dbfuncs;
-- Top-level keys
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
-- Nested object via path
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
-- Empty object
SELECT JSON_KEYS('{}');
-- Non-object input returns NULL
SELECT JSON_KEYS('[1,2,3]');
SELECT JSON_KEYS('"hello"');
SELECT JSON_KEYS('42');
-- Path to non-object returns NULL
SELECT JSON_KEYS('{"a": [1,2,3]}', '$.a');
-- Path not matching returns NULL
SELECT JSON_KEYS('{"a": 1}', '$.b');
-- NULL arguments
SELECT JSON_KEYS(NULL);
SELECT JSON_KEYS('{"a":1}', NULL);
-- Via JSON functions
SELECT JSON_KEYS(JSON_OBJECT('a', 1, 'b', 2));
-- Table usage with VARCHAR column
CREATE TABLE t1 (a VARCHAR(100));
INSERT INTO t1 VALUES ('{"a":1,"b":2}'), ('[1,2]'), ('{}'), ('"hello"'), (NULL);
SELECT a, JSON_KEYS(a) FROM t1;
-- Table usage with JSON column
CREATE TABLE t2 (a JSON);
INSERT INTO t2 VALUES ('{"a":1,"b":2}'), ('[1,2]'), ('{}'), ('"hello"'), ('null');
SELECT a, JSON_KEYS(a) FROM t2;
DROP TABLE t1;
DROP TABLE t2;
DROP DATABASE dbfuncs;