Query data and update JSON data using TSQL

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中文网（m.sbmmt.com）！