In SQL Server, we can use the JSON_OBJECT() function to construct JSON object text from zero or more expressions.

The resulting object contains the key/value pairs that we provide as arguments. Providing zero expressions results in an empty object.

The JSON_OBJECT() function was introduced in SQL Server 2022.

Syntax

The syntax goes like this:

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

The following examples demonstrate how it works.

Example

Here’s a basic example:

SELECT JSON_OBJECT('name' : 'Wag', 'weight' : 10);

Result:

{"name":"Wag","weight":10}

In this case I created a basic JSON object with two key/value pairs.

Create an Empty Object

When we call JSON_OBJECT() without any arguments, we get an empty JSON object:

SELECT JSON_OBJECT();

Result:

{}

The NULL ON NULL Option

By default, if a key contains a NULL value it’s converted into a JSON NULL value:

SELECT JSON_OBJECT('name' : 'Wag', 'weight' : null);

Result:

{"name":"Wag","weight":null} 

However, we also have the option of using NULL ON NULL to explicitly specify this behaviour:

SELECT JSON_OBJECT('name' : 'Wag', 'weight' : null NULL ON NULL);

Result:

{"name":"Wag","weight":null} 

The ABSENT ON NULL Option

We can use ABSENT ON NULL to specify that if a key contains a NULL value, then the key shouldn’t appear in the JSON object:

SELECT JSON_OBJECT('name' : 'Wag', 'weight' : null ABSENT ON NULL);

Result:

{"name":"Wag"} 

Here we can see that the weight key doesn’t even appear in the resulting JSON object because it contains NULL and we specified ABSENT ON NULL.

Embed a JSON Object

We can pass another JSON_OBJECT() as an argument to JSON_OBJECT() itself in order to embed a JSON object into the resulting JSON object:

SELECT JSON_OBJECT(
  'name' : 'Wag', 
  'weight': JSON_OBJECT('morning' : 10, 'night': 12 ) 
  );

Result:

{"name":"Wag","weight":{"morning":10,"night":12}}

Add a JSON Array

We can use the JSON_ARRAY() function to embed a JSON array into our JSON object:

SELECT JSON_OBJECT(
  'name' : 'Wag', 
  'friends': JSON_ARRAY( 'Bark', 'Scratch', 'Fetch') 
  );

Result:

{"name":"Wag","friends":["Bark","Scratch","Fetch"]}

The JSON_ARRAY() function was introduced in SQL Server 2022 (like the JSON_OBJECT() function itself).

A Database Example

This example uses data from a database to provide multiple rows of JSON objects:

SELECT 
  JSON_OBJECT( 'Declared' : DeclarationDate, 'Amount' : Amount )
FROM Dividends;

Result:

{"Declared":"2023-03-08","Amount":10.5200}                                                                                                                                                                                                                      
{"Declared":"2023-06-08","Amount":10.5200}                                                                                                                                                                                                                      
{"Declared":"2023-09-08","Amount":10.5200}                                                                                                                                                                                                                      
{"Declared":"2023-12-08","Amount":12.8600}                                                                                                                                                                                                                      

In this case I returned data from a table called Dividends. This table contains a column called DeclarationDate and another column called Amount. The table contains four rows, and so our code resulted in four rows being returned.