功能概述

UnwindJson 是一种表值类型的函数，可分析 JSON 文本，并以行和列的形式返回 JSON 输入的对象和属性。

换句话说，UnwindJson 对 JSON 文档提供行集视图，并可以指定行集中的列以及用于填充列的 JSON 属性路径，它返回一个虚拟表。

我们可以像使用其他真实表一样在 SQL 语句的 from 子句以及表连接函数 Cross Apply 和 Outer Apply 中使用它们。

基本语法

UnwindJson ( jsonExpression [ , jsonPath ] )  [ <with-clause> ]
<with_clause> ::+ WITH ( { colName type [ column_path ] [ AS JSON ] } [,....n]

其中，jsonExpression 为必须项，jsonPath 和 with 语句为可选项。

注意

在使用表值函数时，必须为返回的虚拟表起一个别名，否则 SQL 语句将被视为无效。

如：select * from UnwindJson(jsonExpression,jsonpath) tmp with (...)

其中 tmp 就是 unwindJson 返回表的别名。后续使用该表时，引用 tmp 即可。

如果不使用 with 子句，UnwindJson 默认返回三列，列名分别为“Key”、“Value” 和“Type”。

比如：

select * from UnwindJson('{"Name":"David","Age":35}') tmp

结果表是：

对返回结果表的说明：

UnwindJson 返回的数据类型值

接下来为您介绍 UnwindJon 函数中各个参数的含义和具体用法。

JsonExpression 参数

JsonExpression 是字符串类型的函数参数，表示 JSON 内容。您可以发送带有任何表达式的值，包括引用列的表达式。

JSON 内容可以是单个JSON 对象或 JSON 数组。

UnwindJson 循环访问 JsonExpression 中的数组元素或对象的属性，并为每个元素或属性返回一行。

JsonExpression 示例：

UnwindJson('{"Name":"David"}')
UnwindJson('[1,2,3]')
UnwindJson('[{"Name":"David"},{"Name":"Sherlock"}]')
UnwindJson(Table1.JsonColumn)
UnwindJson(@commandParameter)

注意

如果 jsonExpression 为 null 或无效的 JSON 结构，则 UnwindJson 会引发异常。

JsonPath 参数

JsonPath 是在 jsonExpression 中引用对象或数组时可选的 JSON 路径表达式。UnwindJson 会定位到指定位置处的 JSON 文本，并且仅分析引用的片段。

规则说明：

示例：

严格模式：

如果您在 JsonPath 的内容之前添加 严格的 字符串 ，则表明 JSON 解析器将应用严格模式。

这意味着，如果解析器找不到 JsonPath 指定的对象，则它将报告错误，而不在严格模式的情况下返回 DBNull。

默认不采用严格模式。

以下表达式包含严格模式下的错误：

UnwindJson('{}', 'strict $.Name')
UnwindJson('[1,2]', 'strict $[8]')

即使在严格模式下，也允许选择具有空值的空数组或对象：

UnwindJson('{"Name":null}', 'strict $.Name')
UnwindJson('{"Array":[]}', 'strict $.Array')
UnwindJson('{"Array":[]}', 'strict $.Array[*]')

注意

JsonPath 参数只能是常量/参数表达式，这意味着它不能引用表的列。以下表达式有效：

UnwindJson('{}','$') 
UnwindJson('{}',@commandParameter)
UnwindJson('{}','$.'+@commandParameter)
UnwindJson('{}','$'+'.'+'sth')

如果 JsonPath 为 null 或空字符串，则 UnwindJson 返回的表中没有行。

*按照惯例，当Json属性中包含特殊字符，。[]（）'-@$：？时，应避免使用[‘propertyName‘]格式引用。

UnwindJson('{"FirstName.LastName":"David.Ma"}','$.[''FirstName.LastName'']')

由于上述JsonPath是按原义格式发送的，因此您可以看到 JsonPath 中的单引号被手动转义为双引号。如果 JsonPath 是来自命令参数的，则无需进行任何处理。

with 子句

With 子句用于指示 UnwindJson 如何解析 JSON内容并填充列数据。

一个 with 子句中包含几个（至少一个）列定义，每个都有一个列名，一个数据类型和一个可选的 JsonPath 描述符。

如下示例中定义了三个列。

WITH (
    UserName  varchar  '$.Name' ,
    Age       integer           ,
    Location  varchar   AS JSON
)

列定义中的数据类型

有效的数据类型包括：Integer, Single, Double, Decimal, Boolean, DateTime, Varchar

列定义中的 JsonPath

上例中的 UserName 通过解析具有给定路径'$ .Name'的 JSON 内容，以获得标量值。

列的数据类型被指定为'varchar'，这意味着已解析的 JSON 内容将转换为字符串。

• 如果'$ .Name'指向 JSON 属性，则 UserName 列的值是该属性的单个值。它可以是整数，布尔值或其他形式，但最终它将被转换为字符串类型，因为指定了'varchar'数据类型。

• 如果'$ .Name'指向 JSON 数组，则该值为 null。

• 如果“ $ .Name”指向嵌套的 JSON 对象，则该值为 null。

• 如果'$ .Name'指向空，则该值为 null。

列定义中的JsonPath也可以使用严格模式。如果将路径更改为“ strict $ .Name”，则会出现错误，提示未选择任何元素。

也可以省略列定义中的 JsonPath，如此的话，列名将作为路径。通过解析带有隐式路径的JSON内容（在此示例中为'$ .Age'），定义Age以获取标量值。

列定义中的AS JSON

上例中的 Location 使用了AS JSON 。

它实际使用了隐式路径“ $ .Location”解析JSON内容，并获取解析的JSON 内容全文。

AS JSON 表示，将已解析的 JSON 全文都设置为列值。

由于AS JSON 始终返回字符串，因此需要显式数据类型 'varchar'。否则将报告错误。

注意

ASJSON 周围没有单引号。

使用 ASJSON 选项，该列必须将数据类型定义为“varchar”

示例讲解

UnwindJson（jsonExpression）

如果 jsonExpression 是 JSON 对象，则 UnwindJson 函数将列出所有属性及其值。

如果 jsonExpression 是 JSON 数组，它将列出所有数组元素的值。

示例1. 以下 SQL 扩展了 JSON 对象的所有属性：

select * from UnwindJson('{"Name":"David","Age":35}') tmp

结果表是：

对返回结果表的说明：

示例2. 以下 SQL 扩展了 JSON 对象的数组元素：

select * from UnwindJson('[1,3,"hi"]') tmp

结果表是：

对返回结果表的说明：

示例3. 以下 SQL 列出了复杂的属性：

select * from UnwindJson('{"Name":"David","Tags":["GCEF","DEV"]}') tmp

结果表是：

注意

JSON 对象或数组作为 Value 将被转换为全文。

强烈建议您将“Value”列转换为期望的数据类型，因为其原始类型是“Object”。

select CSTR(Value) as StringValue from UnwindJson('[1,3,"hi"]') tmp

UnwindJson ( jsonExpression, jsonPath)

在 UnwindJson 中添加 jsonPath 后，函数将给定 JSONPath 的 jsonExpression 进行解析。

结果表的架构为：

示例1.

select * from UnwindJson('{"String":"David"}', '$.String') tmp

结果表：

示例2.

select * from UnwindJson('{"Name":"David","Tags":[{"Group":[1,2]},{"Group":[3]}]}', '$.Tags[*]') tmp

结果表：

示例3.

select * from UnwindJson('{"Name":"David","Tags":[{"Group":[1,2]},{"Group":[3]}]}', '$.Tags[*].Groups[*]') tmp

结果表：

示例4.

select * from UnwindJson('[{"Data1":[1]},{"Data2":[2]}]', '$[*].*') tmp

结果表：

UnwindJson ( jsonExpression ) with ( ... )

UnwindJson 函数将按照 with 子句中各列指定的路径提取jsonExpression。

返回表的架构与 with 子句中定义的完全匹配。

如果 jsonExpression 是 JSON 对象，将返回零或一个数据行。

如果 jsonExpression 是 JSON 数组，则将返回计数等于数组元素计数的行。

请注意，jsonExpression 可能是一个 JSON 数组，在这种情况下，列定义上的路径基于临时 JSON 根（实际上是 JSON 数组的元素）工作。

示例1.

select * from UnwindJson('{"Name":"David","Age":35}') tmp with (
    UserName varchar '$.Name',
    Age  integer
)

结果表：

示例2：

select * from UnwindJson('[{"Name":"Thomas"},{"Name":"David"}]') tmp with (
    UserName varchar '$.Name'
)

结果表：

示例3：

select * from UnwindJson('[{"Name":"Thomas","Tags":[101,102]},{"Name":"David",Tags:[101]}]') tmp with (
    Name varchar ,
    Tags varchar AS JSON
)

结果表：

示例4：

select * from UnwindJson('[{"Name":"Thomas","Location":{"City":"XA"}},{"Name":"David","Location":null}]') tmp with (
    Name varchar ,
    City varchar '$.Location.City'
)

结果表：

注意

每个列都可以定义自己的严格模式。

UnwindJson ( jsonExpression, jsonPath ) with ( ... )

UnwindJson 函数的这个用法是前一个用法的增强版。

不同的是，在每个列的 JsonPath 工作之前，使用整个 JsonPath 提前解析 jsonExpression。

在实际应用场景中，它非常适用于 jsonExpression 是单个 JSON 对象的场景。

示例：

select * from UnwindJson('{"Company":{"Name":"GrapeCity","Departments":[{"Id":1,"Name":"DD1"},{"Id":2,"Name":"DD2"}]}}', '$.Company.Departments[*]') tmp with (
    Id integer,
    DepartmentName varchar '$.Name'
)

结果表：