SQL - JSON_OBJECT() Function

---

---

The SQL JSON_OBJECT() function is used to create JSON objects from the results of evaluating the SQL expressions of the arguments. It constructs the text of a JSON object from none or more expressions.

A JSON object contains a key/value pair. Each key is represented as a string in JSON, and the value can be of any type. The Keys and values are separated by a colon and each key/value pair is separated by a comma.

A curly brace { represents a JSON object.

Syntax

Following is the syntax of SQL JSON_OBJECT() function −

 JSON_OBJECT ( [ <json_key_value> [,...n] ] [ json_null_clause ] ) <json_key_value> ::= json_key_name : value_expression <json_null_clause> ::= NULL ON NULL | ABSENT ON NULL 

Parameters

They are three types of parameters −

• json_key_name − It is a character expression that defines a JSON key name value.

• value_expression − It is an expression that defines the value of the JSON key.

• json_null_clause − It can be used to control the behavior of the JSON_OBJECT function when value_expression is NULL. The NULL ON NULL option when generating a JSON key value converts a SQL NULL value to a JSON null value. The ABSENT ON NULL option omits the entire key if the value is NULL.

Return Value

It returns a valid JSON object string of type nvarchar(max).

Example

Following is an example returns the JSON object with two keys −

 SELECT JSON_OBJECT('name':'value', 'type':4) AS JSON_OBJECT; 

Output

This will display the following result −

 +-----------------------------+ | JSON_OBJECT | +-----------------------------+ | {"name":"value","type":4} | +-----------------------------+ 

Example

The following example returns a JSON object with one key because the value of one of the keys is NULL and the ABSENT ON NULL option is specified −

 SELECT JSON_OBJECT('name':'value', 'type':NULL ABSENT ON NULL) AS JSON_OBJECT; 

Output

This will display the following result −

 +-------------------+ | JSON_OBJECT | +-------------------+ | {"name":"value"} | +-------------------+ 

Example

Following is an example returns an empty JSON object −

 SELECT JSON_OBJECT() AS JSON_OBJECT; 

Output

This will display the following result −

 +-------------------+ | JSON_OBJECT | +-------------------+ | {} | +-------------------+ 

Example

Following is an example returns a JSON object with two keys. One key contains a JSON string and the other key contains a JSON array −

 SELECT JSON_OBJECT('name':'value', 'type':JSON_ARRAY(4, 6)) AS JSON_OBJECT; 

Output

This will display the following result −

 +-----------------------------+ | JSON_OBJECT | +-----------------------------+ |{"name":"value","type":[4,6]}| +-----------------------------+ 

Example

Following is an example returns a JSON object with two keys. One key contains a JSON string and the other key contains a JSON object−

 SELECT JSON_OBJECT('name':'value', 'type':JSON_OBJECT('type_id':4, 'name':'t')) AS JSON_OBJECT; 

Output

This will display the following result −

 +------------------------------------------------+ | JSON_OBJECT | +------------------------------------------------+ |{"name":"value","type":{"type_id":4,"name":"t"}}| +------------------------------------------------+