将 JSON 文本插入 SQL Server 表
4.86/5 (28投票s)
如何轻松地将 JSON 对象数组插入 SQL Server 2016 表中
目录
引言
SQL Server 2016 和 Azure SQL Database 有一个新函数 OPENJSON，它使您能够轻松解析 JSON 文本并将其插入到任何表中。有关 JSON 支持的更多详细信息，请参阅 2016 年 7 月 13 日 - JSON 即将登陆 SQL Server。在本文中，我们将了解如何使用此函数将 JSON 对象数组插入到表中。
背景
您是否曾经创建过接受 JSON 的 REST API，并且需要将此 JSON 导入数据库？也许您有一个 REST 服务，该服务接收来自 JQuery、AngularJS 或 ReactJS 应用程序的 JSON？您是否曾经调用过返回 JSON 响应的 REST 服务，或者从文件中加载过 JSON，然后需要将结果存储在 SQL 表中？也许您需要将来自 Twitter 或 MongoDB 的某些 JSON 文档加载到数据库中？
过去，您可能需要使用 JSON.Net 或其他序列化器来解析此 JSON，或者使用框架将 JSON 映射到对象，然后使用 ADO.NET 或 Entity Framework 将它们存储到数据库中。使用 SQL Server 2016，您有另一种选择 - 只需将整个 JSON 文本发送到数据库，并使用新的 OPENJSON 函数进行解析。
在本文中，我们将介绍如何实现这一点。
用例 - 将 JSON 导入数据库
假设您有一个或多个 JSON 对象，如下例所示
[
{ "id" : 2,"firstName": "John", "lastName": "Smith",
"age": 25, "dateOfBirth": "2007-03-25T12:00:00" },
{ "id" : 5,"firstName": "John", "lastName": "Smith",
"age": 35, "dateOfBirth": "2005-11-04T12:00:00" },
{ "id" : 7,"firstName": "John", "lastName": "Smith",
"age": 15, "dateOfBirth": "1983-10-28T12:00:00" },
{ "id" : 8,"firstName": "John", "lastName": "Smith",
"age": 12, "dateOfBirth": "1995-07-05T12:00:00" },
{ "id" : 9,"firstName": "John", "lastName": "Smith",
"age": 37, "dateOfBirth": "2015-03-25T12:00:00" }
]
如果您将此 JSON 作为查询或存储过程的参数发送，或者将其设置为本地变量，您就可以使用 OPENJSON 函数轻松地将此 JSON 对象数组转换为一组行，并查看此 JSON 中的内容
SELECT *
FROM OPENJSON(@json)
WITH (id int, firstName nvarchar(50), lastName nvarchar(50),
age int, dateOfBirth datetime2)
OPENJSON 函数将解析 JSON，并在 WITH 子句中，您可以指定您想要看到的列名。OPENJSON 会将列名与 JSON 数组中的键进行匹配，并返回一组行。此外，它还会自动将字符数据转换为与每个列关联的类型。如果您执行此查询，您将获得类似以下的输出
2 John Smith 25 2007-03-25 12:00:00.0000000
5 John Smith 35 2005-11-04 12:00:00.0000000
7 John Smith 15 1983-10-28 12:00:00.0000000
8 John Smith 12 1995-07-05 12:00:00.0000000
9 John Smith 37 2015-03-25 12:00:00.0000000
现在，您可以轻松地将这些值导入到任何表中
INSERT INTO Person (id, name, surname, age, dateOfBirth)
SELECT id, firstNAme, lastName, age, dateOfBirth
FROM OPENJSON(@json)
WITH (id int,
firstName nvarchar(50), lastName nvarchar(50),
age int, dateOfBirth datetime2)
因此，这是一个直接将您的 JSON 导入表的命令。您可以将其放在存储过程中，然后仅提供 JSON 作为输入参数
DROP PROCEDURE IF EXISTS dbo.PersonInsertJson
GO
CREATE PROCEDURE dbo.PersonInsertJson(@Person NVARCHAR(MAX))
AS BEGIN
INSERT INTO Person (id, name, surname, age, dateOfBirth)
SELECT id, firstNAme, lastName, age, dateOfBirth
FROM OPENJSON(@json)
WITH (id int, firstName nvarchar(50), lastName nvarchar(50), _
age int, dateOfBirth datetime2)
END
您可以使用类似的存储过程来更新表中的现有行。在以下示例中，我将使用 OPENJSON 来解析输入的 JSON 文本，并通过匹配 id 字段来更新 name、surname、age 和 dateOfBirth 字段
UPDATE Person
SET name = json.firstname,
surname = json.lastname,
age = json.age,
dateOfBirth = json.dateOfBirth
FROM OPENJSON(@json)
WITH (id int,
firstName nvarchar(50), lastName nvarchar(50),
age int, dateOfBirth datetime2) AS json
WHERE Person.id = json.id
有关此帖子的详细信息，请参阅 OPENJSON – 将 JSON 文本导入表的最简单方法。
如果您是高级 SQL 用户，可以使用 MERGE 语句，该语句将在行不存在时插入行，在匹配时更新行。
MERGE INTO Person AS P
USING (
SELECT *
FROM OPENJSON(@json)
WITH (id int, firstName nvarchar(50), lastName nvarchar(50),
age int, dateOfBirth datetime2) InputJSON
ON (P.id = InputJSON.id)
WHEN MATCHED THEN
UPDATE SET P.firstName = InputJSON.firstName,
P.lastName = InputJSON.lastName,
P.age = InputJSON.age,
P.dateOfBirth = InputJSON.dateOfBirth
WHEN NOT MATCHED THEN
INSERT (firstName, lastName, age, dateOfBirth)
VALUES (InputJSON.firstName, InputJSON.lastName, InputJSON.age, InputJSON.dateOfBirth);
这是一个组合的 UPdate 或 inSERT 命令，它将根据 id 更新行，或添加新行。您可以在 SQL Server 2016 中的 Upsert JSON 文档 中找到更多详细信息。
处理必填字段
JSON 对象中可能缺少某些字段，这可能没问题。如果输入中没有该属性，OPENJSON 将返回 NULL。但是，如果您想确保输入 JSON 中包含所有必填字段，可以在列中添加 strict 选项
SELECT *
FROM OPENJSON(@json)
WITH (id int 'strict $.id',
firstName nvarchar(50) 'strict $.firstName',
lastName nvarchar(50),
age int,
dateOfBirth datetime2)
如果您在类型后面添加“$.strict keyName”选项，OPENJSON 将知道 keyName 是必填的。如果它在 keyName 中找不到值，它将抛出错误。
未标记为 strict 的字段不是必填的，如果 OPENJSON 找不到它们，它将返回 null。
导入复杂的 JSON 对象
JSON 不必是扁平的。您的 JSON 对象可能包含嵌套值，如下例所示
[
{ "id" : 2,
"info": { "name": "John", "surame": "Smith" },
"age": 25 },
{
"id" : 5,
"info": { "name": "Jane", "surame": "Smith" },
"dateOfBirth": "2005-11-04T12:00:00" }
]
OPENJSON 也可以解析此结构。如果您没有扁平的键值对层次结构，可以在类型后面指定每个属性的“路径”
SELECT *
FROM OPENJSON(@json)
WITH (id int 'strict $.id',
firstName nvarchar(50) 'strict $.info.name',
lastName nvarchar(50) '$.info.surname',
age int,
dateOfBirth datetime2)
如果您没有扁平的键值对层次结构，并且某些字段嵌套在对象中，您可以指定类似 JavaScript 的字段路径。您可以将嵌套路径与 strict 关键字结合使用。
JSON 甚至可以包含嵌套数组，如下例所示
[
{ "id" : 2,
"info": { "name": "John", "surame": "Smith" },
"age": 25,
"skills": ["C#","SQL","JSON","REST"]
},
{
"id" : 5,
"info": { "name": "Jane", "surame": "Smith" },
"dateOfBirth": "2005-11-04T12:00:00",
"skills": ["C#","SQL"] }
]
您也可以使用 OPENJSON 解析此 JSON 文本。由于我们想将此 skills JSON 数组作为“整个 JSON”读取，因此需要添加 AS JSON 选项
SELECT *
FROM OPENJSON(@json)
WITH (id int 'strict $.id',
firstName nvarchar(50) 'strict $.info.name',
lastName nvarchar(50) '$.info.surname',
age int,
dateOfBirth datetime2,
skills NVARCHAR(MAX) AS JSON
)
现在 OPENJSON 将返回完整的 JSON 数组作为 skills 列
id firstName lastName age dateOfBirth skills
2 John Smith 25 NULL ["C#","SQL","JSON","REST"]
5 Jane Smith NULL 2005-11-04 12:00:00.0000000 ["C#","SQL"]
正如您所看到的，通过一些选项，您可以轻松读取任何 JSON 结构并验证必填字段。
生成插入 JSON 的代码
尽管这是一个简单的命令，但如果您的表有 20-30 列，则可能难以编写。此外，如果某些列包含特殊字符，您需要在 SQL 名称中使用 [ ]，在 JSON 路径中使用“ ”。
因此，我创建了一个函数来生成此脚本 - 您可以在此处下载。此 SQL 函数的签名如下所示
CREATE FUNCTION
dbo.GenerateJsonInsertProcedure(@SchemaName sysname, @TableName sysname, _
@JsonColumns nvarchar(max), @IgnoredColumns nvarchar(max))
RETURNS NVARCHAR(MAX)
为了生成 Insert 存储过程，您可以指定表的架构名称和表名。此外，如果您有包含 JSON 文本的表列，并且您的输入中将包含嵌套 JSON，则可以在 @JsonColumns 参数中指定这些列的列表。最后，如果某些列不应通过 JSON 插入（例如，DateModified、ModifiedBy），您可以将它们提供为逗号分隔的列名列表。
现在，让我们看看它是如何工作的。我将为 AdventureWorks Person.Address 表生成 JSON insert 存储过程
declare @SchemaName sysname = 'Person' --> Name of the table where we want to insert JSON
declare @TableName sysname = _
'Address' --> Name of the table schema where we want to insert JSON
declare @IgnoredColumns nvarchar(max) = _
'DateModified' --> List of columns that should be ignored
-- comma separated column names in this list will not be included in WITH schema
declare @JsonColumns nvarchar(max) = '||' --> List of pipe-separated NVARCHAR(MAX)
--column names that contain JSON text, e.g. '|AdditionalInfo|Demographics|'
print (dbo.GenerateJsonInsertProcedure(@SchemaName, @TableName, @JsonColumns, @IgnoredColumns))
在这种情况下，我只会打印函数返回的脚本。输出将是
DROP PROCEDURE IF EXISTS [Person].[AddressInsertJson]
GO
CREATE PROCEDURE [Person].[AddressInsertJson](@Address NVARCHAR(MAX))
AS BEGIN
INSERT INTO Address([AddressLine1],[AddressLine2],[City],[StateProvinceID],_
[PostalCode],[ModifiedDate])
SELECT [AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[ModifiedDate]
FROM OPENJSON(@AddressJson)
WITH (
[AddressLine1] nvarchar(120) N'strict $."AddressLine1"',
[AddressLine2] nvarchar(120) N'$."AddressLine2"',
[City] nvarchar(60) N'strict $."City"',
[StateProvinceID] int N'strict $."StateProvinceID"',
[PostalCode] nvarchar(30) N'strict $."PostalCode"',
[ModifiedDate] datetime N'strict $."ModifiedDate"')
END
该函数将遍历指定表中的所有列，检查其类型，是否为必填列（在这种情况下，它将在路径中生成 $.strict 修饰符）并创建脚本。您可以修改此查询并删除不必要的列。
如果您想尝试一下，可以 下载 GenerateJsonInsertScript.zip，其中包含 SQL 脚本。
脚本内部
如果您想了解此脚本的详细信息，这里有一些更详细的解释。
首先，我们需要一个查询来返回将在 INSERT 列表、SELECT 列表和 WITH 子句中生成的列列表。我使用了这个查询
select
col.name as ColumnName,
column_id ColumnId,
typ.name as ColumnType,
-- create type with size based on type name and size
case typ.name
when 'char' then '(' + cast(col.max_length as varchar(10))+ ')'
when 'nchar' then '(' + cast(col.max_length as varchar(10))+ ')'
when 'nvarchar' then (IIF(col.max_length=-1, '(MAX)', _
'(' + cast(col.max_length as varchar(10))+ ')'))
when 'varbinary' then (IIF(col.max_length=-1, '(MAX)', _
'(' + cast(col.max_length as varchar(10))+ ')'))
when 'varchar' then (IIF(col.max_length=-1, '(MAX)', _
'(' + cast(col.max_length as varchar(10))+ ')'))
else ''
end as StringSize,
-- if column is not nullable, add Strict mode in JSON
case
when col.is_nullable = 1 then '$.' else 'strict $.'
end Mode,
CHARINDEX(col.name, @JsonColumns,0) as IsJson
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
LEFT JOIN dbo.syscomments SM ON col.default_object_id = SM.id
where object_id = object_id(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName))
-- Do not insert identity, computed columns, hidden columns,
-- rowguid columns, generated always columns
-- Skip columns that cannot be parsed by JSON, e.g. text, sql_variant, etc.
and col.is_identity = 0
and col.is_computed = 0
and col.is_hidden = 0
and col.is_rowguidcol = 0
and generated_always_type = 0
and (sm.text IS NULL OR sm.text NOT LIKE '(NEXT VALUE FOR%')
and LOWER(typ.name) _
NOT IN ('text', 'ntext', 'sql_variant', 'image','hierarchyid','geometry','geography')
and col.name NOT IN (SELECT value FROM STRING_SPLIT(@IgnoredColumns, ','))
此查询将通过查看系统表来返回列及其 id、名称和类型列表。
有趣的是 StringSize 列。此列根据 type 和 length 格式化 WITH 子句中的类型，例如 nvarchar(50) 或 varchar(max)。Mode 列如果列是必填的（即非 null），则返回“strict $”而不是“$”。此模式要求键必须存在于 JSON 中。@jsonColumns 变量中的所有列将在 IsJson 列中标记为“1”。
此代码不会返回不应通过显式插入插入到表中的列。在此代码中，我排除了标识列、hidden 列、computed 列、rowguid 列和始终生成的列。此外，我还排除了所有将通过序列填充的列。
此外，如果 OPENJSON 无法返回某些类型（如 CLR、geometry/geography），此查询将忽略这些列。
最后，所有放置在 IgnoredColumns 中的列都将被忽略。
现在我需要生成属于表架构的列列表，该列表将添加到存储过程的 INSERT (<<column list>>) 和 SELECT <<column list>> 部分。我正在使用此脚本
declare @TableSchema nvarchar(max) = '';
select @TableSchema = @TableSchema + QUOTENAME(ColumnName) + ','
from <<col_def>>
order by ColumnId
SET @TableSchema = SUBSTRING(@TableSchema, 0, LEN(@TableSchema)) --> remove last comma
<<col_def>> 是之前的查询（在我的脚本中用作 CTE）。此代码将连接该查询中的所有列名，并将它们作为逗号分隔的字符串返回。
现在我需要生成 OPENJSON 函数的 WITH 子句中的列、类型和 json 路径。这是查询
declare @JsonSchema nvarchar(max) = '';
select @JsonSchema = @JsonSchema + '
' + QUOTENAME(ColumnName) + ' ' + ColumnType + StringSize +
N''' + Mode + '"' + STRING_ESCAPE(ColumnName, 'json') + _
'"''' +IIF(IsJson>0, ' AS JSON', '') + ','
from col_def
order by ColumnId
此查询与上一个查询类似。在这里，我正在生成以下序列
column_name type json_path [AS JSON],
最后，我需要将这两个列列表注入到 INSERT SELECT OPENJSON WITH() 脚本中，并生成将由存储过程返回的脚本
declare @Result nvarchar(max) =
N'DROP PROCEDURE IF EXISTS ' + QUOTENAME( @SchemaName) + '.' + _
QUOTENAME(@TableName + 'InsertJson') + '
GO
CREATE PROCEDURE ' + QUOTENAME( @SchemaName) + '.' + _
QUOTENAME(@TableName + 'InsertJson') + '(@' + @TableName + ' NVARCHAR(MAX))
AS BEGIN
INSERT INTO ' + @TableName + '(' + @TableSchema + ')
SELECT ' + @TableSchema + '
FROM OPENJSON(' + @JsonParam + ')
WITH (' + @JsonSchema + ')
END'
RETURN REPLACE(@Result,',)',')')
最后的 replace 命令用于删除 WITH 子句中的最后一个逗号。
如果您只想调用此存储过程，则不需要这些详细信息；但是，如果您计划修改它，您将需要这些详细信息。
历史
- 2016 年 3 月 25 日 - 初始版本
- 2016 年 3 月 26 日 - 增加了关于生成代码函数实现细节的说明