此处可找到关于 SQL Server 中内置 JSON 支持的常见问题解答。

FOR JSON 和 JSON 输出

FOR JSON PATH 或 FOR JSON AUTO？

问题。 我希望在单个表上从简单的 SQL 查询创建 JSON 文本结果。 FOR JSON PATH 和 FOR JSON AUTO 生成相同的输出。 我应该使用这两个选项之中的哪一个？

答案。 请使用 FOR JSON PATH。 尽管 JSON 输出没有任何区别，但 AUTO 模式采用一些其他的逻辑，可检查是否应嵌套列。 请将 PATH 作为默认选项。

创建一个嵌套的 JSON 结构

问题。 我希望生成具有多个同级别数组的复杂 JSON。 FOR JSON PATH 可以使用路径创建嵌套对象，FOR JSON AUTO 为每个表创建其他的嵌套级别。 这两个选项都不能够让我生成所需的输出。 我如何才能自定义现有选项不直接支持的 JSON 格式？

答案。 通过将 FOR JSON 查询添加为返回 JSON 文本的列表达式，可创建任何数据结构。 还可以使用 JSON_QUERY 函数手动创建 JSON。 下面的示例演示了这些方法。

SELECT col1, col2, col3,  
     (SELECT col11, col12, col13 FROM t11 WHERE t11.FK = t1.PK FOR JSON PATH) as t11,  
     (SELECT col21, col22, col23 FROM t21 WHERE t21.FK = t1.PK FOR JSON PATH) as t21,  
     (SELECT col31, col32, col33 FROM t31 WHERE t31.FK = t1.PK FOR JSON PATH) as t31,  
     JSON_QUERY('{"'+col4+'":"'+col5+'"}') as t41  
FROM t1  
FOR JSON PATH  
列表达式中 FOR JSON 查询或 JSON_QUERY 函数的每个结果的格式都为单独嵌套的 JSON 子对象，并且都包含在主要结果中。
防止 FOR JSON 输出中的 JSON 被双转义
问题。 我的 JSON 文本存储在一个表列中。 我想将它包含到 FOR JSON 的输出中。 但是 FOR JSON 会转义 JSON 中的所有字符，因此我会得到 JSON 字符串，而不是嵌套对象，如下例所示。
SELECT 'Text' AS myText, '{"day":23}' AS myJson  
FOR JSON PATH  
此查询将生成以下输出。
[{"myText":"Text", "myJson":"{\"day\":23}"}]  
我如何才能防止此行为？ 我想让 {"day":23} 作为 JSON 对象而不是转义文本返回。
答案。 存储在文本列中的 JSON 或文字被当做文本处理。 也就是说，它会括在双引号内并进行转义。 如果想返回未转义的 JSON 对象，请将 JSON 列作为参数传递给 JSON_QUERY 函数，如下例所示。
SELECT col1, col2, col3, JSON_QUERY(jsoncol1) AS jsoncol1  
FROM tab1  
FOR JSON PATH  
没有可选次要参数的 JSON_QUERY 仅将第一个参数作为结果返回。 由于 JSON_QUERY 始终返回有效的 JSON，因此 FOR JSON 知道不需要对此结果进行转义。
使用 WITHOUT_ARRAY_WRAPPER 子句生成的 JSON 在 FOR JSON 输出中被转义
问题。 我正尝试通过使用 FOR JSON 和 WITHOUT_ARRAY_WRAPPER 选项设置列表达式的格式。
SELECT 'Text' as myText,  
   (SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as myJson  
FOR JSON PATH   
但 FOR JSON 查询返回的文本似乎被转义成了纯文本。 这种情况仅在指定了 WITHOUT_ARRAY_WRAPPER 的情况下才会发生。 为什么它不被视为 JSON 对象并以未转义的形式包含在结果中？
答案。 如果在内部 FOR JSON 中指定 WITHOUT_ARRAY_WRAPPER 选项，生成的 JSON 文本不一定是有效的 JSON。 因此，外部 FOR JSON 会假定其为纯文本，并对字符串进行转义。 如果确定该 JSON 输出是有效的，请用 JSON_QUERY 函数包装它，将其提升为格式正确的 JSON，如下例所示。
SELECT 'Text' as myText,  
      JSON_QUERY((SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as myJson  
FOR JSON PATH    
OPENJSON 和 JSON 输出
使用 OPENJSON 从 JSON 文本中返回嵌套的 JSON 子对象
问题。 我无法使用显式架构的 OPENJSON 打开包含标量值、对象和数组的复杂 JSON 对象数组。 当我在 WITH 子句中引用键时，仅返回了标量值。 对象和数组则返回为 NULL 值。 我如何将对象或数组提取为 JSON 对象？
答案。 如果希望对象或数组以列的形式返回，请在列定义中使用 AS JSON 选项，如下例所示。
SELECT scalar1, scalar2, obj1, obj2, arr1  
FROM OPENJSON(@json)  
    WITH ( scalar1 int,  
        scalar2 datetime2,  
        obj1 NVARCHAR(MAX) AS JSON,  
        obj2 NVARCHAR(MAX) AS JSON,  
        arr1 NVARCHAR(MAX) AS JSON)  
使用 OPENJSON 而不是 JSON_VALUE 返回长文本值
问题。 我在包含长文本的 JSON 文本中拥有描述键。 JSON_VALUE(@json, '$.description') 返回 NULL，而不是一个值。
答案。 JSON_VALUE 旨在返回小的标量值。 通常此函数返回 NULL，而不是溢出错误。 如果你想返回更长的值，请使用支持 NVARCHAR(MAX) 值的 OPENJSON，如下例所示。
SELECT myText FROM OPENJSON(@json) WITH (myText NVARCHAR(MAX) '$.description')  
使用 OPENJSON 而不是 JSON_VALUE 处理重复键
问题。 我的 JSON 文本中有重复的键。 JSON_VALUE 仅返回在该路径上找到的第一个键。 我如何返回名称相同的所有键？
答案。 内置的 JSON 标量函数仅返回引用对象的第一个匹配项。 如果需要多个键，请使用 OPENJSON 表值函数，如下例所示。
SELECT value FROM OPENJSON(@json, '$.info.settings')  
WHERE [key] = 'color'  
OPENJSON 要求兼容性级别 130
问题。 我正尝试在 SQL Server 2016 中运行 OPENJSON，但收到以下错误。
Msg 208, Level 16, State 1 'Invalid object name OPENJSON'
答案。 OPENJSON 函数仅在 兼容级别 130下可用。 如果你的 DB 兼容性级别低于 130，则会隐藏 OPENJSON。 其他 JSON 函数在所有兼容性级别均可用。
引用 JSON 文本中中包含非字母数字字符的键
问题。 我的 JSON 文本中有包含非字母数字字符的键。 我如何引用这些属性？
答案。 必须在 JSON 路径中用引号将它们括起来。 例如 JSON_VALUE(@json, '$."$info"."First Name".value')。
详细了解 SQL Server 和 Azure SQL 数据库中的 JSON
Microsoft 视频
此部分中的某些视频链接在此时可能不起作用。 Microsoft 会将以前在第 9 频道上的内容迁移到新平台。 随着视频迁移到新平台，我们将更新链接。
有关 SQL Server 和 Azure SQL 数据库中内置 JSON 支持的视频介绍，请观看以下视频：
JSON 充当 NoSQL 和关系环境之间的桥梁
			即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。

        
提交和查看相关反馈