1. 内容概述

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

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

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

2. 基本语法

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

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

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

比如：

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

结果表是：

对返回结果表的说明：

3. UnwindJson 返回的数据类型值

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

4. 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)

5. 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[*]')

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

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

6. 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'。否则将报告错误。

7. 示例讲解

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

结果表是：

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'
)

结果表：