SQL Reference - JSON

JSON_ARRAYAGG

JSON_ARRAYAGG is a SQL function that aggregates the result set as a single JSON array. It goes through each data value in the group and converts the data into a JSON array. It returns NULL if there is no non-null value. If data type of input is not compatible with JSON format, a syntax error is raised.

JSON_ARRAYAGG(col_or_expr)

col_or_expr: This parameter refers to the column or the expression to be transformed into a JSON array. JSON_ARRAYAGG(col_or_expr) collects values from each row, encapsulating the value of the selected column or expression into a JSON array.

Example

SELECT JSON_ARRAYAGG(customer_name) as customerList
FROM customers;

Output

{
 "customerList": ["John Doe", "Jane Smith", "Tom Brown"]
}

Explanation

The JSON_ARRAYAGG function is used to convert a group of rows into a JSON array. In the example, it is converting the list of “customer_name” from the “customers” table into a JSON array named “customerList”.

JSON_ARRAYAGG( expression )

expression: This parameter defines the column or expression that the aggregate function will operate on. The argument is implicitly cast to JSON before the function operates on it. The result is a JSON array that includes all values from the group.

Example

SELECT JSON_ARRAYAGG(employee_name)
FROM employees
WHERE department_id = 10;

Output

["Jennifer", "Michael", "Patricia"]

Explanation

In the provided example, the JSON_ARRAYAGG function is used to aggregate the employee names into a single JSON array. The function fetches the names of employees in department 10. The output is a JSON array comprised of these employee names.