sqlserver通过OPENJSON转换 json数据

OPENJSON 行集函数可将 JSON 文本转换为一组行和列。 使用 OPENJSON 将 JSON 集合转换为行集后，可以在返回的数据上运行任意 SQL 查询或将其插入到 SQL Server 表中。

OPENJSON 函数采用单个 JSON 对象或 JSON 对象的集合，并将其转换为一行或多行。 OPENJSON 函数默认返回以下数据：

• 从 JSON 对象中，该函数返回在第一个级别找到的所有“键/值”对。
• 从 JSON 数组中，该函数返回数组的所有元素及其索引。

可以添加可选的 WITH 子句来提供显式定义输出结构的架构。

选项 1 - 具有默认输出的 OPENJSON

在不提供结果的显式架构的情况下使用 OPENJSON 函数时（即，在 OPENJSON 之后不使用 WITH 子句），该函数将返回包含以下三列的表：

1. 输入对象中属性的名称（或输入数组中元素的索引）。
2. 属性或数组元素的值。
3. 类型（例如，字符串、数字、布尔值、数组或对象）。

OPENJSON 以单独的行返回 JSON 对象的每个属性或数组的每个元素。

下面是使用具有默认架构（即不包含可选的 WITH 子句）的 OPENJSON 的快捷示例，该示例为 JSON 对象的每个属性返回一行。

示例

SQL复制

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
技能	["SQL","C#","MVC"]	4

有关具有默认架构的 OPENJSON 的详细信息

有关详细信息和示例，请参阅将 OPENJSON 与默认架构 (SQL Server) 配合使用。

有关语法和用法，请参阅 OPENJSON (Transact-SQL) 。

选项 2 - 具有显式结构的 OPENJSON 输出

如果使用 OPENJSON 函数的 WITH 子句指定结果的架构，该函数返回的表只包含 WITH 子句中定义的列。 在可选的 WITH 子句中，指定一组输出列、列类型和每个输出值的 JSON 源属性的路径。 OPENJSON 循环访问 JSON 对象的数组，读取每一列的指定路径上的值，并将值转换为指定类型。

下面是使用具有 WITH 子句中显式指定的输出架构的 OPENJSON 快捷示例。

示例

SQL复制

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

结果

Number	Date	客户	数量
SO43659	2011-05-31T00:00:00	AW29825	1
SO43661	2011-06-01T00:00:00	AW73565	3

此函数返回 JSON 数组的元素并将其格式化。

• 对于 JSON 数组中的每个元素， OPENJSON 会在输出表中生成新的一行。 JSON 数组中的两个元素将在返回的表中转换为两行。

• 对于通过使用 colName type json_path 语法指定的每一列，OPENJSON 将指定路径上的每个数组元素中找到的值转换为指定类型。 在此示例中，Date 列的值获取自路径 $.Order.Date 上的每个元素，并被转换为日期时间值。

有关具有显式架构的 OPENJSON 的详细信息

有关详细信息和示例，请参阅将 OPENJSON 与显式架构配合使用 (SQL Server) 。

有关语法和用法，请参阅 OPENJSON (Transact-SQL) 。

OPENJSON 要求兼容性级别 130

查看兼容性sql

--查看兼容性
--database为数据库名称

USE database;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'database';  
GO

--示例

USE model;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'model';  
GO

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

可以在 sys.databases 视图或数据库属性中查看兼容级别。

可以使用以下命令更改数据库的兼容级别：
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

将 SQL Server 数据转换为 JSON 或导出 JSON

通过将 FOR JSON 子句添加到 SELECT 语句中，可将 SQL Server 数据或 SQL 查询结果的格式设置为 JSON。 使用 FOR JSON 委托从客户端应用程序到 SQL Server 的 JSON 输出格式。 有关详细信息，请参阅 借助 FOR JSON 将查询结果的格式设置为 JSON (SQL Server)。

以下示例使用 PATH 模式和 FOR JSON 子句。

select keys,firstName ,nameTW,nameEn from ceshi for json path

select keys,firstName as "info.name" ,nameTW,nameEn from ceshi for json path
--使用 ROOT 选项指定一个已命名根元素。
select keys,firstName as "info.name" ,nameTW,nameEn  from ceshi for json path, ROOT('data')

控制其他 JSON 输出选项

使用以下附加选项控制 FOR JSON 子句的输出。

• ROOT。 若要将单个顶层元素添加到 JSON 输出中，请指定 ROOT 选项。 如果没有指定此选项，JSON 输出不会包括根元素。 有关详细信息，请参阅使用 ROOT 选项 (SQL Server) 将根节点添加到 JSON 输出。

• INCLUDE_NULL_VALUES。 若要在 JSON 输出中包含 null 值，请指定 INCLUDE_NULL_VALUES 选项。 如果没有指定此选项，输出不会在查询结果中包括 NULL 值的 JSON 属性。 有关详细信息，请参阅使用 INCLUDE_NULL_VALUES 选项 (SQL Server) 在 JSON 输出中包含 Null 值。

• WITHOUT_ARRAY_WRAPPER。 若要删除默认括住 FOR JSON 子句的 JSON 输出的方括号，请指定 WITHOUT_ARRAY_WRAPPER 选项。 使用此选项可以生成单个 JSON 对象作为单行结果中的输出。 如果不指定此选项，JSON 输出将格式化为数组 - 即括在方括号内。 有关详细信息，请参阅使用 WITHOUT_ARRAY_WRAPPER 选项从 JSON 输出中删除方括号 (SQL Server)。

如有侵权联系作者删除