TSQL을 사용하여 데이터 쿼리 및 JSON 데이터 업데이트

JSON은 데이터 교환에 매우 널리 사용되는 데이터 형식으로 주로 웹 및 모바일 애플리케이션에서 사용됩니다. JSON은 키/값 쌍을 사용하여 데이터를 저장하고 중첩된 키-값 쌍과 배열이라는 두 가지 복잡한 데이터 유형을 나타냅니다. 지정된 속성 또는 멤버는 사용하기 쉽고 강력합니다. JSON 형식은 SQL Server 2016 버전에서 지원되며 유니코드 문자 유형을 사용하여 JSON 데이터를 표현하고 JSON 데이터를 확인, 쿼리 및 수정할 수 있습니다. JSON 확인 및 서식 지정 도구를 권장합니다: json formatter.

1. JSON 데이터 정의 및 검증

nvarchar를 사용하여 JSON 데이터를 표현하고, ISJSON 함수를 통해 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 isjson(@json)

ISJSON 함수의 형식은 다음과 같습니다. ISJSON(표현식)은 문자열이 JSON 데이터임을 나타내는 1을 반환하고, 문자열이 JSON 데이터가 아님을 나타내는 NULL을 반환합니다. 표현식이 NULL입니다.

2. JSON 데이터의 PATH 표현식

경로 표현식은 경로 모드와 경로 두 부분으로 나뉩니다. 경로 모드는 선택 사항이며 lax와 strict의 두 가지 모드가 있습니다.

1, 경로 모드

Path 표현식 시작 부분에 lax 또는 strict 키워드를 통해 경로 모드를 명시적으로 선언할 수 있습니다. 선언하지 않으면 기본 경로 모드는 lax입니다. lax 모드에서는 경로 표현식에 오류가 있는 경우 JSON 함수가 NULL을 반환합니다. 엄격 모드에서는 Path 표현식에 오류가 있으면 JSON 함수에서 오류가 발생합니다.

2, Path 표현식

Path는 JSON 데이터에 액세스하는 방법이며 네 가지가 있습니다. 연산자:

$: 전체 JSON 데이터의 내용을 나타냅니다.

쉼표.: 필드(필드) 또는 키라고도 하는 JSON 개체의 멤버를 나타냅니다. 🎜>대괄호 [] : 배열의 요소를 나타냅니다. 요소의 시작 위치는 0입니다.

키 이름: 키 이름, 키 이름을 통해 해당 값을 참조하세요. 이름에는 공백, $, 쉼표, 대괄호가 포함되어 있으며 큰따옴표를 사용합니다.

예를 들어 Path 표현식을 통해 JSON의 각 속성으로 라우팅될 수 있는 다음 JSON 데이터가 있습니다.

경로 표현식으로 쿼리되는 데이터는 다음과 같습니다.

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

$: 가장 바깥쪽 중괄호 안의 모든 항목인 JSON의 내용을 나타냅니다. 이 예는 사람 배열과 아래 첨자입니다.

$. people[0]: people 배열의 첫 번째 요소를 나타냅니다: { "name": "Jane", "surname": null, "active": true }

$.people[0].name: From people 배열의 첫 번째 요소에서 키가 Name인 항목(이 경우 John)에 해당하는 데이터를 쿼리합니다. people[1].surname: People 배열 중간에 surname 필드가 있는데, 이는 Path 표현식이 Path Mode를 선언하지 않고 기본값이 lax이기 때문입니다. Path 표현식에 오류가 발생하면 NULL이 됩니다.

3, Path

1을 통해 JSON 데이터 쿼리, 스칼라 값(JSON_VALUE) 쿼리

JSON_VALUE(표현식, 경로) 함수를 사용하여 스칼라 값 반환 Path 매개변수에 따른 JSON 데이터에서 반환되는 데이터는 문자 유형입니다.

2. JSON 데이터 반환(JSON_QUERY)

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

JSON_QUERY(표현식[, 경로]) 함수를 사용하여 JSON 데이터를 반환합니다. 경로 매개변수(JSON 조각), 매개변수 경로는 선택사항입니다. 옵션 매개변수를 지정하지 않으면 기본 경로는 $입니다. 즉, 전체 JSON 데이터가 반환됩니다.

4. Path를 통해 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
 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

JSON_MODIFY(expression, path, newValue)를 사용하여 속성 값 수정 ​​JSON 데이터에서 수정된 JSON 데이터를 반환합니다. JSON 데이터를 수정하는 이 함수의 프로세스는 다음과 같습니다.

기존 속성 수정: 매개변수 경로에 따라 JSON 데이터에서 지정된 속성을 찾아 값을 수정합니다. newValue 매개변수에 대한 속성의 반환 값은 수정된 JSON 데이터입니다.

새 키-값 쌍(키: 값 쌍)을 추가합니다. 지정된 속성이 JSON 데이터에 존재하지 않는 경우 매개변수 Path에 따라 지정된 경로에 새 키-값 쌍을 추가합니다.

키-값 쌍 삭제(Key: Value pair): 매개변수 newValue의 값이 NULL이면 삭제한다는 의미입니다. JSON 데이터의 지정된 속성

append 키워드: JSON 배열의 요소를 추가하는 데 사용됩니다.

데이터 요소를 JSON 데이터에 업데이트, 삽입, 삭제 및 추가합니다. 🎜>

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')

5. JSON 데이터를 관계형 테이블로 변환

OPENJSON 함수는 행 집합 함수(RowSet)입니다. JSON 데이터를 관계형 테이블로,

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

경로 매개변수: 테이블 경로라고도 하며 JSON 데이터에서 관계형 테이블의 경로를 지정합니다.

column_path 매개변수: 경로 매개변수를 기반으로 관계형 테이블 JSON의 각 열 경로를 지정합니다.

AS JSON 속성: AS JSON 속성인 경우 이 지정되면 열의 데이터 형식은 열 값이 JSON 데이터임을 나타내는 nvarchar(max )로 정의되어야 합니다. AS JSON 특성이 지정되지 않은 경우 열 값은 스칼라 값입니다. 🎜>

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中文网（www.php.cn）！