解决 SQL Server 中 JSON 的常见问题Solve common issues with JSON in SQL Server

适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later

此处可找到关于 SQL Server 中内置 JSON 支持的常见问题解答。Find answers here to some common questions about the built-in JSON support in SQL Server.

FOR JSON 和 JSON 输出FOR JSON and JSON output

FOR JSON PATH 或 FOR JSON AUTO?FOR JSON PATH or FOR JSON AUTO?

问题。Question. 我希望在单个表上从简单的 SQL 查询创建 JSON 文本结果。I want to create a JSON text result from a simple SQL query on a single table. FOR JSON PATH 和 FOR JSON AUTO 生成相同的输出。FOR JSON PATH and FOR JSON AUTO produce the same output. 我应该使用这两个选项之中的哪一个?Which of these two options should I use?

答案。Answer. 请使用 FOR JSON PATH。Use FOR JSON PATH. 尽管 JSON 输出没有任何区别,但 AUTO 模式采用一些其他的逻辑,可检查是否应嵌套列。Although there is no difference in the JSON output, AUTO mode applies some additional logic that checks whether columns should be nested. 请将 PATH 作为默认选项。Consider PATH the default option.

创建一个嵌套的 JSON 结构Create a nested JSON structure

问题。Question. 我希望生成具有多个同级别数组的复杂 JSON。I want to produce complex JSON with several arrays on the same level. FOR JSON PATH 可以使用路径创建嵌套对象,FOR JSON AUTO 为每个表创建其他的嵌套级别。FOR JSON PATH can create nested objects using paths, and FOR JSON AUTO creates additional nesting level for each table. 这两个选项都不能够让我生成所需的输出。Neither one of these two options lets me generate the output I want. 我如何才能自定义现有选项不直接支持的 JSON 格式?How can I create a custom JSON format that the existing options don't directly support?

答案。Answer. 通过将 FOR JSON 查询添加为返回 JSON 文本的列表达式,可创建任何数据结构。You can create any data structure by adding FOR JSON queries as column expressions that return JSON text. 还可以使用 JSON_QUERY 函数手动创建 JSON。You can also create JSON manually by using the JSON_QUERY function. 下面的示例演示了这些方法。The following example demonstrates these techniques.

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 子对象,并且都包含在主要结果中。Every result of a FOR JSON query or the JSON_QUERY function in the column expressions is formatted as a separate nested JSON sub-object and included in the main result.

防止 FOR JSON 输出中的 JSON 被双转义Prevent double-escaped JSON in FOR JSON output

问题。Question. 我的 JSON 文本存储在一个表列中。I have JSON text stored in a table column. 我想将它包含到 FOR JSON 的输出中。I want to include it in the output of FOR JSON. 但是 FOR JSON 会转义 JSON 中的所有字符,因此我会得到 JSON 字符串,而不是嵌套对象,如下例所示。But FOR JSON escapes all characters in the JSON, so I'm getting a JSON string instead of a nested object, as shown in the following example.

SELECT 'Text' AS myText, '{"day":23}' AS myJson  
FOR JSON PATH  

此查询将生成以下输出。This query produces the following output.

[{"myText":"Text", "myJson":"{\"day\":23}"}]  

我如何才能防止此行为?How can I prevent this behavior? 我想让 {"day":23} 作为 JSON 对象而不是转义文本返回。I want {"day":23} to be returned as a JSON object and not as escaped text.

答案。Answer. 存储在文本列中的 JSON 或文字被当做文本处理。JSON stored in a text column or a literal is treated like any text. 也就是说,它会括在双引号内并进行转义。That is, it's surrounded with double quotes and escaped. 如果想返回未转义的 JSON 对象,请将 JSON 列作为参数传递给 JSON_QUERY 函数,如下例所示。If you want to return an unescaped JSON object, pass the JSON column as an argument to the JSON_QUERY function, as shown in the following example.

SELECT col1, col2, col3, JSON_QUERY(jsoncol1) AS jsoncol1  
FROM tab1  
FOR JSON PATH  

没有可选次要参数的 JSON_QUERY 仅将第一个参数作为结果返回。JSON_QUERY without its optional second parameter returns only the first argument as a result. 由于 JSON_QUERY 始终返回有效的 JSON,因此 FOR JSON 知道不需要对此结果进行转义。Since JSON_QUERY always returns valid JSON, FOR JSON knows that this result does not have to be escaped.

使用 WITHOUT_ARRAY_WRAPPER 子句生成的 JSON 在 FOR JSON 输出中被转义JSON generated with the WITHOUT_ARRAY_WRAPPER clause is escaped in FOR JSON output

问题。Question. 我正尝试通过使用 FOR JSON 和 WITHOUT_ARRAY_WRAPPER 选项设置列表达式的格式。I'm trying to format a column expression by using FOR JSON and the WITHOUT_ARRAY_WRAPPER option.

SELECT 'Text' as myText,  
   (SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as myJson  
FOR JSON PATH   

但 FOR JSON 查询返回的文本似乎被转义成了纯文本。It seems that the text returned by the FOR JSON query is escaped as plain text. 这种情况仅在指定了 WITHOUT_ARRAY_WRAPPER 的情况下才会发生。This happens only if WITHOUT_ARRAY_WRAPPER is specified. 为什么它不被视为 JSON 对象并以未转义的形式包含在结果中?Why isn't it treated as a JSON object and included unescaped in the result?

答案。Answer. 如果在内部 FOR JSON 中指定 WITHOUT_ARRAY_WRAPPER 选项,生成的 JSON 文本不一定是有效的 JSON。If you specify the WITHOUT_ARRAY_WRAPPER option in the inner FOR JSON, the resulting JSON text is not necessarily valid JSON. 因此,外部 FOR JSON 会假定其为纯文本,并对字符串进行转义。Therefore the outer FOR JSON assumes that this is plain text and escapes the string. 如果确定该 JSON 输出是有效的,请用 JSON_QUERY 函数包装它,将其提升为格式正确的 JSON,如下例所示。If you are sure that the JSON output is valid, wrap it with the JSON_QUERY function to promote it to properly formatted JSON, as shown in the following example.

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 and JSON input

使用 OPENJSON 从 JSON 文本中返回嵌套的 JSON 子对象Return a nested JSON sub-object from JSON text with OPENJSON

问题。Question. 我无法使用显式架构的 OPENJSON 打开包含标量值、对象和数组的复杂 JSON 对象数组。I can't open an array of complex JSON objects that contains both scalar values, objects, and arrays using OPENJSON with an explicit schema. 当我在 WITH 子句中引用键时,仅返回了标量值。When I reference a key in the WITH clause, only scalar values are returned. 对象和数组则返回为 NULL 值。Objects and arrays are returned as null values. 我如何将对象或数组提取为 JSON 对象?How can I extract objects or arrays as JSON objects?

答案。Answer. 如果希望对象或数组以列的形式返回,请在列定义中使用 AS JSON 选项,如下例所示。If you want to return an object or an array as a column, use the AS JSON option in the column definition, as shown in the following example.

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 返回长文本值Return long text value with OPENJSON instead of JSON_VALUE

问题。Question. 我在包含长文本的 JSON 文本中拥有描述键。I have description key in JSON text that contains long text. JSON_VALUE(@json, '$.description') 返回 NULL,而不是一个值。JSON_VALUE(@json, '$.description') returns NULL instead of a value.

答案。Answer. JSON_VALUE 旨在返回小的标量值。JSON_VALUE is designed to return small scalar values. 通常此函数返回 NULL,而不是溢出错误。Generally the function returns NULL instead of an overflow error. 如果你想返回更长的值,请使用支持 NVARCHAR(MAX) 值的 OPENJSON,如下例所示。If you want to return longer values, use OPENJSON, which supports NVARCHAR(MAX) values, as shown in the following example.

SELECT myText FROM OPENJSON(@json) WITH (myText NVARCHAR(MAX) '$.description')  

使用 OPENJSON 而不是 JSON_VALUE 处理重复键Handle duplicate keys with OPENJSON instead of JSON_VALUE

问题。Question. 我的 JSON 文本中有重复的键。I have duplicate keys in the JSON text. JSON_VALUE 仅返回在该路径上找到的第一个键。JSON_VALUE returns only the first key found on the path. 我如何返回名称相同的所有键?How can I return all keys that have the same name?

答案。Answer. 内置的 JSON 标量函数仅返回引用对象的第一个匹配项。The built-in JSON scalar functions return only the first occurrence of the referenced object. 如果需要多个键,请使用 OPENJSON 表值函数,如下例所示。If you need more than one key, use the OPENJSON table-valued function, as shown in the following example.

SELECT value FROM OPENJSON(@json, '$.info.settings')  
WHERE [key] = 'color'  

OPENJSON 要求兼容性级别 130OPENJSON requires compatibility level 130

问题。Question. 我正尝试在 SQL Server 2016 中运行 OPENJSON,但收到以下错误。I'm trying to run OPENJSON in SQL Server 2016 and I'm getting the following error.

Msg 208, Level 16, State 1 'Invalid object name OPENJSON'

答案。Answer. OPENJSON 函数仅在兼容性级别 130 下可用。The OPENJSON function is available only under compatibility level 130. 如果你的 DB 兼容性级别低于 130,则会隐藏 OPENJSON。If your DB compatibility level is lower than 130, OPENJSON is hidden. 其他 JSON 函数在所有兼容性级别均可用。Other JSON functions are available at all compatibility levels.

其他问题Other questions

引用 JSON 文本中中包含非字母数字字符的键Reference keys that contain non-alphanumeric characters in JSON text

问题。Question. 我的 JSON 文本中有包含非字母数字字符的键。I have non-alphanumeric characters in keys in my JSON text. 我如何引用这些属性?How can I reference these properties?

答案。Answer. 必须在 JSON 路径中用引号将它们括起来。You have to surround them with quotes in JSON paths. 例如,JSON_VALUE(@json, '$."$info"."First Name".value')For example, JSON_VALUE(@json, '$."$info"."First Name".value').

详细了解 SQL Server 和 Azure SQL 数据库中的 JSONLearn more about JSON in SQL Server and Azure SQL Database

Microsoft 视频Microsoft videos

有关 SQL Server 和 Azure SQL 数据库中内置 JSON 支持的视频介绍,请观看以下视频:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos: