This article explores the process of JSON data import in the SQL Server table using T-SQL and SSIS.

本文探讨了使用T-SQL和SSIS在SQL Server表中导入JSON数据的过程。

介绍 (Introduction)

Java Script Object Notation is an accessible data exchange format. Many applications support JSON format data nowadays. We can use JSON data for unstructured data such as log files and NoSQL databases. SQL Server also supports JSON format data import and export for exchanging data with different data sources and applications.

Java脚本对象表示法是一种可访问的数据交换格式。 如今，许多应用程序都支持JSON格式数据。 我们可以将JSON数据用于非结构化数据，例如日志文件和NoSQL数据库。 SQL Server还支持JSON格式的数据导入和导出，以与不同的数据源和应用程序交换数据。

We can use SQL Server FOR XML PATH or FOR XML Auto clause in the SQL Server query, and it retrieves the results into the JSON format. We have explored this in the article

我们可以在SQL Server查询中使用SQL Server的FOR XML PATH或FOR XML Auto子句，它将结果转换为JSON格式。 我们已在文章对此进行了探讨

In the following image, we can see built-in functions for converting text into JSON and vice-versa:

在下图中，我们可以看到用于将文本转换为JSON（反之亦然）的内置函数：

Azure Data Studio JSON格式输出 (Azure Data Studio JSON format output)

We can also save results in JSON format without specifying any parameter in Azure Data Studio. Let’s execute a query in the AdventureWorks sample database in Azure Data Studio:

我们还可以将结果保存为JSON格式，而无需在Azure Data Studio中指定任何参数。 让我们在Azure Data Studio的AdventureWorks示例数据库中执行查询：

SELECT TOP (10) [FirstName],                 [MiddleName],                 [LastName],                 [JobTitle],                 [PhoneNumber],                 [PhoneNumberType],                 [EmailAddress],                 [EmailPromotion]FROM [AdventureWorks].[HumanResources].[vEmployee];

We get the output in the tabular format as shown below:

我们以表格格式获得输出，如下所示：

In the above screenshot, we have following output formats:

在上面的屏幕截图中，我们具有以下输出格式：

• Save as CSV
  另存为CSV
• Save as Excel
  另存为Excel
• Save as JSON
  另存为JSON
• Save as XML
  另存为XML
• Chart
  图表
• Visualizer
  可视化器

Click on Save as JSON, and it asks you for specifying the directory and file name for JSON file:

单击另存为JSON ，它要求您指定JSON文件的目录和文件名：

Click on Save, and it shows the JSON format output:

单击Save ，它显示JSON格式输出：

Let’s focus on a single row output. In the output, we can see key-value pair for each:

让我们专注于单行输出。 在输出中，我们可以看到每个键值对：

Now, we will take the opposite approach.

现在，我们将采取相反的方法。

在SQL Server中导入JSON数据 (JSON data import in SQL Server)

We require JSON data import into the SQL Server table from the .json file created earlier.

我们要求将JSON数据从先前创建的.json文件导入到SQL Server表中。

步骤1：使用OPENROWSET导入文件 (Step 1: Import file using OPENROWSET)

The first step is to load the JSON file content in a table. We can use the table value function OPENROWSET for reading data from a file and return a table in the output. This table contains a single column and loads entire file data into it.

第一步是将JSON文件内容加载到表中。 我们可以使用表值函数OPENROWSET从文件中读取数据并在输出中返回表。 该表包含单个列，并将整个文件数据加载到其中。

Specify the complete file path in the OPENROWSET function:

在OPENROWSET函数中指定完整的文件路径：

SELECT * FROM OPENROWSET (BULK 'C:\sqlshack\Results.JSON', SINGLE_CLOB) as import

It returns the JSON file output and contains the column BulkColumn as shown below:

它返回JSON文件输出，并包含BulkColumn列，如下所示：

Starting from SQL Server 2016, we have a function ISJSON for validating the JSON format. It checks the JSON stored in the @JSON having data type Varchar(max).

从SQL Server 2016开始，我们具有ISJSON函数来验证JSON格式。 它检查存储在@JSON中的JSON，其数据类型为Varchar（max）。

In the following query, the @JSON variable reads the BulkColumn from the imported file, and further, we pass this variable into the function ISJSON() for checking the syntax. It returns value 1 if JSON is a valid structure:

在下面的查询中，@JSON变量从导入的文件中读取BulkColumn，然后，我们将此变量传递给函数ISJSON（）来检查语法。 如果JSON是有效结构，则返回值1：

Declare @JSON varchar(max)SELECT @JSON=BulkColumnFROM OPENROWSET (BULK 'C:\sqlshack\Results.JSON', SINGLE_CLOB) importIf (ISJSON(@JSON)=1)Print 'It is a valid JSON'ELSEPrint 'Error in JSON format'

In the following screenshot, verify that the JSON structure is valid:

在以下屏幕截图中，验证JSON结构是否有效：

步骤2：将JSON输出从变量转换为SQL Server表 (Step 2: Convert JSON output from the variable into SQL Server tables)

We use OPENJSON() function for converting the JSON output from a variable into a tabular format. We get the output in different rows and columns, and later, it can be inserted into SQL Server tables.

我们使用OPENJSON（）函数将JSON输出从变量转换为表格格式。 我们在不同的行和列中获得输出，以后，可以将其插入SQL Server表中。

Let’s run the following command without specifying any additional parameter. By default, it returns all key-value pairs at the first level in the output:

让我们运行以下命令，而不指定任何其他参数。 默认情况下，它在输出的第一级返回所有键值对：

Declare @JSON varchar(max)SELECT @JSON=BulkColumnFROM OPENROWSET (BULK 'C:\sqlshack\Results.JSON', SINGLE_CLOB) importSELECT *FROM OPENJSON (@JSON)

In this output, we can see the following columns:

在此输出中，我们可以看到以下几列：

• Key: We can consider it as a row number in a table. In JSON format, it is a combination of key-value pair enclosed in the curly brackets {} 关键 ：我们可以将其视为表中的行号。 在JSON格式中，它是括在大括号{}中的键/值对的组合
• Value: It is the property value. In this case, we can see it is a combination of the column name and its value 值 ：这是属性值。 在这种情况下，我们可以看到它是列名及其值的组合
• Type: It is the type of object such as String, Boolean, number, array or object 类型 ：它是对象的类型，例如字符串，布尔值，数字，数组或对象

We want the output like a query output in a tabular format. We can use the WITH clause in the above query along with the column definition. It is like defining a table with appropriate column names and their data types. Once we use a WITH clause, OPENJSON reads the JSON object array and converts the individual value as per specified data type and prints in the output.

我们希望输出类似于表格格式的查询输出。 我们可以在上面的查询中将WITH子句与列定义一起使用。 就像使用适当的列名及其数据类型定义表一样。 一旦使用WITH子句，OPENJSON就会读取JSON对象数组并根据指定的数据类型转换单个值，并在输出中进行打印。

In the following query, we specified all output columns with appropriate data types:

在以下查询中，我们为所有输出列指定了适当的数据类型：

Declare @JSON varchar(max)SELECT @JSON=BulkColumnFROM OPENROWSET (BULK 'C:\sqlshack\Results.JSON', SINGLE_CLOB) importSELECT *FROM OPENJSON (@JSON)WITH (    [FirstName] varchar(20),     [MiddleName] varchar(20),     [LastName] varchar(20),     [JobTitle] varchar(20),     [PhoneNumber] nvarchar(20),     [PhoneNumberType] varchar(10),     [EmailAddress] nvarchar(100),     [EmailPromotion] bit )

In this output, we get the results similar to query output we get earlier in this article:

在此输出中，我们得到的结果与在本文前面获得的查询输出类似：

Ideally, we should specify the similar data type that we use in the table else it might cause issues due to data type conversion errors:

理想情况下，我们应该指定表中使用的类似数据类型，否则由于数据类型转换错误而可能导致问题：

We can skip any columns as well in the WITH clause. For example, let’s say we do not want EmailPromotion column in the output; however, it is available in the JSON file. If we do not specify any data type in the WITH clause, SQL Server removes this column from the output:

我们也可以在WITH子句中跳过任何列。 例如，假设我们不希望在输出中使用EmailPromotion列。 但是，它在JSON文件中可用。 如果我们在WITH子句中未指定任何数据类型，则SQL Server将从输出中删除此列：

Declare @JSON varchar(max)SELECT @JSON=BulkColumnFROM OPENROWSET (BULK 'C:\sqlshack\Results.JSON', SINGLE_CLOB) importSELECT *FROM OPENJSON (@JSON)WITH (    [FirstName] varchar(20),     [MiddleName] varchar(20),     [LastName] varchar(20),     [JobTitle] varchar(20),     [PhoneNumber] nvarchar(20),     [PhoneNumberType] varchar(10),     [EmailAddress] nvarchar(100))

In this output, we do not see the EmailPromotion column even if it is available in the JSON:

在此输出中，即使JSON中有可用的列，我们也看不到EmailPromotion列：

查看JSON数据导入的实际执行计划 (View actual execution plan for JSON data import)

In SSMS, click on Actual Execution Plan and execute the previous SQL statement. It gives the following plan:

在SSMS中，单击“ 实际执行计划”并执行上一个SQL语句。 它给出了以下计划：

In this plan, we can note that it uses a table-valued function for OPENJSON. Let’s hover the cursor over it and view the details:

在该计划中，我们可以注意到，它为OPENJSON使用了表值函数。 让我们将光标悬停在它上面并查看详细信息：

In the above execution plan, note the following:

在上述执行计划中，请注意以下几点：

• It uses object [OPENJSON_EXPLICIT] for fetching the columns and their values
  它使用对象[OPENJSON_EXPLICIT]来获取列及其值
• We see a difference between the estimated number of rows (50) and an actual number of rows (10) as query optimizer unable to estimates the correct number of rows for JSON input
  由于查询优化器无法估计JSON输入的正确行数，因此我们看到了估计的行数（50）和实际的行数（10）之间的差异

Let’s import another JSON file having 290 records and view the actual execution plan. In this execution plan also, it estimates the number of rows 50 in comparison with the actual 290 rows. It seems SQL Server uses a fixed estimate of 50 rows for JSON data import:

让我们导入另一个具有290条记录的JSON文件，并查看实际的执行计划。 同样在该执行计划中，与实际的290行相比，它估计了50行的数量。 看来SQL Server对JSON数据导入使用了50行的固定估计：

将JSON文件数据导入到SQL Server表中 (Import JSON file data into the SQL Server table )

We can use the similar t-SQL described earlier for inserting data in an existing table. We can also create a table using the statement similar to a relational DB table. The following query, imports data from results.json, creates a JSON table automatically using SQL SELECT INTO statement and inserts data into it:

我们可以使用前面描述的类似的t-SQL在现有表中插入数据。 我们还可以使用类似于关系数据库表的语句创建表。 以下查询从results.json导入数据，使用SQL SELECT INTO语句自动创建JSON表并将数据插入其中：

Declare @JSON varchar(max)SELECT @JSON=BulkColumnFROM OPENROWSET (BULK 'C:\sqlshack\Results.JSON', SINGLE_CLOB) importSELECT * INTO  JSONTableFROM OPENJSON (@JSON)WITH (    [FirstName] varchar(20),     [MiddleName] varchar(20),     [LastName] varchar(20),     [JobTitle] varchar(20),     [PhoneNumber] nvarchar(20),     [PhoneNumberType] varchar(10),     [EmailAddress] nvarchar(100))

We can verify the records using the following SELECT statement:

我们可以使用以下SELECT语句来验证记录：

Select * from JSONTable

用于将JSON数据导入SQL Server表的SSIS包 (SSIS package for importing JSON data into SQL Server table)

We can use SQL Server integration package for inserting JSON into the SQL Server table as well. You should have basic knowledge of SSIS packages. If you are not much aware of it, I would recommend reading articles.

我们也可以使用SQL Server集成包将JSON插入SQL Server表中。 您应该具有SSIS包的基本知识。 如果您不太了解它，我建议您阅读文章。

I am using Visual Studio 2019 with SSDT package in this article.

我在本文中将Visual Studio 2019与SSDT包一起使用。

Open Visual Studio 2019 and create a new integration service project in a suitable directory. In this package, drag Data Flow Task in the control flow task and rename it to Data import from the JSON file:

打开Visual Studio 2019并在合适的目录中创建一个新的集成服务项目。 在此程序包中，将数据流任务拖到控制流任务中，并将其重命名为从JSON文件导入数据 ：

Double-click on this data import from the JSON file task and it moves the package in the data flow tab. In the data flow tab, drag the OLE DB Source and OLE DB destination as shown below:

双击此从JSON文件任务导入的数据，它将包移动到“数据流”选项卡中。 在数据流选项卡中，如下所示拖动OLE DB源和OLE DB目标：

Rename the source and destination as per below:

按照以下方式重命名源和目标：

• OLE DB Source -> JSON File data
  OLE DB源-> JSON文件数据
• OLE DB Destination -> SQL Server table
  OLE DB目标-> SQL Server表

Double-click on the JSON file data and in the OLE DB source editor, do the following tasks:

双击JSON文件数据，然后在OLE DB源编辑器中，执行以下任务：

1. OLE DB connection Manager: Specify SQL Server instance details in the connection manager OLE DB连接管理器：在连接管理器中指定SQL Server实例详细信息
2. Data access mode: We will use the SQL query specified above section. From the drop-down menu, select the data access mode as数据访问模式：我们将使用上一节中指定SQL查询。 从下拉菜单中，选择数据访问模式作为“ SQL Command SQL命令”

3. SQL Command Text: In this section, paste the query we specified earlier for JSON data import. Let me paste the query again for easiness

   SQL命令文本：在此部分中，粘贴我们之前为JSON数据导入指定的查询。 为了方便起见，我再次粘贴查询

   Declare @JSON varchar(max)SELECT @JSON=BulkColumnFROM OPENROWSET (BULK 'C:\sqlshack\Results.JSON', SINGLE_CLOB) importSELECT * FROM OPENJSON (@JSON)WITH (    [FirstName] varchar(20),     [MiddleName] varchar(20),     [LastName] varchar(20),     [JobTitle] varchar(20),     [PhoneNumber] nvarchar(20),     [PhoneNumberType] varchar(10),     [EmailAddress] nvarchar(100),    [EmailPromotion] bit)

Click on Columns and verify the source columns that we want to populate in the destination table:

单击列，然后验证我们要在目标表中填充的源列：

Click OK, and our configuration for OLE DB source is complete:

单击OK ，我们对OLE DB源的配置已完成：

Now configure SQL Server table for the destination table in the SQL instance:

现在，为SQL实例中的目标表配置SQL Server表：

Click on Mappings and verify the source, destination column mappings:

单击映射，然后验证源列，目标列映射：

Click OK, and our SSIS package configuration is complete. You do not see any error icon on any task:

单击OK ，我们的SSIS包配置已完成。 您在任何任务上都看不到任何错误图标：

Click on Start. It quickly processes the JSON file and inserts data into the SQL table. From the output, we see it transferred 10 rows from the JSON file to SQL table:

点击开始 。 它可以快速处理JSON文件并将数据插入SQL表。 从输出中，我们看到它从JSON文件向SQL表传输了10行：

Now, view the records using a SELECT statement:

现在，使用SELECT语句查看记录：

结论 (Conclusion)

In this article, we explored importing JSON file into SQL Server tables using OPENROWSET and OPENJSON function. We also used an SSIS package for JSON data import. You should practice this as per your data requirements.

在本文中，我们探讨了使用OPENROWSET和OPENJSON函数将JSON文件导入SQL Server表的方法。 我们还将SSIS包用于JSON数据导入。 您应该根据数据要求进行练习。

翻译自: