OPENJSON (Transact-SQL)

项目
05/23/2023

适用于：SQL Server 2016 (13.x) 及更高版本Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Microsoft Fabric 中的 SQL 分析终结点 Microsoft Fabric 中的仓库

OPENJSON 是一种表值函数，可分析 JSON 文本，并以行和列的形式从 JSON 输入返回对象和属性。换句话说，OPENJSON 对 JSON 文档提供行集视图。可以显式指定行集中的列以及用于填充列的 JSON 属性路径。由于 OPENJSON 返回一组行，因此可以在 Transact-SQL 语句的 FROM 子句中使用 OPENJSON，就如同可以使用任何其他表、视图或表值函数一样。

对于不能直接使用 JSON 的应用或服务，可以使用 OPENJSON 将 JSON 数据导入 SQL Server，或者将 JSON 数据转换为关系格式。

注意

OPENJSON 函数仅在兼容级别 130 或更高级别下可用。如果数据库兼容级别低于 130，SQL Server 将无法找到并运行 OPENJSON 函数。其他 JSON 函数在所有兼容性级别均可用。

可以在 sys.databases 视图或数据库属性中查看兼容级别。可以使用以下命令更改数据库的兼容级别：

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Transact-SQL 语法约定

语法

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

OPENJSON 表值函数会分析作为第一个参数提供的 jsonExpression，并返回包含来自表达式中 JSON 对象的数据的一行或多行。 jsonExpression 可以包含嵌套子对象。如果要分析 jsonExpression 中的子对象，则可以为 JSON 子对象指定 path参数。

openjson

Syntax for OPENJSON TVF

默认情况下，OPENJSON 表值函数返回三列，这些列包含在 jsonExpression 中找到的每个 {键:值} 对的键名称、值和类型。作为替代方法，可以通过提供 with_clause 来显式指定 OPENJSON 返回的结果集的架构。

with_clause

Syntax for WITH clause in OPENJSON TVF

with_clause 包含 OPENJSON 要返回的列及其类型的列表。默认情况下，OPENJSON 将 jsonExpression 中的键与 with_clause 中的列名进行匹配（在此情况下，匹配键意味着它区分大小写）。如果列名与键名称不匹配，则可以提供可选的 column_path，它是在 jsonExpression 中引用键的 JSON 路径表达式。

参数

jsonExpression

是包含 JSON 文本的 Unicode 字符表达式。

OPENJSON 循环访问 JSON 表达式中的数组的元素或对象的属性，并为每个元素或属性返回一行。下面的示例返回作为 jsonExpression 提供的对象的每个属性：

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

结果：

key	值	type
String_value	John	1
DoublePrecisionFloatingPoint_value	45	2
DoublePrecisionFloatingPoint_value	2.3456	2
BooleanTrue_value	true	3
BooleanFalse_value	false	3
Null_value	Null	0
Array_value	["a","r","r","a","y"]	4
Object_value	{"obj":"ect"}	5

DoublePrecisionFloatingPoint_value 遵循 IEEE-754。

路径

是在 jsonExpression 中引用对象或数组的可选 JSON 路径表达式。 OPENJSON 会定位到指定位置处的 JSON 文本，并且仅分析引用的片段。有关详细信息，请参阅 JSON 路径表达式 (SQL Server)。

在 SQL Server 2017 (14.x) 和 Azure SQL 数据库中，可提供变量作为 path 的值。

下面的示例通过指定 path 来返回嵌套对象：

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

结果

密钥	值
0	en-GB
1	en-UK
2	de-AT
3	es-AR
4	sr-Cyrl

当 OPENJSON 分析 JSON 数组时，该函数以键的形式返回 JSON 文本中元素的索引。

用于将路径各步与 JSON 表达式的属性进行匹配的比较不区分大小写且无法识别排序规则（即是 BIN2 比较）。

数组元素标识

Azure Synapse Analytics 中的无服务器 SQL 池中的 OPENJSON 函数可以自动生成每一行的标识并作为结果返回。在列定义后面的 JSON 路径中，使用表达式 $.sql:identity() 来指定标识列。 JSON 路径表达式中包含此值的列将为该函数所分析的 JSON 数组中的每个元素都生成一个唯一的基于 0 的数。标识值表示数组元素的位置/索引。

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

结果

月份	temp	month_id
一月	10	0
二月	12	1
三月	15	2
四月	17	3
五月	23	4
Jun	27	5

该标识仅在 Synapse Analytics 中的无服务器 SQL 池中可用。

with_clause

显式定义 OPENJSON 函数要返回的输出架构。可选 with_clause 可以包含以下元素：

colName 是输出列的名称。

默认情况下，OPENJSON 使用列的名称与 JSON 文本中的属性进行匹配。例如，如果在架构中指定列 name，则 OPENJSON 会尝试使用 JSON 文本中的属性“name”填充此列。可以使用 column_path 参数替代此默认映射。

type
是输出列的数据类型。

注意

如果还使用 AS JSON 选项，则列 type 必须是 NVARCHAR(MAX)。

column_path
是指定要在指定列中返回的属性的 JSON 路径。有关详细信息，请参阅本主题前面的 path 参数说明。

使用 column_path 可在输出列的名称与属性的名称不匹配时替代重写默认映射规则。

用于将路径各步与 JSON 表达式的属性进行匹配的比较不区分大小写且无法识别排序规则（即是 BIN2 比较）。

有关路径的详细信息，请参阅 JSON 路径表达式 (SQL Server)。

AS JSON
在列定义中使用 AS JSON 选项可指定引用的属性包含内部 JSON 对象或数组。如果指定 AS JSON 选项，列的类型必须是 NVARCHAR(MAX)。

如果没有为列指定 AS JSON，则函数会从指定路径上的指定 JSON 属性返回标量值（例如 int、string、true、false）。如果路径表示对象或数组，并且在指定路径上找不到属性，则函数在宽松模式下返回 NULL，或在严格模式下返回错误。此行为与 JSON_VALUE 函数的行为类似。
如果为列指定 AS JSON，则函数会从指定路径上的指定 JSON 属性返回 JSON 片段。如果路径表示标量值，并且在指定路径上找不到属性，则函数在宽松模式下返回 NULL，或在严格模式下返回错误。此行为与 JSON_QUERY 函数的行为类似。

注意

如果要从 JSON 属性返回嵌套 JSON 片段，则必须提供 AS JSON 标志。未使用此选项时，如果找不到属性，则 OPENJSON 会返回 NULL 值而不是引用的 JSON 对象或数组，或在严格模式下返回运行时错误。

例如，以下查询返回数组的元素并进行格式设置：

DECLARE @json NVARCHAR(MAX) = 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',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

结果

Number	Date	客户	数量	订单
SO43659	2011-05-31T00:00:00	AW29825	1	{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661	2011-06-01T00:00:00	AW73565	3	{"Number":"SO43661","Date":"2011-06-01T00:00:00"}

返回值

OPENJSON 函数返回的列取决于 WITH 选项。

当调用具有默认架构的 OPENJSON 时（即当未在 WITH 子句中指定显式架构时），该函数返回具有以下各列的表：
1. Key。一个 nvarchar(4000) 值，包含指定属性的名称或指定数组中元素的索引。键列具有 BIN2 排序规则。
2. 值。一个 nvarchar(max) 值，包含属性的值。值列从 jsonExpression 继承其排序规则。
3. Type。一个 int 值，包含值的类型。仅当使用具有默认架构的 OPENJSON 时，才返回 Type 列。类型列具有以下值之一：
  
  类型列的值 JSON 数据类型
  
  0 Null
  
  1 字符串
  
  2 数字
  
  3 true/false
  
  4 array
  
  5 对象 (object)
仅返回第一级属性。如果 JSON 文本的格式不正确，则语句会失败。
当调用 OPENJSON 并且在 WITH 子句中指定显式架构时，该函数返回具有在 WITH 子句中定义的架构的表。

类型列的值	JSON 数据类型
0	Null
1	字符串
2	数字
3	true/false
4	array
5	对象 (object)

注意

只有在你结合使用 OPENJSON 和默认架构时，“Key”、“Value”和“Type”列才会返回，它们不适用于显式架构。

备注

在 OPENJSON 的第二个参数或 with_clause 中使用的 json_path可以以 lax 或 strict 关键字开头。

在 lax 模式下，OPENJSON 在找不到指定路径上的对象或值时不会引发错误。如果找不到路径，则 OPENJSON 返回空结果集或 NULL 值。
在 strict 模式下，OPENJSON 在找不到路径时返回错误。

此页面上的某些示例显式指定路径模式（宽松或严格）。路径模式是可选项。如果未显式指定路径模式，则宽松模式是默认值。有关路径模式和路径表达式的详细信息，请参阅 JSON 路径表达式 (SQL Server)。

with_clause 中的列名会与 JSON 文本中的键进行匹配。如果指定列名 [Address.Country]，则它会与键 Address.Country 进行匹配。如果要在对象 Address 中引用嵌套键 Country，则必须在列路径中指定路径 $.Address.Country。

json_path 可以包含具有字母数字字符的键。如果在键中包含特殊字符，则使用双引号在 json_path 中对键名称进行转义。例如，在以下 JSON 文本中，$."my key $1".regularKey."key with . dot" 与值 1 进行匹配：

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

示例

示例 1 - 将 JSON 数组转换为临时表

下面的示例以 JSON 数字数组的形式提供标识符的列表。查询将 JSON 数组转换为标识符表，并筛选有指定 ID 的所有产品。

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

此查询与下面的示例等效。但是在下面的示例中，必须在查询中嵌入数字而不是将它们作为参数进行传递。

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

示例 2 - 合并来自两个 JSON 对象的属性

下面的示例选择两个 JSON 对象的所有属性的并集。这两个对象具有重复的 name 属性。该示例使用键值从结果中排除重复行。

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

示例 3 - 使用 CROSS APPLY 联接包含存储在表单元格中的 JSON 数据的行

在下面的示例中，SalesOrderHeader 表具有一个 SalesReason 文本列，它包含采用 JSON 格式的 SalesOrderReasons 的数组。 SalesOrderReasons 对象包含属性，例如 Quality 和 Manufacturer。该示例创建一个报表，它将每个销售订单行联接到相关销售原因。 OPENJSON 运算符会扩展销售原因的 JSON 数组，如同原因是存储在单独的子表中一样。 CROSS APPLY 运算符随后将每个销售订单行与 OPENJSON 表值函数返回的行联接。

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

提示

必须扩展存储在各个字段中的 JSON 数组，并将它们与其父行联接时，通常使用 Transact-SQL CROSS APPLY 运算符。有关 CROSS APPLY 的详细信息，请参阅 FROM (Transact-SQL)。

可以通过将 OPENJSON 与要返回的行的显式定义架构一起使用，来重写相同查询：

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

在此示例中，$ 路径引用数组中的每个元素。如果要显式强制转换返回值，则可以使用此类型的查询。

示例 4 - 使用 CROSS APPLY 合并关系行和 JSON 元素

以下查询将关系行和 JSON 元素合并到下表中显示的结果中。

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street VARCHAR(500) ,  postcode VARCHAR(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

结果

title	street	postcode	lon	lat
Whole Food Markets	17991 Redmond Way	WA 98052	47.666124	-122.10155
Sears	148th Ave NE	WA 98052	47.63024	-122.141246,17

示例 5 - 将 JSON 数据导入 SQL Server 中

下面的示例展示了将整个 JSON 对象加载到 SQL Server 表中。

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  
   
  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id INT,  
        firstName NVARCHAR(50), lastName NVARCHAR(50),   
        isAlive BIT, age INT,  
        dateOfBirth DATETIME, spouse NVARCHAR(50))

示例 6 - 使用 JSON 内容的简单示例

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

OPENJSON (Transact-SQL)

语法

openjson

with_clause

参数

jsonExpression

路径

数组元素标识

with_clause

返回值

备注

示例

示例 1 - 将 JSON 数组转换为临时表

示例 2 - 合并来自两个 JSON 对象的属性

示例 3 - 使用 CROSS APPLY 联接包含存储在表单元格中的 JSON 数据的行

示例 4 - 使用 CROSS APPLY 合并关系行和 JSON 元素

示例 5 - 将 JSON 数据导入 SQL Server 中

示例 6 - 使用 JSON 内容的简单示例

另请参阅

反馈

反馈

其他资源