JSON_KEY_VALUE
MariaDB starting with 11.2
JSON_KEY_VALUE was added in MariaDB 11.2.
Syntax
JSON_KEY_VALUE(obj, json_path)
Description
JSON_KEY_VALUE extracts key/value pairs from a JSON object. The JSON path parameter is used to only return key/value pairs for matching JSON objects.
Example
SELECT JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]');
+-----------------------------------------------------------------------------+
| JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]') |
+-----------------------------------------------------------------------------+
| [{"key": "key1", "value": "val1"}, {"key": "key2", "value": "val2"}] |
+-----------------------------------------------------------------------------+
JSON_KEY_VALUE() can be used as an argument to JSON_TABLE(), which allows adding the key to a result set.
SELECT jt.* FROM JSON_TABLE(
JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]'),'$[*]'
COLUMNS (
k VARCHAR(20) PATH '$.key',
v VARCHAR(20) PATH '$.value',
id FOR ORDINALITY )) AS jt;
+------+------+------+
| k | v | id |
+------+------+------+
| key1 | val1 | 1 |
| key2 | val2 | 2 |
+------+------+------+
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
