如何让SQL Server的JSON子查询在无匹配行时返回空数组而非省略该字段

我完全懂你的困扰——SQL Server在处理FOR JSON子查询时，一旦子查询没有返回任何行，就会直接把对应的字段从JSON结果里删掉，这和我们平时写SQL返回空集的逻辑确实有点脱节：明明查不到行也会返回一个空的结果集，到JSON这里却直接“消失”了。

不过别担心，我们可以通过一个简单的技巧来强制它返回空数组，核心思路是把NULL的子查询结果替换成合法的空JSON数组，同时让SQL Server识别它是JSON类型而非普通字符串。

修改后的查询代码 #

SELECT
    t.Name,
    t.object_id,
    t.type_desc,
    t.create_date,
    JSON_QUERY(ISNULL(
        (
            SELECT name, type_desc, is_unique, is_primary_key
            FROM sys.indexes si
            WHERE si.object_id = t.object_id
            AND type <> 0
            FOR JSON PATH
        ), '[]'
    )) AS indexes
FROM sys.tables t
FOR JSON PATH

为什么这个方案有效？ #

1. 原来的子查询在无结果时会返回NULL，而FOR JSON默认会忽略值为NULL的字段，所以indexes就被省略了。
2. 我们用ISNULL(子查询, '[]')把NULL替换成字符串'[]'，但直接这么做的话，SQL Server会把它当成普通字符串值，结果会变成"indexes": "[]"（带引号的字符串），这不是我们想要的。
3. 外层包裹JSON_QUERY()的作用是告诉SQL Server：“这是一个合法的JSON对象/数组，别把它当字符串处理”，这样最终结果里就会得到真正的空数组"indexes": []。

现在你再去测试没有索引的表，就能看到indexes字段稳稳地出现在JSON里，值是一个空数组啦。

备注：内容来源于stack exchange，提问作者Ian Boyd