In the realm of MySQL database management, the JSON_OBJECTAGG function plays a crucial role in manipulating and aggregating JSON data. It facilitates the creation of JSON objects from key-value pairs extracted from specific columns. This function’s versatility extends to aggregating data based on group-by criteria, enabling the creation of comprehensive JSON representations of aggregated information.

Syntax

JSON_OBJECTAGG(key, value)

key: The column or expression whose values will be used as keys in the resulting JSON object.
value: The column or expression whose values will be used as values in the resulting JSON object.

Example

Consider a table named employees with columns employee_id, first_name, and last_name. We want to create a JSON object where the keys are employee IDs and the values are the corresponding full names.

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

INSERT INTO employees VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Bob', 'Johnson'),
(4, 'Alice', 'Williams');

Now, let’s use JSON_OBJECTAGG to create a JSON object:

SELECT 
JSON_OBJECTAGG(employee_id, CONCAT(first_name, ' ', last_name))
FROM employees;

The result will be a single-row, single-column result set containing a JSON object:

{"1": "John Doe", "2": "Jane Smith", "3": "Bob Johnson", "4": "Alice Williams"} 

In this example, the employee_id column values are used as keys, and the concatenated full names (first_name + ‘ ‘ + last_name) are used as values in the resulting JSON object.

It’s important to note that JSON_OBJECTAGG can be quite handy when you need to aggregate data into a JSON format, and it is just one of the many JSON functions available in MySQL for working with JSON data.