JSON is a very popular data format for data exchange, mainly used in web and mobile applications. JSON uses key/value pairs to store data, and represents two complex data types: nested key-value pairs and arrays. It can be routed to The specified attributes or members are simple to use and powerful. The SQL Server 2016 version supports JSON format, uses Unicode character types to represent JSON data, and can verify, query and modify JSON data. Recommend a tool for JSON verification and formatting: json formatter.

1. Define and verify JSON data

Use nvarchar to represent JSON data, and verify whether the JSON data is valid through the function ISJSON function.

declare @json nvarchar(max)
set @json = 
N'{
 "info":{ 
  "type":1,
  "address":{ 
  "town":"bristol",
  "county":"avon",
  "country":"england"
  },
  "tags":["sport", "water polo"]
 },
 "type":"basic"
}'
 
select isjson(@json)

The format of the ISJSON function is: ISJSON (expression), returns 1, indicating that the string is JSON data; returns 0, indicating that the string is not JSON data; returns NULL, indicating that expression is NULL;

Second, PATH of JSON data Expression

Path The expression is divided into two parts: Path Mode and Path. Path Mode is optional and has two modes: lax and strict.

1, Path Mode

At the beginning of the Path expression, you can explicitly declare the Path Mode through the lax or strict keywords. If not declared, the default Path Mode is lax. In lax mode, if there is an error in the path expression, the JSON function returns NULL. In strict mode, if there is an error in the Path expression, the JSON function throws an error;

2, Path expression

Path is the way to access JSON data. There are four operators:

$: represents the entire JSON data Content;

Comma.: Represents members of the JSON object, also called Field, or Key;

Brackets []: Represents elements in the array, the starting position of the element is 0;

Key Name: The name of the key, refer to the corresponding Value through Key Name; if the Key Name contains spaces, $, commas, brackets, use double quotes;

For example, the following JSON data can be routed to JSON through Path expression Each attribute of:

{ "people": 
 [ 
 { "name": "John", "surname": "Doe" }, 
 { "name": "Jane", "surname": null, "active": true } 
 ] 
}

The data queried by the Path expression is:

$: represents the content of JSON, which is all the Items in the outermost curly brackets. This example is a people array, and the subscript of the array starts from 0. ;

$.people[0]: represents the first element of the people array: { "name": "Jane", "surname": null, "active": true }

$.people[0].name : From the first element of the people array, query the data corresponding to the Item whose Key is Name, in this case John;

$.people[1].surname: There is a surname field in the middle of the people array, because the Path expression does not Declare Path Mode, the default value is lax, when an error occurs in the Path expression, NULL is returned;

Three, query JSON data through Path

1, query the scalar value (JSON_VALUE)

Use the JSON_VALUE(expression, path) function , from JSON data, return a scalar value according to the Path parameter, and the returned data is a character type.

declare @json nvarchar(max)
set @json = 
N'{
 "info":{ 
  "type":1,
  "address":{ 
  "town":"bristol",
  "county":"avon",
  "country":"england"
  },
  "tags":["sport", "water polo"]
 },
 "type":"basic"
}'
 
select
 json_value(@json, '$.type') as type,
 json_value(@json, '$.info.type') as info_type,
 json_value(@json, '$.info.address.town') as town,
 json_value(@json, '$.info.tags[0]') as tag

2. Return JSON data (JSON_QUERY)

Use the JSON_QUERY ( expression [ , path ] ) function to return JSON data (JSON fragment) according to the Path parameter; the parameter path is optional (optional), if If the option parameter is not specified, the default path is $, that is, the entire JSON data is returned.

declare @json nvarchar(max)
set @json = 
N'{
 "info":{ 
  "type":1,
  "address":{ 
  "town":"bristol",
  "county":"avon",
  "country":"england"
  },
  "tags":["sport", "water polo"]
 },
 "type":"basic"
}'
 
select
 json_query(@json, '$') as json_context,
 json_query(@json, '$.info') as info,
 json_query(@json, '$.info.address') as info_address,
 json_query(@json, '$.info.tags') as info_tags

Four. Modify JSON data through Path

Use JSON_MODIFY ( expression , path , newValue ) to modify the attribute values ??in the JSON data and return the modified JSON data. The process of modifying the JSON data by this function is:

Modify existing attributes: Find the specified attribute from the JSON data according to the parameter path, modify the Value of the attribute to the parameter newValue, and the return value is the modified JSON data;

Add a new key-value pair (Key : Value pair): If the specified attribute does not exist in the JSON data, then according to the parameter Path, add a new key-value pair on the specified path;

Delete key-value pair (Key: Value pair): If the value of the parameter newValue is NULL, then it means to delete the specified attribute from the JSON data;

append keyword: used to append an element from the JSON array;

Example, update, insert, delete and append data elements to the JSON data

declare @info nvarchar(100) = '{"name":"john","skills":["c#","sql"]}'
-- update name
set @info = json_modify(@info, '$.name', 'mike')
-- insert surname
set @info = json_modify(@info, '$.surname', 'smith')
-- delete name
set @info = json_modify(@info, '$.name', null)
-- add skill
set @info = json_modify(@info, 'append $.skills', 'azure')

Fifth, convert JSON data into a relational table

OPENJSON function is a row set function (RowSet), which can convert JSON data into a relational table,

OPENJSON( jsonExpression [ , path ] )
[
 WITH (
  colName type [ column_path ] [ AS JSON ]
 [ , colName type [ column_path ] [ AS JSON ] ]
 [ , . . . n ]
  )
]

path parameter: also called table path, specify the path of the relational table in the JSON data;

column_path parameter: Based on the path parameter, specify the path of each column in the relational table JSON, column path should always be specified explicitly;

AS JSON attribute: if If the AS JSON attribute is specified, the data type of the column must be defined as nvarchar(max), which means that the value of the column is JSON data; if the AS JSON attribute is not specified, the value of the column is a scalar value;

with 選項：指定關系表的Schema，應總是指定with選項；如果不指定with 選項，那么函數返回key，value和type三列；

示例，從JSON數據中，以關系表方式呈現數據

declare @json nvarchar(max)
set @json =
N'{
 "info":{
  "type":1,
  "address":{
  "town":"bristol",
  "county":"avon",
  "country":"england"
  },
  "tags":["sport", "water polo"]
 },
 "type":"basic"
}'
 
SELECT info_type,info_address,tags
FROM OPENJSON(@json, '$.info')
with
(
info_type tinyint 'lax $.type',
info_address nvarchar(max) 'lax $.address' as json,
tags nvarchar(max) 'lax $.tags' as json
)

六，將關系表數據以JSON格式存儲

通過For JSON Auto/Path，將關系表數據存儲為JSON格式，

Auto 模式：根據select語句中column的順序，自動生成JSON數據的格式；

Path 模式：使用column name的格式來生成JSON數據的格式，column name使用逗號分隔（dot-separated）表示組-成員關系；

1，以Auto 模式生成JSON格式

select id,
 name,
 category
from dbo.dt_json
for json auto,root('json')

返回的數據格式是

{
 "json":[
  {
   "id":1,
   "name":"C#",
   "category":"Computer"
  },
  {
   "id":2,
   "name":"English",
   "category":"Language"
  },
  {
   "id":3,
   "name":"MSDN",
   "category":"Web"
  },
  {
   "id":4,
   "name":"Blog",
   "category":"Forum"
  }
 ]
}

2，以Path模式生成JSON格式

select id as 'book.id',
 name as 'book.name',
 category as 'product.category'
from dbo.dt_json
for json path,root('json')

返回的數據格式是：

{
"json":[
{
"book":{
"id":1,
"name":"C#"
},
"product":{
"category":"Computer"
}
},
{
"book":{
"id":2,
"name":"English"
},
"product":{
"category":"Language"
}
},
{
"book":{
"id":3,
"name":"MSDN"
},
"product":{
"category":"Web"
}
},
{
"book":{
"id":4,
"name":"Blog"
},
"product":{
"category":"Forum"
}
}
]
}

? ?

以上就是使用TSQL查詢數據和更新JSON數據的內容，更多相關內容請關注PHP中文網（ipnx.cn）！