使用 OPENJSON 分析和转换 JSON 数据
适用范围:SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics
OPENJSON
行集函数可将 JSON 文本转换为一组行和列。 使用 OPENJSON
将 JSON 集合转换为行集后,可以在返回的数据上运行任意 SQL 查询或将其插入到 SQL Server 表中。 有关在 SQL Server 数据库引擎中使用 JSON 数据的详细信息,请参阅 SQL Server 中的 JSON 数据。
OPENJSON
函数提取单个 JSON 对象或 JSON 对象的集合,并将其转换为一行或多行。 默认情况下,OPENJSON
函数会返回以下数据:
- 从 JSON 对象中,该函数返回在第一个级别找到的所有“键/值”对。
- 从 JSON 数组中,该函数返回数组的所有元素及其索引。
可以添加可选的 WITH
子句来提供显式定义输出结构的架构。
具有默认输出的 OPENJSON
在不提供结果的显式架构的情况下使用 OPENJSON
函数时(即在 OPENJSON
之后不使用 WITH
子句),该函数将返回包含以下三列的表:
- 输入对象中属性的
name
(或输入数组中元素的索引)。 - 属性或数组元素的
value
。 type
(例如,字符串、数字、布尔值、数组或对象)。
OPENJSON
以单独的行返回 JSON 对象的每个属性或数组的每个元素。
以下示例使用具有默认架构的 OPENJSON
(即不包含可选的 WITH
子句),并为 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 |
skills |
[ "SQL" ,"C#" ,"MVC" ] |
4 |
有关详细信息和更多示例,请参阅使用具有默认架构的 OPENJSON。
有关语法和用法的信息,请参阅 OPENJSON。
具有显式结构的 OPENJSON 输出
如果使用 OPENJSON
函数的 WITH
子句指定结果的架构,该函数返回的表将只包含 WITH
子句中定义的列。 在可选的 WITH
子句中,指定一组输出列、列类型和每个输出值的 JSON 源属性的路径。 OPENJSON
循环访问 JSON 对象的数组,读取每一列的指定路径上的值,并将值转换为指定类型。
以下示例使用的 OPENJSON
具有你在 WITH
子句中显式指定的输出架构。
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{
"Order": {
"Number": "SO43659",
"Date": "2024-05-31T00:00:00"
},
"AccountNumber": "AW29825",
"Item": {
"Price": 2024.9940,
"Quantity": 1
}
},
{
"Order": {
"Number": "SO43661",
"Date": "2024-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'
);
结果集如下。
数字 | Date | 客户 | 数量 |
---|---|---|---|
SO43659 |
2024-05-31T00:00:00 |
AW29825 |
1 |
SO43661 |
2024-06-01T00:00:00 |
AW73565 |
3 |
此函数返回 JSON 数组的元素并将其格式化。
对于 JSON 数组中的每个元素,
OPENJSON
会在输出表中生成新的一行。 JSON 数组中的两个元素将在返回的表中转换为两行。对于使用
colName type json_path
语法指定的每一列,OPENJSON
将指定路径上的每个数组元素中找到的值转换为指定类型。 在此示例中,Date
列的值获取自路径$.Order.Date
上的每个元素,并被转换为日期时间值。
有关详细信息和更多示例,请参阅使用具有显示架构的 OPENJSON (SQL Server)。
有关语法和用法的信息,请参阅 OPENJSON。
OPENJSON 要求兼容性级别 130
OPENJSON
函数仅在 130
或更高兼容性级别下可用。 如果数据库兼容性级别低于 130
,SQL Server 将无法找到并运行 OPENJSON
函数。 其他内置 JSON 函数在所有兼容级别均可用。
可以在 sys.databases
视图或数据库属性中检查兼容性级别,并使用以下命令更改数据库的兼容性级别:
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;