How the JSON_OBJECT_TO_ARRAY() function works in Mariadb?

The JSON_OBJECT_TO_ARRAY() function is a built-in function in Mariadb that allows you to convert a JSON object to a JSON array. It can be useful when you want to access the values of a JSON object in an ordered way, or when you want to transform a JSON object to a different format.

Syntax

The syntax of the JSON_OBJECT_TO_ARRAY() function is as follows:

JSON_OBJECT_TO_ARRAY(json_doc)

The function takes one argument, as follows:

• json_doc: This is a mandatory argument that specifies the JSON object to convert. It must be a valid JSON object or a column that contains JSON objects.

The function returns a new JSON array that is converted from the JSON object according to the specified mode.

Examples

In this example, we use the JSON_OBJECT_TO_ARRAY() function to convert a JSON object to a JSON array of key-value pairs with the default mode (0 or JSON_OBJECT_TO_ARRAY_MODE_DEFAULT).

SELECT JSON_OBJECT_TO_ARRAY(
  '{"name": "Alice", "age": 25, "gender": "female"}'
) AS converted_array;

The output is:

+--------------------------------------------------------+
| converted_array                                        |
+--------------------------------------------------------+
| [["name", "Alice"], ["age", 25], ["gender", "female"]] |
+--------------------------------------------------------+

As you can see, the function returns a JSON array that contains three key-value pairs, each represented as a two-element array.

Related Functions

There are some other functions in Mariadb that are related to the JSON_OBJECT_TO_ARRAY() function. Here are some of them:

• JSON_OBJECT() function: This function creates a JSON object from a list of key-value arguments. It takes an even number of arguments, where each pair of arguments represents a key and a value, and returns a JSON object that contains the same key-value pairs. For example, JSON_OBJECT('a', 'b', 'c', 'd') returns {"a": "b", "c": "d"}.
• JSON_ARRAY() function: This function creates a JSON array from a list of arguments. It takes any number of arguments, and returns a JSON array that contains the same arguments as elements. For example, JSON_ARRAY('a', 'b', 'c') returns ["a", "b", "c"].

Conclusion

The JSON_OBJECT_TO_ARRAY() function is a useful function in Mariadb that allows you to convert a JSON object to a JSON array. It can handle different types of arguments, such as JSON objects or columns. It supports three conversion modes, such as JSON_OBJECT_TO_ARRAY_MODE_DEFAULT, JSON_OBJECT_TO_ARRAY_MODE_KEYS, and JSON_OBJECT_TO_ARRAY_MODE_VALUES. There are also some other functions that are related to the JSON_OBJECT_TO_ARRAY() function, such as JSON_ARRAY_TO_OBJECT(), JSON_OBJECT(), and JSON_ARRAY(). You can use these functions to manipulate JSON data in different ways.