How do I return NULL conditionally from a JSON_QUERY

Question

How do I return a JSON_QUERY that returns NULL (or no property at all) if the selected value from the sub-query (or left join) is empty?

Here is a simple example (imagine NULL below is a sub-query):

SELECT 
    JSON_QUERY((SELECT NULL as SomeColumn 
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test

This returns and empty JSON object - which is not what I want:

Test
----------------
{}

Since NULL is being returned from the sub-query, I want it to return NULL if the subquery returns NULL, otherwise it should return JSON.

i.e.

When the Subquery returns NULL, the JSON result should be:

Test
----------------
NULL

When the Subquery returns JSON, the JSON result should be:

Test
----------------
{ someColumn: 'some json' ... }

How do I do this with a JSON query?

[Edit]

I tried this, thinking I could just do a CASE statement:

SELECT 
    JSON_QUERY((SELECT JSON_VALUE('{"SomeColumn":"Test"}', '$') AS SomeColumn 
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test

But this won't select the root. The above returns:

Test
------
{}

USE CASE

The use case that I'm after is this.

When I return a JSON query, I want to be able to return something like this when the address exists:

   { 
      name: 'John',
      address: {
         line1: '123 elm st'
      }
   }

OR if there is no address record, I want it to return:

   { 
      name: 'John',
      address: NULL
   }

OR

   { 
      name: 'John'
   }

I DON'T want it to return

   { 
      name: 'John',
      address: {}
   }

Answer 1

I found a simple solution to this.

To return NULL properties conditionally when the record from the left join (or subquery) does not exist, use "dot-notation" properties:

SELECT
    EMP.firstName as [Employee.FirstName],
    EMP.lastName as [Employee.LastName],
    A.Line1 as [Employee.Address.Line1],
    A.City as [Employee.Address.City]
FROM
   Employee EMP
LEFT OUTER JOIN Address A ON A.Id = EMP.AddressId
FOR JSON PATH

This will return the following when the employee has an Address:

{
   employee: {
       firstName: 'john',
       lastName: 'smith',
       address: {
          line1: 'elm st',
          city: 'New York'
       }
   } 
}

But if the employee does not have an Address record, the query will return this:

{
   employee: {
       firstName: 'john',
       lastName: 'smith'
   } 
}

Note:

• You need to make sure all dot notation properties are grouped so that properties and sub properties are together

Answer 2

Like this?

SELECT NULLIF(JSON_QUERY((SELECT NULL as SomeColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)), '{}') as Test

Answer 3

Yes, you can use a CASE..WHEN expression :

WITH t AS
(
  SELECT JSON_QUERY((SELECT JSON_VALUE('{"SomeColumn":"Test"}', '$') AS SomeColumn 
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test
)     
SELECT CASE WHEN Test = '{}' THEN 'NULL' ELSE Test END AS Test
  FROM t;


Test
-----
NULL

If you want NULL value (boolean expression), but not a string 'NULL', then use :

CASE WHEN Test != '{}' THEN Test END AS Test

Demo

Answer 4

If the JSON_QUERY doesn't locate any value, then it returns NULL. When you test if JSON_QUERY=N'{}' (when there is nothing located) then it evaluates to FALSE.

Run this and see

declare
  @json         nvarchar(max)=N'{ "objects": [ { "shape": "circle" }, {"shape": "square" } ] }',
  @nojson       nvarchar(max)=N'{}';

select json_query(@json, '$.objects[0]'); /* returns { "shape": "circle" } */
select json_query(@json, '$.objects[1]'); /* returns {"shape": "square" } */
select json_query(@json, '$.objects[3]'); /* returns NULL */

select case when json_query(@json, '$.objects[20]')=N'{}' then null else json_query(@nojson) end; 
/* returns {} */

select case when json_query(@json, '$.objects[20]') is null then 'True NULL' else 'False NULL' end;
/* returns 'True NULL' */

Answer 5

If I understand the question correctly, you may try the following approach, using the result from JSON_VALUE() as a condition:

SELECT JSON_VALUE(
   (SELECT NULL as SomeColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
   '$.SomeColumn'
) AS Test

Result:

Test
----
null

But if the JSON output contains information from more than one column, you may count the rows, returned from OPENJSON() as a check condition. The result from OPENJSON() with default schema is a table, that contains a single row for each key/value pair from a JSON text:

SELECT CASE WHEN EXISTS(SELECT 1 FROM OPENJSON(Json)) THEN Json END AS Test
FROM (VALUES
   ((SELECT NULL AS SomeColumn, NULL AS OtherColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)),
   ((SELECT NULL AS SomeColumn, 123 AS OtherColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
) j (Json)

Result:

Test
-------------------
null
{"OtherColumn":123}

Answer 6

I tried this, thinking I could just do a CASE statement:

SELECT JSON_QUERY((SELECT JSON_VALUE('{"SomeColumn":"Test"}', '$') AS SomeColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Test

But this won't select the root.

json_value() retrieves scalar values (null/boolean/string/number).

--"a" value is a scalar/string
select json_value('{"a":"1234"}', '$.a') --> returns 1234

--"a" value is an object
select json_value('{"a":{"anothercolumn": "the value of anothercolumn"}}', '$.a') --> returns null, since a is not a scalar value

...hence, json_value('any json goes here', '$') will always return NULL, because $elf is object and json_value() can retrieve scalars only.

select json_value('{"a":"b"}', '$'), json_value('[1, 2]', '$'), json_value('[{"a":"b"}, {"c":"d"}]', '$') --all NULL

for the retrieval of values which are objects or arrays, json_query() is used:

select json_query('{"a":{"anothercolumn": "the value of anothercolumn"}}', '$.a') --> returns {"anothercolumn":...}, since value of "a" is an object
--...and....
select json_query('{"a":"b"}', '$'), json_query('[1, 2]', '$'), json_query('[{"a":"b"}, {"c":"d"}]', '$') --each json

If you want to retrieve an "object" from a subquery which could return either NULL or a string of a json object, wrap the code from @iceblade, in a json_query()

SELECT JSON_QUERY(
NULLIF(JSON_QUERY((SELECT NULL as SomeColumn FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)), '{}')
) as Test;

SELECT 
     JSON_QUERY (NULLIF((SELECT v.col as SomeColumn 
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER), '{}'))  as 'jsonquery'
from (values ('a'), (null), ('b')) as v(col);

SELECT 
     JSON_QUERY (NULLIF((SELECT v.col as SomeColumn 
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER), '{}'))  as 'jsonquery'
from (values ('a'), (null), ('b')) as v(col)
for json path, INCLUDE_NULL_VALUES;

Answer 7

SELECT ISNULL(@JSONPATHQUERY),'{}')