'How to force SQL Server to return empty JSON array

I'm using SQL Server 2016, which supports JSON PATH to return JSON string. I wonder how to get just a simple empty json array, I mean [] when my query or sub-query returns null. I've tried this query:

SELECT '' AS TEST
FOR JSON PATH,ROOT('arr')

which returns:

{"arr":[{"test":""}]}

and also this one:

SELECT NULL AS TEST
FOR JSON PATH,ROOT('arr')

which returns:

{"arr":[{}]}

it's better but still not correct, I need this:

{"arr":[]}
sql-serverjsonsql-server-2016


Solution 1:[1]

You can always check this with ISNULL, e.g.:

select ISNULL( (SELECT * FROM sys.tables where 1=2 FOR JSON PATH), '[]')

If you need this in app layer, maybe it would be better to check is there some results set in data access code, and if not just return [] or {}.

Solution 2:[2]

This works, and can be composed within another for json query:

select json_query('[]') arr 
    for json path, without_array_wrapper

Solution 3:[3]

When nesting such subqueries, I've found that combining what others have said works best, i.e.:

  • Using COALESCE((SELECT .. FOR JSON), '[]') to prevent the null value from the subquery
  • Using JSON_QUERY() to prevent the escaping / quoting.

For example:

select
  json_query(coalesce((select 1 as b where 1 = 0 for json path), '[]')) as a
for json path;

Produces:

|JSON      |
|----------|
|[{"a":[]}]|

Without JSON_QUERY

Now the nested json array gets quoted:

select
  coalesce((select 1 as b where 1 = 0 for json path), '[]') as a
for json path;

Results in

|JSON        |
|------------|
|[{"a":"[]"}]|

Without COALESCE

Now the nested JSON is null:

select
  json_query((select 1 as b where 1 = 0 for json path)) as a
for json path`;

Results in

|JSON|
|----|
|[{}]|

Solution 4:[4]

A little manual, but if you need a quick hack, here you go:

DECLARE @JSON NVARCHAR(MAX) = (SELECT NULL AS test
FOR JSON PATH,ROOT('arr'))


SELECT REPLACE(@json, '{}', '')

Solution 5:[5]

By itself, JSON_QUERY('[]') AS [features] did not work for me. I found that the results were formatted as follows:

"features":"[]"

which was not desirable.

To get the desired result, I needed to store the JSON in a variable, then perform a REPLACE on the result, as follows:

DECLARE @json VARCHAR(MAX) = (SELECT JSON_QUERY('[]') AS [features],
                                     -- Other selected fields elided for brevity 
                                FROM MyTable
                                 FOR JSON, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES);

SET @json = REPLACE(@json, '"features":"[]"', '"features":[]');

SELECT @json;

Yes, it's a terrible hack. But it works, and returns the result I want. Our client absolutely must have an empty array returned, and this was the best way I could find to ensure that it was present.

Solution 6:[6]

Right now I had exactly the same problem, I think this is the right way to handle it according to the microsoft documentation:

DECLARE @Array TABLE(TEST VARCHAR(100));

SELECT
    arr = ISNULL((SELECT TEST FROM @Array FOR JSON PATH), '[]')
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Result:

{"arr":[]}

Solution 7:[7]

Using JSON_OBJECT and JSON_ARRAY functions(SQL Server 2022):

SELECT JSON_OBJECT('arr':JSON_ARRAY())

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jovan MSFT
Solution 2 N8allan
Solution 3 Lukas Eder
Solution 4 dfundako
Solution 5 Mike Hofer
Solution 6 Svalinn
Solution 7 Lukasz Szozda

Related Questions