Skip to content

Latest commit

 

History

History
169 lines (117 loc) · 4.62 KB

json-object-transact-sql.md

File metadata and controls

169 lines (117 loc) · 4.62 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs ms.custom monikerRange
JSON_OBJECT (Transact-SQL)
JSON_OBJECT constructs JSON object text from zero or more expressions.
WilliamDAssafMSFT
wiassaf
umajay
05/13/2024
sql
t-sql
reference
JSON_OBJECT
JSON_OBJECT_TSQL
JSON_OBJECT function
JSON, validating
TSQL
build-2024
=azuresqldb-current||>=sql-server-ver16||>=sql-server-linux-ver16||=azuresqldb-mi-current

JSON_OBJECT (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-fabricse-fabricdw]

Constructs JSON object text from zero or more expressions.

Syntax

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

Arguments

json_key_name

Is a character expression that defines the value of the JSON key name.

value_expression Is an expression that defines the value of the JSON key.

json_null_clause

Can be used to control the behavior of JSON_OBJECT function when value_expression is NULL. The option NULL ON NULL converts the SQL NULL value into a JSON null value when generating the JSON key value. The option ABSENT ON NULL will omit the entire key if the value is NULL. The default setting for this option is NULL ON NULL.

Return value

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

For more info about what you see in the output of the JSON_OBJECT function, see the following articles:

Examples

Example 1

The following example returns an empty JSON object.

SELECT JSON_OBJECT();

Result

{}

Example 2

The following example returns a JSON object with two keys.

SELECT JSON_OBJECT('name':'value', 'type':1)

Result

{"name":"value","type":1}

Example 3

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

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

Result

{"name":"value"}

Example 4

The following example returns a JSON object with two keys. One key contains a JSON string and another key contains a JSON array.

SELECT JSON_OBJECT('name':'value', 'type':JSON_ARRAY(1, 2))

Result

{"name":"value","type":[1,2]}

Example 5

The following example returns a JSON object with a two keys. One key contains a JSON string and another key contains a JSON object.

SELECT JSON_OBJECT('name':'value', 'type':JSON_OBJECT('type_id':1, 'name':'a'))

Result

{"name":"value","type":{"type_id":1,"name":"a"}}

Example 6

The following example returns a JSON object with the inputs specified as variables or SQL expressions.

DECLARE @id_key nvarchar(10) = N'id',@id_value nvarchar(64) = NEWID();
SELECT JSON_OBJECT('user_name':USER_NAME(), @id_key:@id_value, 'sid':(SELECT @@SPID))

Result

{"user_name":"dbo","id":"E2CBD8B4-13C1-4D2F-BFF7-E6D722F095FD","sid":63}

Example 7

The following example returns a JSON object per row in the query.

SELECT s.session_id, JSON_OBJECT('security_id':s.security_id, 'login':s.login_name, 'status':s.status) as info
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1;

Result

session_id info
51 {"security_id":"AQYAAAAAAAVQAAAAY/0dmFnai5oioQHh9eNArBIkYd4=","login":"NT SERVICE\\SQLTELEMETRY$SQL22","status":"sleeping"}
52 {"security_id":"AQUAAAAAAAUVAAAAoGXPfnhLm1/nfIdwAMgbAA==","login":WORKGROUP\\sqluser","status":"running"}

Related content