使用 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 子句),该函数将返回包含以下三列的表:

  1. 输入对象中属性的 name(或输入数组中元素的索引)。
  2. 属性或数组元素的 value
  3. 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;