如何强制SQL Server返回空JSON数组

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:

我正在使用SQL Server 2016，它支持JSON路径返回JSON字符串。我想知道如何获得一个简单的空json数组，我的意思是[]当我的查询或子查询返回null时。我试着这个查询:

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

3 个解决方案

#1

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

这是可行的，并且可以在另一个json查询中进行组合:

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

#2

You can always check this with ISNULL, e.g.:

你可以用ISNULL来检查这个，例如:

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

如果在应用程序层中需要这个，那么最好检查数据访问代码中是否设置了一些结果，如果不是返回[]或{}。

#3

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

#1