如何使用SQL Server 2016导入/导出JSON数据

JSON is an abbreviation for JavaScript Object Notation. JSON is very popular and currently the most commonly used data exchange format. Most modern web and mobile services return information formatted as JSON text, all database web services, web browsers (Firefox, Internet Explorer) return results formatted as JSON text or accept data formatted as JSON. Since external systems format information as JSON text, JSON is also stored in SQL Server 2016 as text. You can use standard NVARCHAR columns to store JSON data in SQL Server 2016.

JSON是JavaScript Object Notation的缩写。 JSON非常流行，并且是当前最常用的数据交换格式。 大多数现代的Web和移动服务都返回格式为JSON文本的信息，所有数据库Web服务，Web浏览器（Firefox，Internet Explorer）都返回格式为JSON文本的结果或接受格式为JSON的数据。 由于外部系统将信息格式设置为JSON文本，因此JSON也作为文本存储在SQL Server 2016中。 您可以使用标准NVARCHAR列在SQL Server 2016中存储JSON数据。

This article will explain how to import JSON data into SQL Server 2016 table and how to export data from SQL Server 2016 table as JSON using SQL Server 2016 built-in functions.

本文将介绍如何使用SQL Server 2016内置函数将JSON数据导入SQL Server 2016表以及如何将数据从SQL Server 2016表导出为JSON。

With SQL Server 2016, built-in functions can parse JSON text to read or modify JSON values, transform JSON array of objects into table format, any Transact -SQL query can be run over the converted JSON objects, results of Transact-SQL queries can be formatted into JSON format.

使用SQL Server 2016，内置函数可以解析JSON文本以读取或修改JSON值，将对象的JSON数组转换为表格式，任何Transact -SQL查询都可以在转换后的JSON对象上运行，Transact-SQL查询的结果可以格式化为JSON格式。

So, let’s start. Below is a simple example of JSON:

所以，让我们开始吧。 以下是JSON的简单示例：

{
  ”BusinessEntityID”:1,
  ”NationalIDNumber”:”295847284″,
  ”JobTitle”:”Chief Executive Officer”,
  ”BirthDate”:”1969-01-29″,
  ”Gender”:”M”
}

{
“ BusinessEntityID”：1，
“ NationalIDNumber”：“ 295847284”，
“ JobTitle”：“首席执行官”，
“出生日期”：“ 1969-01-29”，
“性别”：“ M”
}

More information about structure of the JSON can be found on this link.

可以在此链接上找到有关JSON结构的更多信息。

Let’s declare a SQL Server variable and put JSON code in it.

让我们声明一个SQL Server变量并将JSON代码放入其中。

 
DECLARE @json varchar(max)='
   { 
      "BusinessEntityID":1,
      "NationalIDNumber":"295847284",
      "JobTitle":"Chief Executive Officer",
      "BirthDate":"1969-01-29",
      "Gender":"M"
   }   
';
 

One of the built-in JASON functions that are implemented in SQL Server 2016 is ISJSON.

ISJSON是SQL Server 2016中实现的内置JASON函数之一。

The ISJSON function verifies if it is the code in @json variable formatted as JSON. If the code in the @json variable, formats correctly the output value in the Results grid, 1 will appear:

ISJSON函数验证@JSON变量中的代码是否为JSON格式。 如果@json变量中的代码在“结果”网格中正确格式化了输出值，则将出现1：

Otherwise, the output value in the Results grid will be 0. For example, if the open curly bracket is omitted from the example above, the result will be:

否则，“结果”网格中的输出值将为0。例如，如果在上面的示例中省略了大括号，则结果将是：

To convert the JSON text into a set of rows and columns, the OPENJSON function is used.

要转换的JSON文本转换成一组行和列的OPENJSON使用功能。

Syntax of the OPENJSON function that transforms JSON text to row and columns looks like:

将JSON文本转换为行和列的OPENJSON函数的语法如下：

OPENJSON (<json text>)
WITH (<column/type>)

OPENJSON（<json文本>）
WITH（<列/类型>）

In the WITH clause, the schema of returned rows with name of columns and their types is defined. The OPENJSON function will parse JSON object, match properties in JSON object with column names in the WITH clause and convert their values to specified types.

在WITH子句中，定义了具有列名及其类型的返回行的架构。 OPENJSON函数将解析JSON对象，将JSON对象中的属性与WITH子句中的列名进行匹配，并将其值转换为指定的类型。

In the example below, it is shown how to convert JSON text to set of rows and columns:

在下面的示例中，显示了如何将JSON文本转换为行和列集：

 
DECLARE @json varchar(max)='
    {
      "BusinessEntityID":1,
      "NationalIDNumber":"295847284",
      "JobTitle":"Chief Executive Officer",
      "BirthDate":"1969-01-29",
      "Gender":"M"
   }   
';
 
SELECT * FROM OPENJSON(@json)
WITH (BusinessEntityID int,
	 NationalIDNumber int,
	 JobTitle varchar(100),
	 BirthDate varchar(200),
	 Gender varchar(2)
)
 

The result will look like:

结果将如下所示：

BusinessEntityID	NationalIDNumber	JobTitle	BirthDate	Gender
1	295847284	Chief Executive Officer	1969-01-29	M

BusinessEntityID	身份证号码	职称	生日	性别
1个	295847284	首席执行官	1969-01-29	中号

If the SELECT statement without a WITH clause is executed:

如果执行不带WITH子句的SELECT语句：

 
DECLARE @json varchar(max)='
    {
      "BusinessEntityID":1,
      "NationalIDNumber":"295847284",
      "JobTitle":"Chief Executive Officer",
      "BirthDate":"1969-01-29",
      "Gender