CODEWITHSUNDEEP
sql-serverjsonsql-server-2016
Saman Gholami
Saman Gholami

Reputation: 3512

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":[]}

Upvotes: 24

Views: 23737

Answers (7)

Lukasz Szozda
Lukasz Szozda

Reputation: 175934

Using JSON_OBJECT and JSON_ARRAY functions(SQL Server 2022):

SELECT JSON_OBJECT('arr':JSON_ARRAY())

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 221106

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|
|----|
|[{}]|

Upvotes: 5

Svalinn
Svalinn

Reputation: 19

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":[]}

Upvotes: 1

Mike Hofer
Mike Hofer

Reputation: 17022

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.

Upvotes: 1

N8allan
N8allan

Reputation: 2268

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

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

Upvotes: 12

Jovan MSFT
Jovan MSFT

Reputation: 14630

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 {}.

Upvotes: 18

dfundako
dfundako

Reputation: 8324

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, '{}', '')

Upvotes: 1

Related Questions