适用于: SQL Server Azure SQL 数据库 Azure Synapse Analytics (SQL DW) 并行数据仓库
OPENJSON 行集函数可将 JSON 文本转换为一组行和列. 使用 OPENJSON 将 JSON 集合转换为行集后, 可以在返回的数据上运行任意 SQL 查询或将其插入到 SQL Server 表中 .
OPENJSON 函数采用单个 JSON 对象或 JSON 对象的集合, 并将其转换为一行或多行. OPENJSON 函数默认返回以下数据 :
从 JSON 对象中, 该函数返回在第一个级别找到的所有 "键 / 值" 对.
从 JSON 数组中, 该函数返回数组的所有元素及其索引.
可以添加可选的 WITH 子句来提供显式定义输出结构的架构 .
选项 1 - 具有默认输出的 OPENJSON
在不提供结果的显式架构的情况下使用 OPENJSON 函数时(即, 在 OPENJSON 之后不使用 WITH 子句), 该函数将返回包含以下三列的表 :
输入对象中属性的名称(或输入数组中元素的索引) .
属性或数组元素的值 .
类型(例如, 字符串, 数字, 布尔值, 数组或对象) .
OPENJSON 以单独的行返回 JSON 对象的每个属性或数组的每个元素 .
下面是使用具有默认架构 (即不包含可选的 WITH 子句) 的 OPENJSON 的快捷示例, 该示例为 JSON 对象的每个属性返回一行.
示例
- DECLARE @JSON NVARCHAR(MAX)
- SET @JSON='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';
- SELECT *
- FROM OPENJSON(@JSON);
结果
key | 值 | type |
---|---|---|
name | John | 1 |
surname | Doe | 1 |
age | 45 | 2 |
技能 | ["SQL","C#","MVC"] | 4 |
有关具有默认架构的 OPENJSON 的详细信息
有关详细信息和更多示例, 请参阅使用具有默认架构的 OPENJSON (SQL Server).
有关语法和用法的信息, 请参阅 OPENJSON (Transact-SQL)下可用.
选项 2 - 具有显式结构的 OPENJSON 输出
如果使用 OPENJSON 函数的 WITH 子句指定结果的架构, 该函数返回的表只包含 WITH 子句中定义的列. 在可选的 WITH 子句中, 指定一组输出列, 列类型和每个输出值的 JSON 源属性的路径 . OPENJSON 循环访问 JSON 对象的数组, 读取每一列的指定路径上的值, 并将值转换为指定类型.
下面是使用具有 WITH 子句中显式指定的输出架构的 OPENJSON 快捷示例 .
示例
- DECLARE @JSON NVARCHAR(MAX)
- SET @JSON =
- N'[
- {
- "Order": {
- "Number":"SO43659",
- "Date":"2011-05-31T00:00:00"
- },
- "AccountNumber":"AW29825",
- "Item": {
- "Price":2024.9940,
- "Quantity":1
- }
- },
- {
- "Order": {
- "Number":"SO43661",
- "Date":"2011-06-01T00:00:00"
- },
- "AccountNumber":"AW73565",
- "Item": {
- "Price":2024.9940,
- "Quantity":3
- }
- }
- ]'
- SELECT * FROM
- OPENJSON ( @JSON )
- WITH (
- Number varchar(200) '$.Order.Number' ,
- Date datetime '$.Order.Date',
- Customer varchar(200) '$.AccountNumber',
- Quantity int '$.Item.Quantity'
- )
结果
Number | Date | 客户 | 数量 |
---|---|---|---|
SO43659 | 2011-05-31T00:00:00 | AW29825 | 1 |
SO43661 | 2011-06-01T00:00:00 | AW73565 | 3 |
此函数返回 JSON 数组的元素并将其格式化.
对于 JSON 数组中的每个元素, OPENJSON 会在输出表中生成新的一行. JSON 数组中的两个元素将在返回的表中转换为两行.
对于通过使用 colName type json_path 语法指定的每一列, OPENJSON 将指定路径上的每个数组元素中找到的值转换为指定类型 . 在此示例中, Date 列的值获取自路径 $.Order.Date 上的每个元素, 并被转换为日期时间值.
有关具有显式架构的 OPENJSON 的详细信息
有关详细信息和更多示例, 请参阅使用具有显示架构的 OPENJSON (SQL Server).
有关语法和用法的信息, 请参阅 OPENJSON (Transact-SQL)下可用.
OPENJSON 要求兼容性级别 130
OPENJSON 函数仅在 兼容级别 130 下可用. 如果数据库兼容级别低于 130,SQL Server 将无法找到并运行 OPENJSON 函数 . 其他内置 JSON 函数在所有兼容级别均可用.
可以在 sys.databases 视图或数据库属性中查看兼容级别.
可以使用以下命令更改数据库的兼容级别:
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130
来源: http://www.bubuko.com/infodetail-3497636.html