How the JSON_OBJECTAGG() function works in Mariadb?

The JSON_OBJECTAGG() function is a built-in function in Mariadb that allows you to create a JSON object from a set of key-value pairs. It can be useful when you want to aggregate data from multiple rows into a single JSON object.

Syntax

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

JSON_OBJECTAGG(key, value)

The function takes two arguments, as follows:

• key: This is the key of the JSON object. It must be a valid JSON key or a column that contains JSON keys. It can be a string, a number, a boolean, or a null. If the keys are not unique, the function uses the last value for each key.
• value: This is the value of the JSON object. It can be any valid JSON value or a column that contains JSON values. It can be a string, a number, a boolean, a null, an array, or an object.

The function returns a new JSON object that contains the aggregated key-value pairs from the rows of a group.

Examples

Example 1: Creating a JSON object from a table

In this example, we use the JSON_OBJECTAGG() function to create a JSON object from a table that contains information about users.

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_OBJECTAGG(name, age) AS json_object FROM users;

The output is:

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

As you can see, the function returns a JSON object that contains the name and age of each user as key-value pairs.

Example 2: Creating a JSON object with a condition

In this example, we use the JSON_OBJECTAGG() function to create a JSON object with a condition. We only include the users who are older than 25.

SELECT JSON_OBJECTAGG(name, age) AS json_object FROM users WHERE age > 25;

The output is:

+-------------+
| json_object |
+-------------+
| {"Bob":30}  |
+-------------+

As you can see, the function returns a JSON object that only contains the name and age of the user who is older than 25.

Example 3: Creating a JSON object with a group by clause

In this example, we use the JSON_OBJECTAGG() function to create a JSON object with a group by clause. We group the users by their gender and create a JSON object for each group.

SELECT gender, JSON_OBJECTAGG(name, age) AS json_object FROM users GROUP BY gender;

The output is:

+--------+--------------+
| gender | json_object  |
+--------+--------------+
| female | {"Alice":25} |
| male   | {"Bob":30}   |
+--------+--------------+

As you can see, the function returns a JSON object for each gender group that contains the name and age of the users in that group.

Related Functions

There are some other functions in Mariadb that are related to the JSON_OBJECTAGG() 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_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_OBJECTAGG() function is a useful function in Mariadb that allows you to create a JSON object from a set of key-value pairs. It can handle different types of arguments, such as columns or expressions. It follows some rules when creating a JSON object, such as using the last value for duplicate keys. It can also be used with a condition or a group by clause to create a JSON object for each group. There are also some other functions that are related to the JSON_OBJECTAGG() function, such as JSON_OBJECT(), 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.