SQL - JSON_ARRAY() Function

The SQL JSON_ARRAY() function takes an expression or a comma-separated list of expressions and returns a JSON array containing those values. Several types of select-items may be used in conjunction with JSON ARRAY in a SELECT statement. Other places where a SQL function may be used, such as a WHERE clause, allow the specification of JSON ARRAY.

Each array element's value is returned by JSON ARRAY as either a string (with double quotes around it) or a number. The format of returned numbers is canonical. As a literal, a numeric string surrounded in double quotations is returned. The return value for all other data types, such as Date and $List, is a string. JSON_ARRAY does not support the COUNT(*) aggregate function.

Syntax

Following is the syntax of the SQL JSON_ARRAY() function −

JSON_ARRAY ( [ <json_array_value> [,...n] ] [ <json_null_clause> ]  )  
<json_array_value> ::= value_expression
<json_null_clause> ::=
   NULL ON NULL
   | ABSENT ON NULL

Parameters

  • json_array_value − It is an expression that indicates the value of the element in the JSON array.

  • json_null_clause − It is used to control the behaviour of the JSON_OBJECT function when the value expression is NULL. The option NULL ON NULL converts SQL NULL values into JSON NULL when generating the value of JSON_ARRAY. The option ABSENT ON NULL will omit the element from the JSON array if its value is NULL. The default setting for this option is "Absent on Null."

Example

Let's retrive the empty JSON ARRAY by using the following query −

SELECT JSON_ARRAY() AS Result;

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                                []  |
+------------------------------------+

Example

Let's retrive all the JSON_ARRAY values that was passed in the expression by using the following query −

SELECT JSON_ARRAY('abc', 14, 'bcd', 3) AS Result;

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                 ["abc",14,"bcd",3] |
+------------------------------------+

Example

Let's look at another example where we are going to use NULL as input values, which negates the null values, and return other input value in JSON_ARRAY. Because the json_null_clause is omitted and the default for this is ABSENT ON NULL, Hence the NULL value is not converted to a JSON null value −

SELECT JSON_ARRAY('abc', 14, 'bcd', NULL) AS Result;

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                   ["abc",14,"bcd"] |
+------------------------------------+

Example

Let's consider another scenario where we are going to pass NULL ON NULL option which helps to convert any SQL NULL value to JSON null value and retrieving the output by using the following query−

SELECT JSON_ARRAY('aBC', 14, NULL, 3 NULL ON NULL) AS Result;

Output

On executing the above query, the output is displayed as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                  ["aBC",14,null,3] |
+------------------------------------+

Example

Let's consider another example, where we are going to retrive the JSON_ARRAY with two element containing JSON string and another element is JSON object by using the following query −

SELECT JSON_ARRAY('ABC', JSON_OBJECT('car':'RX100', 'type':2)) AS Result;

Output

On executing the above query, the output is displayed as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|   ["ABC",{"car":"RX100","type":2}] |
+------------------------------------+

Example

Considering the another scenario, where we are going to retrive the JSON_ARRAY with three elements containing JSON string, JSON object and another element contains JSON array by using the following query −

SELECT JSON_ARRAY('TP', JSON_OBJECT('course':'HTML', 'type':2), JSON_ARRAY(145, null, 6 NULL ON NULL)) AS Result;

Output

On executing the above query, the output is displayed as follows −

+----------------------------------------------+
|                                       Result |
+----------------------------------------------+
|["TP",{"course":"HTML","type":2},[145,null,6]]|
+----------------------------------------------+

Example

Let's look into the following example, where we are going to specify the input as variable or SQL exression by using the following the query −

DECLARE @work varchar(64) = NEWID();
SELECT JSON_ARRAY(1, @work, (SELECT @@SPID)) AS Result;

Output

The output for the above query is produced as given below − 

+----------------------------------------------+
|                                       Result |
+----------------------------------------------+
|[1,"1DAC88DD-BE4E-45C7-BEA1-6FAF3EE86EE0",73] |
+----------------------------------------------+
sql-json-functions.htm
Advertisements