[]
        
(Showing Draft Content)

表值函数UnwindJson

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

此列返回数据类型值。具体请见下表。

UnwindJson 返回的数据类型值

数据类型

返回值

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 参数

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

说明

$

根对象/元素

*

通配符,指所有对象/元素。

. 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 子句

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

结果表是:

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 ( jsonExpression, jsonPath)

在 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 ( 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
)

结果表:

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

结果表:

Id

DepartmentName

1

DD1

2

DD2