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