[]
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
结果表是:
Key | Value | Type |
---|---|---|
Name | David | 8 |
Age | 35 | 6 |
对返回结果表的说明:
列索引 | 列名 | 数据类型 | 描述 |
---|---|---|---|
0 | Key | string | JSON 属性名 |
1 | Value | object | JSON 属性值 |
2 | Type | integer | 此列返回数据类型值。具体请见下表。 |
数据类型 | 返回值 |
---|---|
None | 0 |
Object | 1 |
Array | 2 |
Constructor | 3 |
Property | 4 |
Comment | 5 |
Integer | 6 |
Float | 7 |
String | 8 |
Boolean | 9 |
Null | 10 |
Undefined | 11 |
Date | 12 |
Raw | 13 |
Bytes | 14 |
Guid | 15 |
Uri | 16 |
TimeSpan | 17 |
接下来为您介绍 UnwindJon 函数中各个参数的含义和具体用法。
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 是在 jsonExpression 中引用对象或数组时可选的 JSON 路径表达式。UnwindJson 会定位到指定位置处的 JSON 文本,并且仅分析引用的片段。
规则说明:
Jsonpath | 说明 |
---|---|
$ | 根对象/元素 |
* | 通配符,指所有对象/元素。 |
. or [ ] | 子级对象成员 |
.. | 递归下降 |
?( ) | 应用过滤器(脚本)表达式。 |
@ | 当前的对象/元素 |
[ , ] | XPath中的Union运算符导致节点集的组合。JSONPath允许将备用名称或数组索引作为一组。 |
[ ] | 下标运算符。在Javascript和JSON中,它是本机数组运算符。 |
[ start : end : step ] | 从ES4借鉴的数组切片运算符。 |
示例:
Jsonpath | 示例结果说明 |
---|---|
$.store.book[*].author | 商店中所有书籍的作者 |
$..author | 所有作者 |
$.store.* | 商店里所有的东西,包括一些书和一辆红色的自行车。 |
$.store..price | 商店中所有商品的价格。 |
$..book[2] | 第三本书 |
$..book[-1:] | 最后一本书。 |
$..book[0,1]$..book[:2] | 前两本书 |
$..book[?(@.isbn)] | 过滤所有具有isbn编号的书 |
$..book[?(@.price<10)] | 筛选所有价格低于10的书籍 |
$..* | XML文档中的所有元素。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 子句用于指示 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”
如果 jsonExpression 是 JSON 对象,则 UnwindJson 函数将列出所有属性及其值。
如果 jsonExpression 是 JSON 数组,它将列出所有数组元素的值。
示例1. 以下 SQL 扩展了 JSON 对象的所有属性:
select * from UnwindJson('{"Name":"David","Age":35}') tmp
结果表是:
Key | Value | Type |
---|---|---|
Name | David | 8 |
Age | 35 | 6 |
对返回结果表的说明:
列索引 | 列名 | 数据类型 | 描述 |
---|---|---|---|
0 | Key | string | JSON 属性名 |
1 | Value | object | JSON 属性值 |
2 | Type | integer | 此列返回数据类型值,详见 UnwindJson 返回的数据类型值 。 |
示例2. 以下 SQL 扩展了 JSON 对象的数组元素:
select * from UnwindJson('[1,3,"hi"]') tmp
结果表是:
Key | Value | Type |
---|---|---|
0 | 1 | 6 |
1 | 2 | 6 |
2 | hi | 8 |
对返回结果表的说明:
列索引 | 列名 | 数据类型 | 描述 |
---|---|---|---|
0 | Key | string | 数组元素索引的字符串格式 |
1 | Value | object | 数组元素的值 |
2 | Type | integer | 此列返回数据类型值,详见 UnwindJson 返回的数据类型值 。 |
示例3. 以下 SQL 列出了复杂的属性:
select * from UnwindJson('{"Name":"David","Tags":["GCEF","DEV"]}') tmp
结果表是:
Key | Value | Type |
---|---|---|
Name | David | 8 |
Tags | ["GCEF","DEV"] | 2 |
注意
JSON 对象或数组作为 Value 将被转换为全文。
强烈建议您将“Value”列转换为期望的数据类型,因为其原始类型是“Object”。
select CSTR(Value) as StringValue from UnwindJson('[1,3,"hi"]') tmp
在 UnwindJson 中添加 jsonPath 后,函数将给定 JSONPath 的 jsonExpression 进行解析。
结果表的架构为:
列索引 | 列名 | 数据类型 | 说明 |
---|---|---|---|
0 | Key | string | 解析的JSON内容中索引的字符串格式 |
1 | Value | object | 解析的Json内容 |
2 | Type | integer | 此列返回数据类型值,详见 UnwindJson 返回的数据类型值 。 |
示例1.
select * from UnwindJson('{"String":"David"}', '$.String') tmp
结果表:
Key | Value | Type |
---|---|---|
0 | David | 8 |
示例2.
select * from UnwindJson('{"Name":"David","Tags":[{"Group":[1,2]},{"Group":[3]}]}', '$.Tags[*]') tmp
结果表:
Key | Value | Type |
---|---|---|
0 | {"Group":[1,2]} | 1 |
1 | {"Group":[3]} | 1 |
示例3.
select * from UnwindJson('{"Name":"David","Tags":[{"Group":[1,2]},{"Group":[3]}]}', '$.Tags[*].Groups[*]') tmp
结果表:
Key | Value | Type |
---|---|---|
0 | 1 | 6 |
1 | 2 | 6 |
2 | 3 | 6 |
示例4.
select * from UnwindJson('[{"Data1":[1]},{"Data2":[2]}]', '$[*].*') tmp
结果表:
Key | Value | Type |
---|---|---|
0 | 1 | 6 |
1 | 2 | 6 |
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
)
结果表:
UserName | Age |
---|---|
David | 35 |
示例2:
select * from UnwindJson('[{"Name":"Thomas"},{"Name":"David"}]') tmp with (
UserName varchar '$.Name'
)
结果表:
UserName |
---|
Thomas |
David |
示例3:
select * from UnwindJson('[{"Name":"Thomas","Tags":[101,102]},{"Name":"David",Tags:[101]}]') tmp with (
Name varchar ,
Tags varchar AS JSON
)
结果表:
Name | Tags |
---|---|
Thomas | [101,102] |
David | [101] |
示例4:
select * from UnwindJson('[{"Name":"Thomas","Location":{"City":"XA"}},{"Name":"David","Location":null}]') tmp with (
Name varchar ,
City varchar '$.Location.City'
)
结果表:
Name | City |
---|---|
Thomas | XA |
David | null |
注意
每个列都可以定义自己的严格模式。
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'
)
结果表:
Id | DepartmentName |
---|---|
1 | DD1 |
2 | DD2 |