MariaDB JSON_OBJECT_TO_ARRAY(): Convert Objects to Key-Value Arrays

MariaDB JSON_OBJECT_TO_ARRAY() converts a JSON object’s key-value pairs into an array of two-element arrays. It is available from MariaDB 11.2. See the official MariaDB JSON_OBJECT_TO_ARRAY reference.

Syntax

JSON_OBJECT_TO_ARRAY(json_doc)

Each item in the returned array has the form [key, value]. Array or object values stay intact as the value in their pair.

Example

Convert the objects in a JSON document into key-value pair arrays:

SET @json_doc = '{"a": [1, 2, 3], "b": {"key1": "val1", "key2": {"key3": "val3"}}}';

SELECT JSON_OBJECT_TO_ARRAY(@json_doc);

[["a", [1, 2, 3]], ["b", {"key1": "val1", "key2": {"key3": "val3"}}]]

The result contains one pair for each top-level key. The array and nested object remain intact as the values paired with a and b. MariaDB documents this function for comparing objects by both keys and values. It can be combined with JSON_ARRAY_INTERSECT() to return common key-value pairs. If you only need a yes-or-no result, JSON_OVERLAPS() checks the objects directly for any common pair.

To represent each pair as an object with key and value fields, or to turn those pairs into table rows, see JSON_KEY_VALUE().