JSON_OBJECTAGG

JSON_OBJECTAGG(key, value)

• key: The key parameter indicates the column or expression that will be used as keys in the JSON object. Every JSON object entry comprises a key-value pair, with this parameter defining what will be used as the key.
• value: The value parameter specifies the column or expression that will be used as values in the JSON object. This is the information the key will map to in the resulting JSON object.

Example

SELECT JSON_OBJECTAGG(
    employee_id,
    CONCAT(first_name, ' ', last_name)
)
FROM employees
WHERE employee_id <= 5;

Output

{
    "1": "John Doe",
    "2": "Jane Smith",
    "3": "Mary Johnson",
    "4": "James Brown",
    "5": "Patricia Davis"
}

Explanation

JSON_OBJECTAGG is a MySQL function that concatenates row values into a JSON object. In this example, it outputs a JSON object where each key is an employee ID, and the corresponding value is the full name of the employee. Only records with an employee ID less than or equal to 5 are selected.

JSON_OBJECTAGG( key NVARCHAR(4000), value NVARCHAR(max) )

• key nvarchar(4000): This parameter serves as the name of the property in the created JSON object. Passed as a column or expression of NVARCHAR(4000) type, it restricts the maximum length of the key-value pair’s key to 4000 characters.
• value nvarchar(max): This parameter represents the value assigned to the corresponding key in the JSON object. Accepting a column or expression of NVARCHAR(max) type, it allows storing sizable data as it doesn’t limit the value’s length or size. This value can be of any data type that can be transformed to a string.

JSON_OBJECTAGG is not directly supported by SQL Server. However, you can create a similar result by using FOR JSON clause in SQL Server. This allows you to format query results as JSON and direct the output to a file or a console. Below is an example:

Example

SELECT
  (SELECT
    Name AS 'Name',
    Age AS 'Age'
  FROM Employee
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS Employee
FROM Departments
FOR JSON PATH;

Output

{
  "Employee": {
    "Name": "John Doe",
    "Age": 30
  }
}

Explanation

The SQL example demonstrates how to format a query result as JSON. The output is a JSON object with details about an employee.

Inside the subquery, we convert the selected Name and Age columns to JSON format using the FOR JSON PATH, WITHOUT_ARRAY_WRAPPER clause.

The main query turns the result of the subquery into another JSON object with the ‘Employee’ key. For this purpose, we again use the FOR JSON PATH clause.

JSON_OBJECTAGG( key, value )

• key: This parameter expects a string that will serve as the name or key for the JSON element. The key parameter is typically provided as an expression that evaluates to a string. If a non-string value is used, the Oracle database will convert it to a string.
• value: This parameter represents the values to be associated with the defined keys in the generated JSON object. It takes an expression that evaluates to a JSON-compatible datatype. If a non-JSON compatible datatype is used, Oracle will attempt to convert it to one. If the conversion is unsuccessful, an error is returned.

Example

SELECT JSON_OBJECTAGG(
  key "name", value "value"
)
FROM (
  SELECT name, count(name) as value
  FROM employee
  GROUP BY name
)

Output

{
  "John": 3,
  "Maria": 2,
  "Michael": 4
}

Explanation

In the presented example, the JSON_OBJECTAGG function is used to aggregate the data from the ‘employee’ table into a JSON object. The keys of the JSON object (“John”, “Maria”, “Michael”) correspond to the unique names in the ‘employee’ table. The values of the JSON object (3, 2, 4) represent the count of each name in the ‘employee’ table.