How to force SQL Server to return empty JSON array

sql-server json sql-server-2016
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 {}.

Related questions

Accessing JSON Array in SQL Server 2016 using JSON_VALUE

I am stuck while accessing array inside json using newly introduced JSON_VALUE function. Please consider following code - IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='JsonData') DROP TABLE JsonData; go CREATE TABLE JsonData(JsonData nvarchar(max)); …

Read More
OPENJSON does not work in SQL Server?

I want to use JSON functions in SQL Server 2016, but when I try to execute OPENJSON function, I get the following error: Msg 208, Level 16, State 1, Line 1 Invalid object name 'openjson'. Why it does not work? I have SQL Server 2016 RC …

Read More
Create nested JSON arrays using FOR JSON PATH

I need to create a JSON output from a query that uses inner join between two tables with a one to many relationship. I would like the values of the secondary table to be nested as array properties of the …

Read More