json 转对象函数_JSON_QUERY（）函数从JSON数据提取对象-白红宇

json 转对象函数_JSON_QUERY（）函数从JSON数据提取对象

阅读量：2527 次

发布时间：2019-05-11

本文共 15792 字，大约阅读时间需要 52 分钟。

json 转对象函数

In this article, we will explore JSON_QUERY() functions in SQL Server to extract JSON objects and array from the JSON Data.

在本文中，我们将探索SQL Server中的JSON_QUERY（）函数，以从JSON数据中提取JSON对象和数组。

JSON概述 (Overview of JSON)

JavaScript Object Notation (JSON) is an accessible format for representing data in a structured way. It consists of lightweight data for data exchange. If you are familiar with Amazon Web Service, DynamoDB, MongoDB, Couchbase databases, you should be familiar with the JSON documents. These NoSQL databases primarily uses JSON structured data. It becomes necessary for SQL database administrators as well to understand JSON and use SQL Server function in conjunction with JSON.

JavaScript Object Notation（JSON）是一种可访问的格式，用于以结构化方式表示数据。它包含用于数据交换的轻量级数据。如果您熟悉Amazon Web Service，DynamoDB，MongoDB，Couchbase数据库，则应该熟悉JSON文档。这些NoSQL数据库主要使用JSON结构化数据。 SQL数据库管理员也必须了解JSON并结合使用SQL Server功能和JSON。

It consists of key-value pairs.
它由键值对组成。

for more details 以获取更多详细信息

Each key should be enclosed in a double-quote
每个密钥都应放在双引号中

We can have a string, object, array, Boolean or number data format in a value
我们可以在值中使用字符串，对象，数组，布尔值或数字数据格式

Each key should use colon (:) to segregate from the values. For example “Name”:”Rajendra”
每个键都应使用冒号（:)与值分开。例如“名称”：“ Rajendra”

Before we go further, I will give a small overview of JSON object and array.

在继续之前，我将简要介绍JSON对象和数组。

JSON Object:

JSON对象：

In JSON, each object is enclosed by curly brackets({}). In the below example, we have a key (Name), and its value is a JSON object (nested JSON)

在JSON中，每个对象都用大括号（{}）括起来。在下面的示例中，我们有一个键（名称），其值是JSON对象（嵌套JSON）
```
"Name" : {        "FirstName" : "Rajendra"    }
```

JSON Array

JSON数组

It is an ordered list of values to store multiple values. We use square brackets to represent an array in the JSON Data. In the following example, we have an employee array that has employees’ records.

它是值的有序列表，用于存储多个值。我们使用方括号表示JSON数据中的数组。在下面的示例中，我们有一个雇员数组，其中包含雇员的记录。
```
{   "employees":[      {            "name":"Raj",            "email":"raj@gmail.com",            "age":32            },         {            "name":"Mohan",            "email":"Mohan@yahoo.com",            "age":21            }         ]       }
```

SQL Server provides the following JSON functions to work with JSON Data:

SQL Server提供以下JSON函数以使用JSON数据：

ISJSON(): we can check valid JSON using this function ISJSON（）：我们可以使用此函数检查有效的JSON

JSON_VALUE(): It extracts a scalar value from the JSON data JSON_VALUE（）：从JSON数据中提取标量值

JSON_MODIFY(): It modifies values in the JSON Data. You should go through JSON_MODIFY（）：修改JSON数据中的值。您应该 for this function

JSON_QUERY: It extracts an array or string from JSON in SQL Server JSON_QUERY：它从SQL Server中的JSON中提取数组或字符串

We can view these functions in the following image of.

我们可以在下面的图像中查看这些功能。

We explored JSON_VALUE() and JSON_MODIFY() functions in my previous articles. You can refer to for these articles. In this article, we are exploring JSON_QUERY() function using various examples.

在之前的文章中，我们探讨了JSON_VALUE（）和JSON_MODIFY（）函数。您可以参考以获得这些文章。在本文中，我们将使用各种示例来探索JSON_QUERY（）函数。

JSON_QUERY（）的语法 (Syntax of JSON_QUERY())

JSON_QUERY (expression ,[Path Mode] JSON_path)

JSON_QUERY（表达式，[路径模式] JSON_path）

Expression: It is a JSON string or a variable holding JSON data 表达式 ：它是一个JSON字符串或一个保存JSON数据的变量

JSON_Path: It is the path of the object or an array from where we want to retrieve values JSON_Path：这是我们要从中检索值的对象或数组的路径

Path mode: It controls the output of a JSON_QUERY() function in case of an invalid JSON string using the LAX and Strict arguments 路径模式：使用LAX和Strict参数在无效的JSON字符串的情况下控制JSON_QUERY（）函数的输出

示例1：从JSON字符串获取JSON对象 (Example 1: Get the JSON object from a JSON string)

In this example, we require to retrieve the first JSON object from the [employees] key.

在此示例中，我们需要从[员工]键中检索第一个JSON对象。

“employees” key “员工”键的数组

We can note the array is enclosed in a square bracket
我们可以注意到数组被括在方括号中

employees[0] argument employee [0]参数

employees[1] argument employee [1]参数访问第二条记录

DECLARE @data NVARCHAR(4000);SET @data = N'{"employees":[      {         "name":"Raj",         "email":"raj@gmail.com",         "age":32          },      {         "name":"Mohan",         "email":"Mohan@yahoo.com",         "age":21                  }   ]}';SELECT JSON_QUERY(@data, '$.employees[0]') AS 'Result';

It retrieves the first JSON object and gives you the following output.

它检索第一个JSON对象，并提供以下输出。

We can retrieve the whole JSON document by removing the second argument. It is an optional argument, so we do not get any error. We can also use the ‘$’ argument to get the whole JSON string.

我们可以通过删除第二个参数来检索整个JSON文档。这是一个可选参数，因此不会出现任何错误。我们还可以使用'$'参数获取整个JSON字符串。

DECLARE @data NVARCHAR(4000);SET @data = N'{"employees":[      {         "name":"Raj",         "email":"raj@gmail.com",         "age":32          },      {         "name":"Mohan",         "email":"Mohan@yahoo.com",         "age":21                  }   ]}';SELECT JSON_QUERY(@data) AS 'Result';

You can see the whole document as part of the JSON_QUERY() function.

您可以将整个文档视为JSON_QUERY（）函数的一部分。

As you know, we cannot use the JSON_VALUE() function to retrieve an object or array. It retrieves a scalar value from a JSON string. You get NULL value in the output if we replace the JSON_MODIFY() function with JSON_VALUE().

如您所知，我们无法使用JSON_VALUE（）函数来检索对象或数组。它从JSON字符串中检索标量值。如果我们用JSON_VALUE（）替换JSON_MODIFY（）函数，则输出中将得到NULL值。

DECLARE @data NVARCHAR(4000);SET @data = N'{"employees":[      {         "name":"Raj",         "email":"raj@gmail.com",         "age":32          },      {         "name":"Mohan",         "email":"Mohan@yahoo.com",         "age":21                  }   ]}';SELECT JSON_VALUE(@data,'$.employees') AS 'Result';

It returns a NULL value in the output, as shown below.

它将在输出中返回NULL值，如下所示。

示例2：使用JSON_QUERY（）函数检索标量值 (Example 2: Retrieve a scalar value using the JSON_QUERY() function )

As you know, we use JSON_VALUE() function to retrieve a scalar value. If we try to retrieve the scalar value using JSON_QUERY() function, let’s see the output.

如您所知，我们使用JSON_VALUE（）函数检索标量值。如果我们尝试使用JSON_QUERY（）函数检索标量值，请看输出。

DECLARE @json_data NVARCHAR(4000) = '{"Id":1,"Brand":"HP",       "City":["Laptop","Mobile"]}'SELECT JSON_QUERY(@json_data,'$.Name') as Name

It also gives you NULL value in the output If we try to get a scalar value from the JSON_QUERY() function.

如果我们尝试从JSON_QUERY（）函数获取标量值，它还会在输出中为您提供NULL值。

By default, JSON_QUERY() function uses a default path mode lax. In this mode, SQL Server does not raise an error in case of any invalid key or value. We might want to raise an error message instead of getting a NULL value in the output. We can use strict mode for getting the error message.

默认情况下，JSON_QUERY（）函数使用默认路径模式lax 。在这种模式下，SQL Server不会在任何无效的键或值的情况下引发错误。我们可能想引发一条错误消息，而不是在输出中获取NULL值。我们可以使用严格模式来获取错误消息。

示例3：使用JSON_QUERY（）函数通过$符号检索JSON (Example 3: Retrieve JSON by the $ symbol using the JSON_QUERY() function )

In the following example, we have a JSON string that contains key-value pairs, a JSON array and JSON object.

在以下示例中，我们有一个包含键值对的JSON字符串，一个JSON数组和JSON对象。

DECLARE @data NVARCHAR(4000) = '{"Employees":   [{"EmpId":1,"Name":"Raj",           "Address":{"City":"Gurgaon","Country":"India"}},    {"EmpId":2,"Name":"Sohan",           "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}}   ]}'SELECT JSON_QUERY(@data,'$') Employees_String

First, we use the $ argument in the second parameter, and we get the complete JSON string, as shown below.

首先，我们在第二个参数中使用$参数，然后获得完整的JSON字符串，如下所示。

In the output, we can also notice the message – 1 row affected. It treats the entire JSON string as a single row in SQL Server.

在输出中，我们还可以注意到消息-1行受到影响。它将整个JSON字符串视为SQL Server中的一行。

Now, we want to retrieve the Employees array. In this case, we can specify the array that we want to retrieve with the $ symbol. In the below, we specify it as $.Employees.

现在，我们要检索Employees数组。在这种情况下，我们可以使用$符号指定要检索的数组。在下面，我们将其指定为$ .Employees。

DECLARE @data NVARCHAR(4000) = '{"Employees":   [{"EmpId":1,"Name":"Raj",           "Address":{"City":"Gurgaon","Country":"India"}},    {"EmpId":2,"Name":"Sohan",           "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}}   ]}'SELECT JSON_QUERY(@data,'$.Employees') Employees_Array

In the output, we get the employees array without the key. We can note that the array starts and end with a square bracket.

在输出中，我们获得没有键的employee数组。我们可以注意到数组以方括号开头和结尾。

Further to this example, we need to retrieve the second row (JSON object]. As we already know, JSON uses a zero-based indexing process, and we can specify the second argument $.Employees[1].

在此示例之后，我们需要检索第二行（JSON对象），众所周知，JSON使用基于零的索引过程，并且可以指定第二个参数$ .Employees [1]。

DECLARE @data NVARCHAR(4000) = '{"Employees":   [{"EmpId":1,"Name":"Raj",           "Address":{"City":"Gurgaon","Country":"India"}},    {"EmpId":2,"Name":"Sohan",           "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}}   ]}'SELECT JSON_QUERY(@data,'$.Employees[1]') Employees_Object

We can further filter the JSON and get the customer’s address JSON object. Here, we can specify further argument as $.Employees[1].Address.

我们可以进一步过滤JSON并获取客户的地址JSON对象。在这里，我们可以将其他参数指定为$ .Employees [1] .Address。

DECLARE @data NVARCHAR(4000) =     '{"Employees":       [{"EmpId":1,"Name":"Raj",               "Address":{"City":"Gurgaon","Country":"India"}},        {"EmpId":2,"Name":"Sohan",               "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}}       ]    }'   SELECT JSON_QUERY(@data,'$.Employees[1].Address') Employees_Object

AdventureWorks示例数据库中的JSON查询 (JSON query in AdventureWorks sample database)

We can use the JSON_Query() function in the AdventureWorks sample database. For this demo, you can use the below steps to prepare the same database.

我们可以在AdventureWorks示例数据库中使用JSON_Query（）函数。对于此演示，您可以使用以下步骤准备相同的数据库。

Download the backup file of AdventureWorks2016_EXT from the

从下载AdventureWorks2016_EXT的备份文件

Restore this database in your SQL instance in RECOVERY mode

在RECOVERY模式下在您SQL实例中还原此数据库

Download [sql-server-2016-samples.zip] from

从下载[sql-server-2016-samples.zip]

Extract the folder and execute all scripts(except cleanup.sql) from the JSON folder in the AdventureWorks2016_EXT. You also need a full-text search service for json.indexes.sql, but it is ok for you to ignore the errors related to full text for this article

从AdventureWorks2016_EXT中的JSON文件夹中提取文件夹并执行所有脚本（cleanup.sql除外）。您还需要json.indexes.sql的全文本搜索服务，但是可以忽略与本文有关的全文本错误。

At this step, we can use the JSON function to query data in the AdventureWorks2016_EXT sample database.

在这一步，我们可以使用JSON函数来查询AdventureWorks2016_EXT示例数据库中的数据。

SELECT TOP 10 JSON_QUERY(OrderItems, '$') OrderItems, JSON_QUERY(info, '$') InfoFROM Sales.SalesOrder_json;

SQL函数与JSON_QUERY（）函数结合使用 (SQL Functions in combination with JSON_QUERY() function)

We can use SQL functions such as SQL CONCAT in combination with JSON_QUERY functions. You can download WideWorldImporters database.

我们可以将SQL CONCAT等SQL函数与JSON_QUERY函数结合使用。您可以下载WideWorldImporters数据库。

SELECT TOP 1 JSON_QUERY(Tags) AS Tags, JSON_QUERY(CONCAT('["ValidFrom","', ValidFrom, '","', "ValidTo", ValidTo, '"]')) ValidityPeriodFROM Warehouse.StockItems;

We can use FOR JSON PATH argument to format the output in the JSON format. It also allows you to provide a root element in the JSON string. In the below query, we use the root as Items.

我们可以使用FOR JSON PATH参数将输出格式化为JSON格式。它还允许您在JSON字符串中提供根元素。在下面的查询中，我们将根用作Items。

SELECT TOP 1 JSON_QUERY(Tags) AS Tags, JSON_QUERY(CONCAT('["ValidFrom","', ValidFrom, '","', "ValidTo", ValidTo, '"]')) ValidityPeriodFROM Warehouse.StockItemsFOR json PATH,Root('Items')

You can click on the JSON hyperlink and it gives you JSON as shown below.

您可以单击JSON超链接，它会为您提供JSON，如下所示。

You can copy the JSON and paste it in the It gives you the following formatted JSON.

您可以复制JSON并将其粘贴到它为您提供以下格式的JSON。

JSON_VALUE和JSON_Modify函数之间的区别 (Difference between JSON_VALUE and JSON_Modify functions)

JSON_VALUE function	JSON_MODIFY function
It returns a scalar value from JSON.	We get an object or an array from the JSON.
Output data type – Nvarhcar(4000)	Output data type – Nvarchar(max)
It returns a NULL Value if we try to return an array or object. DECLARE @data NVARCHAR(4000) = '{"Employees": [{"EmpId":1,"Name":"Raj", "Address":{"City":"Gurgaon","Country":"India"}}, {"EmpId":2,"Name":"Sohan", "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}} ] }' SELECT JSON_VALUE(@data,'$.Employees[1].Address') JSON_VALUE SELECT JSON_QUERY(@data,'$.Employees[1]') JSON_QUERY	It returns a NULL value in the output if we try to retrieve a scalar value. SELECT JSON_VALUE('{"Name": "Rajendra"}', '$.Name') AS 'JSON_VALUE', JSON_QUERY('{"Name": "Rajendra"}', '$.Name') AS 'JSON_QUERY';
We cannot retrieve a JSON object using the JSON_VALUE() function. It returns a NULL value in this case. DECLARE @data NVARCHAR(4000) SET @data=N'{ "Company": { "OrgID": "1", "Employees":["Raj","Mohan","Shyam"] } }' SELECT JSON_VALUE(@data,'$.Company') JSON_VALUE ,JSON_QUERY(@data,'$.Company') JSON_QUERY	We cannot retrieve an array element in using this function. We get the NULL value for the array element. DECLARE @data NVARCHAR(4000) SET @data=N'{ "Company": { "OrgID": "1", "Employees":["Raj","Mohan","Shyam"] } }' SELECT JSON_VALUE(@data,'$.Company.Employees[1]') JSON_VALUE ,JSON_QUERY(@data,'$.Company.Employees[1]') JSON_QUERY

JSON_VALUE函数	JSON_MODIFY函数
它从JSON返回标量值。	我们从JSON获取对象或数组。
输出数据类型– Nvarhcar（4000）	输出数据类型– Nvarchar（max）
如果我们尝试返回数组或对象，它将返回NULL值。 DECLARE @data NVARCHAR(4000) = '{"Employees": [{"EmpId":1,"Name":"Raj", "Address":{"City":"Gurgaon","Country":"India"}}, {"EmpId":2,"Name":"Sohan", "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}} ] }' SELECT JSON_VALUE(@data,'$.Employees[1].Address') JSON_VALUE SELECT JSON_QUERY(@data,'$.Employees[1]') JSON_QUERY	如果我们尝试检索标量值，它将在输出中返回NULL值。 SELECT JSON_VALUE('{"Name": "Rajendra"}', '$.Name') AS 'JSON_VALUE', JSON_QUERY('{"Name": "Rajendra"}', '$.Name') AS 'JSON_QUERY';
我们无法使用JSON_VALUE（）函数检索JSON对象。在这种情况下，它将返回NULL值。 DECLARE @data NVARCHAR(4000) SET @data=N'{ "Company": { "OrgID": "1", "Employees":["Raj","Mohan","Shyam"] } }' SELECT JSON_VALUE(@data,'$.Company') JSON_VALUE ,JSON_QUERY(@data,'$.Company') JSON_QUERY	使用此函数无法检索数组元素。我们获得数组元素的NULL值。 DECLARE @data NVARCHAR(4000) SET @data=N'{ "Company": { "OrgID": "1", "Employees":["Raj","Mohan","Shyam"] } }' SELECT JSON_VALUE(@data,'$.Company.Employees[1]') JSON_VALUE ,JSON_QUERY(@data,'$.Company.Employees[1]') JSON_QUERY

结论 (Conclusion)

In this article, we explored JSON_QUERY() function to retrieve the JSON object and array from JSON data. It is a useful function to work with SQL Server JSON data. You should explore these functions and be familiar with the Javascript object Notation.

在本文中，我们探索了JSON_QUERY（）函数以从JSON数据检索JSON对象和数组。使用SQL Server JSON数据是一项有用的功能。您应该探索这些功能，并熟悉Javascript对象Notation。