如何在SQL Server中利用嵌套查询配合FOR JSON导出报表？

SQL Server中实现多层JSON嵌套必须用子查询配合JSON_QUERY()显式包裹，否则嵌套内容会转义为字符串；子查询需加TOP 100 PERCENT（或OFFSET 0）规避ORDER BY报错；空结果默认为null，可用ISNULL(JSON_QUERY(...), '[]')补空数组；点号别名仅支持一级嵌套，深层结构须用嵌套子查询。

SQL Server 中无法靠单条 FOR JSON 自动推导多层嵌套结构，必须用子查询 + JSON_QUERY() 显式包裹，否则嵌套部分会变成带引号的字符串而非真实 JSON 对象。

子查询返回的 JSON 必须用 JSON_QUERY() 包裹

直接在 SELECT 中写子查询并 FOR JSON PATH，外层 FOR JSON 会把整个子结果当普通字符串处理，导致类似 "items": "[{"id":1}]" 的转义输出——这不是合法嵌套，而是字符串字面量。

• 子查询结果类型是 NVARCHAR(MAX)，不是 JSON 类型，FOR JSON 不识别其语义
• 必须显式调用 JSON_QUERY(子查询) 告诉 SQL Server：“这段文本应作为 JSON 值嵌入”
• 漏掉 JSON_QUERY 是生产环境最常踩的坑，现象是前端解析失败或显示空数组

FOR JSON PATH 子查询里不能有 ORDER BY

子查询中若出现 ORDER BY，除非同时加 TOP（如 TOP 100 PERCENT），否则报错 FOR JSON requires an alias for nested results —— 这个错误提示和实际问题不匹配，容易误导。

• 正确写法：(SELECT TOP 100 PERCENT id, name FROM Items WHERE ... FOR JSON PATH) AS items
• TOP 不影响语义，仅用于满足语法限制；SQL Server 2022+ 支持 OFFSET 0 ROWS 替代，但兼容性不如 TOP
• 若需稳定排序，应在子查询外层再套一层 SELECT * FROM (子查询) ORDER BY ...，但注意这会破坏 FOR JSON PATH 的数组生成逻辑

空子查询结果默认为 null，不是 []

当关联子查询无匹配行时（例如某订单没有明细），JSON_QUERY(...) 返回 NULL，最终字段值就是 "items": null。多数前端期望的是 "items": []。

• 补空数组可用：ISNULL(JSON_QUERY((SELECT ... FOR JSON PATH)), '[]')
• 更稳妥的做法是在子查询中用 LEFT JOIN 或 CASE WHEN 确保至少返回一行（哪怕字段全 NULL），再配合 INCLUDE_NULL_VALUES
• INCLUDE_NULL_VALUES 只控制字段是否出现在对象中，不改变空集合的 null 行为

别名含点号只能生成一级嵌套，深层结构仍需子查询

列别名如 user.name 或 address.city 能让 FOR JSON PATH 输出 {"user":{"name":"a"}}，但仅限一层。若想表达 {"user":{"profile":{"age":30}}}，点号写法无效。

• 三层及以上必须拆成两层子查询：外层查 user，内层子查询查 profile 并 FOR JSON PATH，再由外层用 JSON_QUERY 嵌入
• 别名点号本质是语法糖，底层仍依赖字段映射，不触发真正的嵌套查询执行计划
• 混用点号和子查询易造成结构混乱，建议统一用子查询 + JSON_QUERY，逻辑清晰且可控

真正难的不是写出来，而是确保每层子查询的关联条件准确、空值边界被覆盖、以及 JSON_QUERY 没被漏掉——这三个地方出错，JSON 看似生成成功，实际在消费端崩溃。