Menu

SQL Server JSON_OBJECT(): Build a JSON Object

JSON_OBJECT(key:value, ...) constructs a JSON object from zero or more key/value pairs. It was introduced in SQL Server 2022 (16.x) and is available on supported Azure SQL and Microsoft Fabric platforms. See Microsoft’s JSON_OBJECT reference for the current platform list.

Syntax

JSON_OBJECT([key:value [, ...n]] [NULL ON NULL | ABSENT ON NULL] [RETURNING json])

The default return type is nvarchar(max) containing valid JSON text. RETURNING json returns the native json type on platforms that support it. SQL Server 2022 doesn’t have the native json type; it is available in SQL Server 2025 (17.x) and selected Azure SQL and Fabric platforms.

Create an object from key/value pairs

Write each key and value with a colon between them. SQL Server quotes and escapes key names and converts SQL values to JSON types using the same rules as FOR JSON:

SELECT JSON_OBJECT(
    'orderId': 1042,
    'status': 'ready',
    'customer': 'Ada Lovelace'
) AS payload;

payload
--------------------------------------------------
{"orderId":1042,"status":"ready","customer":"Ada Lovelace"}

With no pairs, JSON_OBJECT() returns an empty object ({}).

Control SQL NULL values

The default NULL ON NULL keeps a key and writes its value as JSON null. Use ABSENT ON NULL to omit keys whose SQL value is NULL:

SELECT
    JSON_OBJECT('name':'Ada', 'city':NULL) AS keep_json_null,
    JSON_OBJECT('name':'Ada', 'city':NULL ABSENT ON NULL) AS omit_null_key;

keep_json_null         | omit_null_key
-----------------------|-----------------
{"name":"Ada","city":null} | {"name":"Ada"}

SQL NULL and the JSON literal null are not the same output choice: the first is a database value, while the second is a JSON value in the result document.

Nest JSON arrays and objects

Use JSON_ARRAY() and another JSON_OBJECT() call to create nested JSON values:

SELECT JSON_OBJECT(
    'name':'Ada',
    'roles':JSON_ARRAY('author', 'reviewer'),
    'profile':JSON_OBJECT('active':1, 'level':3)
) AS payload;

payload
--------------------------------------------------------------------------
{"name":"Ada","roles":["author","reviewer"],"profile":{"active":1,"level":3}}

For rows from a query, JSON_OBJECT() can construct one object per row. To aggregate multiple rows into one JSON object, use JSON_OBJECTAGG(), available on SQL Server 2025 (17.x) and supported Azure SQL/Fabric platforms. To serialize a whole query result, see FOR JSON.

For arrays, see JSON_ARRAY(). Browse the SQL Server JSON function guide for the full task-based list.

Advertisement