How the JSON_OBJECT() function works in Mariadb?

The JSON_OBJECT() function is a built-in function in Mariadb that allows you to create a JSON object from a list of key-value arguments. It can be useful when you want to construct a JSON object from dynamic or user-defined data.

Syntax

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

JSON_OBJECT(key1, value1, ..., keyN, valueN)

The function takes an even number of arguments, where each pair of arguments represents a key and a value, as follows:

• key1, ..., keyN: These are the keys of the JSON object. They must be valid JSON keys or columns that contain JSON keys. They can be strings, numbers, booleans, or nulls. If the keys are not unique, the function uses the last value for each key.
• value1, ..., valueN: These are the values of the JSON object. They can be any valid JSON values or columns that contain JSON values. They can be strings, numbers, booleans, nulls, arrays, or objects.

The function returns a new JSON object that contains the key-value pairs from the arguments.

Examples

Example 1: Creating a JSON object from literal arguments

In this example, we use the JSON_OBJECT() function to create a JSON object from literal arguments.

SELECT JSON_OBJECT(
  'name', 'Alice',
  'age', 25,
  'gender', 'female'
) AS json_object;

The output is:

+--------------------------------------------------+
| json_object                                      |
+--------------------------------------------------+
| {"name": "Alice", "age": 25, "gender": "female"} |
+--------------------------------------------------+

As you can see, the function returns a JSON object that contains the key-value pairs from the arguments.

Example 2: Creating a JSON object from column arguments

In this example, we use the JSON_OBJECT() function to create a JSON object from column arguments.

CREATE TABLE users (
  id INT,
  name VARCHAR(50),
  age INT,
  gender VARCHAR(10)
);

INSERT INTO users VALUES
(1, 'Alice', 25, 'female'),
(2, 'Bob', 30, 'male');

SELECT JSON_OBJECT(
  'id', id,
  'name', name,
  'age', age,
  'gender', gender
) AS json_object FROM users;

The output is:

+-----------------------------------------------------------+
| json_object                                               |
+-----------------------------------------------------------+
| {"id": 1, "name": "Alice", "age": 25, "gender": "female"} |
| {"id": 2, "name": "Bob", "age": 30, "gender": "male"}     |
+-----------------------------------------------------------+

As you can see, the function returns a JSON object that contains the key-value pairs from the columns for each row.

Example 3: Creating a JSON object with duplicate keys

In this example, we use the JSON_OBJECT() function to create a JSON object with duplicate keys.

SELECT JSON_OBJECT(
  'name', 'Alice',
  'age', 25,
  'name', 'Bob',
  'age', 30
) AS json_object;

The output is:

+--------------------------------------------------------+
| json_object                                            |
+--------------------------------------------------------+
| {"name": "Alice", "age": 25, "name": "Bob", "age": 30} |
+--------------------------------------------------------+

Related Functions

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

• JSON_OBJECTAGG() function: This function creates a JSON object from a set of key-value pairs. It takes two arguments, a key and a value, and returns a JSON object that contains the aggregated key-value pairs from the rows of a group. For example, SELECT JSON_OBJECTAGG(name, age) AS json_object FROM users returns {"Alice": 25, "Bob": 30}.
• JSON_OBJECT_TO_ARRAY() function: This function converts a JSON object to a JSON array. It takes a JSON object and an optional mode argument, and returns a JSON array that contains the keys, the values, or the key-value pairs from the JSON object, depending on the mode. For example, JSON_OBJECT_TO_ARRAY('{"a": "b", "c": "d"}') returns [["a", "b"], ["c", "d"]].

Conclusion

The JSON_OBJECT() function is a useful function in Mariadb that allows you to create a JSON object from a list of key-value arguments. It can handle different types of arguments, such as literals or columns. It follows some rules when creating a JSON object, such as using the last value for duplicate keys. There are also some other functions that are related to the JSON_OBJECT() function, such as JSON_OBJECTAGG(), JSON_OBJECT_TO_ARRAY(), JSON_OBJECT_GET_KEYS(), and JSON_OBJECT_GET_VALUE(). You can use these functions to manipulate JSON data in different ways.