MariaDB JSON_KEY_VALUE(): Extract Object Keys and Values

MariaDB JSON_KEY_VALUE() extracts the key-value pairs from a JSON object selected by a JSONPath expression. It returns an array of objects with key and value fields. The function is available from MariaDB 11.2. See the official MariaDB JSON_KEY_VALUE reference.

Syntax

JSON_KEY_VALUE(json_doc, json_path)

Extract an object’s key-value pairs

The path below selects the object inside a nested JSON array:

SELECT JSON_KEY_VALUE(
    '[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]',
    '$[0][1]'
);

[{"key": "key1", "value": "val1"}, {"key": "key2", "value": "val2"}]

Return one row per pair with JSON_TABLE

Pass the returned array to JSON_TABLE() to expose the keys and values as columns:

SELECT jt.*
FROM JSON_TABLE(
    JSON_KEY_VALUE(
        '[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]',
        '$[0][1]'
    ),
    '$[*]' COLUMNS (
        `key` VARCHAR(20) PATH '$.key',
        `value` VARCHAR(20) PATH '$.value',
        id FOR ORDINALITY
    )
) AS jt;

+------+-------+----+
| key  | value | id |
+------+-------+----+
| key1 | val1  |  1 |
| key2 | val2  |  2 |
+------+-------+----+

For a direct array of [key, value] pairs, see JSON_OBJECT_TO_ARRAY(). For extracting object values without their keys, see JSON_TABLE().