Skip to content

json-array-transact-sql.md

Latest commit

 

History

History
183 lines (124 loc) · 4.88 KB

json-array-transact-sql.md

File metadata and controls

183 lines (124 loc) · 4.88 KB
description JSON_ARRAY (Transact-SQL)
title JSON_ARRAY (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 04/26/2022
ms.prod sql
ms.technology t-sql
ms.topic reference
f1_keywords
JSON_ARRAY
JSON_ARRAY_TSQL
dev_langs
TSQL
helpviewer_keywords
JSON_ARRAY function
JSON, validating
ms.assetid c836f3d3-3e17-44ae-92bf-f341918896c3
author uc-msft
ms.author umajay
monikerRange = azuresqldb-current||= azure-sqldw-latest||>= sql-server-2016||>= sql-server-linux-2017

JSON_ARRAY (Transact-SQL)

[!INCLUDE sqlserver2022-asdb-asmi]

Constructs JSON array text from zero or more expressions.

Syntax

JSON_ARRAY ( [ <json_array_value> [,...n] ] [ <json_null_clause> ]  )  

<json_array_value> ::= value_expression

<json_null_clause> ::=
	  NULL ON NULL
	| ABSENT ON NULL

Arguments

json_array_value Is an expression that defines the value of the element in the JSON array.

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 value of the element in the JSON array. The option ABSENT ON NULL will omit the element in the JSON array if the value is NULL. The default setting for this option is ABSENT ON NULL.

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

Return value

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

Remarks

Examples

Example 1

The following example returns an empty JSON array.

SELECT JSON_ARRAY();

Result

[]

Example 2

The following example returns a JSON array with four elements.

SELECT JSON_ARRAY('a', 1, 'b', 2)

Result

["a",1,"b",2]

Example 3

The following example returns a JSON array with three elements since one of the input values is NULL. Since the json_null_clause is omitted and the default for this option is ABSENT ON NULL, the NULL value in one of the inputs is not converted to a JSON null value.

SELECT JSON_ARRAY('a', 1, 'b', NULL)

Result

["a",1,"b"]

Example 4

The following example returns a JSON array with four elements. The NULL ON NULL option is specified so that any SQL NULL value in the input will be converted to JSON null value in the JSON array.

SELECT JSON_ARRAY('a', 1, NULL, 2 NULL ON NULL)

Result

["a",1,null,2]

Example 5

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

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

Result

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

Example 6

The following example returns a JSON array with three elements. One element contains a JSON string, another element contains a JSON object & another element contains a JSON array.

SELECT JSON_ARRAY('a', JSON_OBJECT('name':'value', 'type':1), JSON_ARRAY(1, null, 2 NULL ON NULL))

Result

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

Example 7

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

DECLARE @id_value nvarchar(64) = NEWID();
SELECT JSON_ARRAY(1, @id_value, (SELECT @@SPID));

Result

[1,"4BEA4F9F-D169-414F-AF99-9270FDB2EA62",55]

Example 8

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

SELECT s.session_id, JSON_ARRAY(s.host_name, s.program_name, s.client_interface_name)
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1;

Result

session_id info
52 ["WIN16-VM","Microsoft SQL Server Management Studio - Query",".Net SqlClient Data Provider"]
55 ["WIN16-VM","Microsoft SQL Server Management Studio - Query",".Net SqlClient Data Provider"]
56 ["WIN19-VM","SQLServerCEIP",".Net SqlClient Data Provider"]

See also

JSON Data (SQL Server)