通过

使用 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;

相关内容