How to force SQL Server to return empty JSON array

Saman Gholami picture Saman Gholami · Sep 19, 2016 · Viewed 7.5k times · Source

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

Answer

Jovan MSFT picture Jovan MSFT · Oct 9, 2016

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