OPENJSON (Transact-SQL)

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

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

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

Transact-SQL 语法约定

Syntax

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

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

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

openjson

默认情况下，OPENJSON表值函数返回三列，其中包含 jsonExpression 中找到的每个key:value对的键名称、值和类型。 或者，可以通过提供with_clause显式指定返回的结果集OPENJSON的架构。

with_clause

with_clause包含一个列列表，其中包含要OPENJSON返回的列类型。 默认情况下，将 OPENJSONjsonExpression 中的键与 with_clause 中的列名匹配（在本例中，匹配键表示区分大小写）。 如果列名与键名称不匹配，可以提供可选的column_path，这是引用 jsonExpression 中的键的 JSON 路径表达式。

Arguments

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);

Results:

• DoublePrecisionFloatingPoint_value 遵循 IEEE-754。

path

引用 jsonExpression 中的对象或数组的可选 JSON 路径表达式。 OPENJSON 查找位于指定位置的 JSON 文本，并仅分析引用的片段。 有关详细信息，请参阅 JSON 路径表达式。

可以将变量作为 路径的值提供。 （SQL Server 2016（13.x）和早期版本中不支持此功能。

以下示例通过指定 路径返回嵌套对象：

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"')

Results

分析 JSON 数组时 OPENJSON ，该函数将 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

Results

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

with_clause

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

colName

输出列的名称。

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

type

输出列的数据类型。

column_path

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

当输出列的名称与属性的名称不匹配时，请使用 column_path 替代默认映射规则。

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

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

AS JSON

AS JSON使用列定义中的选项指定引用的属性包含内部 JSON 对象或数组。 如果指定AS JSON该选项，则列的类型必须为 nvarchar（MAX）。

• 如果未为列指定 AS JSON ，该函数将从指定路径上的指定 JSON 属性返回标量值（例如 int、 string、 true、 false）。 如果路径表示对象或数组，并且无法在指定路径中找到该属性，则函数在模式中NULL返回lax或返回模式中的strict错误。 此行为类似于函数的行为 JSON_VALUE 。

• 如果为列指定 AS JSON ，该函数将从指定路径上的指定 JSON 属性返回 JSON 片段。 如果路径表示标量值，并且无法在指定路径中找到该属性，则函数在模式中NULL返回lax或返回模式中的strict错误。 此行为类似于函数的行为 JSON_QUERY 。

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

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  
 )

Results

Return value

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

• 使用默认架构进行调用 OPENJSON 时（即未在 WITH 子句中指定显式架构时）函数将返回具有以下列的表：

  • Key. 一个 nvarchar（4000） 值，该值包含指定数组中元素的指定属性的名称或索引。 该 key 列具有 BIN2 排序规则。

  • Value. 一个包含属性值的 nvarchar（MAX） 值。 该 value 列从 jsonExpression 继承其排序规则。

  • Type. 一个包含值类型的 int 值。 Type仅当与默认架构一起使用OPENJSON时，才会返回该列。 该 type 列具有以下值之一：

    类型列的值	JSON 数据类型
    0	null
    1	字符串
    2	number
    3	true/false
    4	数组
    5	对象

  仅返回第一级属性。 如果 JSON 文本的格式不正确，则语句会失败。

• 在子句中OPENJSON调用WITH并指定显式架构时，该函数将返回一个表，其中包含在子句中WITH定义的架构。

Remarks

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

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

此页上的一些示例显式指定路径模式， lax 或 strict。 路径模式是可选项。 如果未显式指定路径模式， lax 则模式为默认值。 有关路径模式和路径表达式的详细信息，请参阅 JSON 路径表达式。

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

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

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

Examples

示例 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 对象的所有属性的并集。 这两个对象具有重复 的名称 属性。 该示例使用键值从结果中排除重复行。

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 对象包含 质量 与 制造商等属性。 该示例创建一个报表，它将每个销售订单行联接到相关销售原因。 运算符 OPENJSON 扩展销售原因的 JSON 数组，就像原因存储在单独的子表中一样。 然后， CROSS APPLY 运算符将每个销售订单行联接到表值函数返回的 OPENJSON 行。

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

可以通过将 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

Results

示例 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

Related content

• JSON 路径表达式
• 使用 OPENJSON 分析和转换 JSON 数据
• 将 OPENJSON 与默认架构配合使用
• 将 OPENJSON 与显式架构配合使用