sqlrowset 转化为json_使用 OPENJSON 分析和转换 JSON 数据 - SQL Server | Microsoft Docs

使用 OPENJSON 分析和转换 JSON 数据 (SQL Server)Parse and Transform JSON Data with OPENJSON (SQL Server)

06/03/2020

本文内容

适用于：Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics适用于：Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics

OPENJSON 行集函数可将 JSON 文本转换为一组行和列。The OPENJSON rowset function converts JSON text into a set of rows and columns. 使用 OPENJSON 将 JSON 集合转换为行集后，可以在返回的数据上运行任意 SQL 查询或将其插入到 SQL Server 表中。After you transform a JSON collection into a rowset with OPENJSON, you can run any SQL query on the returned data or insert it into a SQL Server table.

OPENJSON 函数采用单个 JSON 对象或 JSON 对象的集合，并将其转换为一行或多行。The OPENJSON function takes a single JSON object or a collection of JSON objects and transforms them into one or more rows. OPENJSON 函数默认返回以下数据：By default, the OPENJSON function returns the following data:

从 JSON 对象中，该函数返回在第一个级别找到的所有“键/值”对。From a JSON object, the function returns all the key/value pairs that it finds at the first level.

从 JSON 数组中，该函数返回数组的所有元素及其索引。From a JSON array, the function returns all the elements of the array with their indexes.

可以添加可选的 WITH 子句来提供显式定义输出结构的架构。You can add an optional WITH clause to provide a schema that explicitly defines the structure of the output.

选项 1 - 具有默认输出的 OPENJSONOption 1 - OPENJSON with the default output

在不提供结果的显式架构的情况下使用 OPENJSON 函数时(即，在 OPENJSON 之后不使用 WITH 子句)，该函数将返回包含以下三列的表：When you use the OPENJSON function without providing an explicit schema for the results - that is, without a WITH clause after OPENJSON - the function returns a table with the following three columns:

输入对象中属性的名称(或输入数组中元素的索引)。The name of the property in the input object (or the index of the element in the input array).

属性或数组元素的值。The value of the property or the array element.

类型(例如，字符串、数字、布尔值、数组或对象)。The type (for example, string, number, boolean, array, or object).

OPENJSON 以单独的行返回 JSON 对象的每个属性或数组的每个元素。OPENJSON returns each property of the JSON object, or each element of the array, as a separate row.

下面是使用具有默认架构(即不包含可选的 WITH 子句)的 OPENJSON 的快捷示例，该示例为 JSON 对象的每个属性返回一行。Here's a quick example that uses OPENJSON with the default schema - that is, without the optional WITH clause - and returns one row for each property of the JSON object.

示例Example

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *

FROM OPENJSON(@json);

结果Results

keykey

值value

typetype

namename

JohnJohn

11

surnamesurname

DoeDoe

11

ageage

4545

22

技能skills

["SQL","C#","MVC"]["SQL","C#","MVC"]

44

有关具有默认架构的 OPENJSON 的详细信息More info about OPENJSON with the default schema

选项 2 - 具有显式结构的 OPENJSON 输出Option 2 - OPENJSON output with an explicit structure

如果使用 OPENJSON 函数的 WITH 子句指定结果的架构，该函数返回的表只包含 WITH 子句中定义的列。When you specify a schema for the results by using the WITH clause of the OPENJSON function, the function returns a table with only the columns that you define in the WITH clause. 在可选的 WITH 子句中，指定一组输出列、列类型和每个输出值的 JSON 源属性的路径。In the optional WITH clause, you specify a set of output columns, their types, and the paths of the JSON source properties for each output value. OPENJSON 循环访问 JSON 对象的数组，读取每一列的指定路径上的值，并将值转换为指定类型。OPENJSON iterates through the array of JSON objects, reads the value on the specified path for each column, and converts the value to the specified type.

下面是使用具有 WITH 子句中显式指定的输出架构的 OPENJSON 快捷示例。Here's a quick example that uses OPENJSON with a schema for the output that you explicitly specify in the WITH clause.

示例Example

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'

)

结果Results

NumberNumber

DateDate

客户Customer

数量Quantity

SO43659SO43659

2011-05-31T00:00:002011-05-31T00:00:00

AW29825AW29825

11

SO43661SO43661

2011-06-01T00:00:002011-06-01T00:00:00

AW73565AW73565

33

此函数返回 JSON 数组的元素并将其格式化。This function returns and formats the elements of a JSON array.

对于 JSON 数组中的每个元素， OPENJSON 会在输出表中生成新的一行。For each element in the JSON array, OPENJSON generates a new row in the output table. JSON 数组中的两个元素将在返回的表中转换为两行。The two elements in the JSON array are converted into two rows in the returned table.

对于通过使用 colName type json_path 语法指定的每一列，OPENJSON 将指定路径上的每个数组元素中找到的值转换为指定类型。For each column, specified by using the colName type json_path syntax, OPENJSON converts the value found in each array element on the specified path to the specified type. 在此示例中，Date 列的值获取自路径 $.Order.Date 上的每个元素，并被转换为日期时间值。In this example, values for the Date column are taken from each element on the path $.Order.Date and converted to datetime values.

有关具有显式架构的 OPENJSON 的详细信息More info about OPENJSON with an explicit schema

OPENJSON 要求兼容性级别 130OPENJSON requires Compatibility Level 130

OPENJSON 函数仅在 兼容级别 130 下可用。The OPENJSON function is available only under compatibility level 130. 如果数据库兼容级别低于 130，SQL Server 将无法找到并运行 OPENJSON 函数。If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function. 其他内置 JSON 函数在所有兼容级别均可用。Other built-in JSON functions are available at all compatibility levels.

可以在 sys.databases 视图或数据库属性中查看兼容级别。You can check compatibility level in the sys.databases view or in database properties.

可以使用以下命令更改数据库的兼容级别：You can change the compatibility level of a database by using the following command:

ALTER DATABASE SET COMPATIBILITY_LEVEL = 130

详细了解 SQL Server 和 Azure SQL 数据库中的 JSONLearn more about JSON in SQL Server and Azure SQL Database

Microsoft 视频Microsoft videos

有关 SQL Server 和 Azure SQL 数据库中内置 JSON 支持的视频介绍，请观看以下视频：For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

另请参阅See Also