In MariaDB, JSON_KEYS() is a built-in function that returns an array of the top-level keys from a JSON document. If a path is provided, it returns the top-level keys from that path.

It excludes keys from nested sub-objects in the specified level.

Also, if the selected object is empty, an empty array is returned.

Syntax

The syntax goes like this:

JSON_KEYS(json_doc[, path])

Where json_doc is the JSON document, and path is a path within the document.

Example

Here’s an example to demonstrate.

SET @json_document = '
    { 
        "name": "Wag", 
        "type": "Dog", 
        "weight": 20 
    }
';
SELECT JSON_KEYS(@json_document);

Result:

+----------------------------+
| JSON_KEYS(@json_document)  |
+----------------------------+
| ["name", "type", "weight"] |
+----------------------------+

Specifying a Path

Here’s an example of specifying a path within the document:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_KEYS(
    @json_document, 
    '$.details'
    ) AS Result;

Result:

+------------------------------+
| Result                       |
+------------------------------+
| ["type", "weight", "awards"] |
+------------------------------+

As mentioned, sub-objects are excluded from the result.

Non-Existent Paths

Passing a path that doesn’t exist in the JSON document results in NULL.

Example:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_KEYS(
    @json_document, 
    '$.wrong'
    ) AS Result;

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

Empty Objects

If the selected object is empty, an empty array is returned:

SELECT JSON_KEYS('{}');

Result:

+-----------------+
| JSON_KEYS('{}') |
+-----------------+
| []              |
+-----------------+

Null Arguments

If any argument is NULL, the result is NULL:

SELECT 
    JSON_KEYS(null),
    JSON_KEYS(null, '$.type'),
    JSON_KEYS('{"a":1}', null);

Result:

+-----------------+---------------------------+----------------------------+
| JSON_KEYS(null) | JSON_KEYS(null, '$.type') | JSON_KEYS('{"a":1}', null) |
+-----------------+---------------------------+----------------------------+
| NULL            | NULL                      | NULL                       |
+-----------------+---------------------------+----------------------------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_KEYS();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_KEYS'

It’s the same when you provide too few or too many arguments:

SELECT JSON_KEYS('{ "a": 1}', 1, 2);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_KEYS'