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 {}.
This works, and can be composed within another for json query:
select json_query('[]') arr
for json path, without_array_wrapper
When nesting such subqueries, I've found that combining what others have said works best, i.e.:
COALESCE((SELECT .. FOR JSON), '[]') to prevent the null value from the subqueryJSON_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|
|----|
|[{}]|
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, '{}', '')
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.
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":[]}
Using JSON_OBJECT and JSON_ARRAY functions(SQL Server 2022):
SELECT JSON_OBJECT('arr':JSON_ARRAY())
