Microsoft SQL Server
JSON 데이터로 쿼리하기
수색…
쿼리에서 JSON의 값 사용하기
JSON_VALUE 함수를 사용하면 두 번째 인수로 지정된 경로에서 JSON 텍스트의 데이터를 가져 와서 select 쿼리의 모든 부분에서이 값을 사용할 수 있습니다.
select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
보고서에서 JSON 값 사용하기
JSON 텍스트에서 JSON 값을 추출한 후에는 쿼리의 모든 부분에서 JSON 값을 사용할 수 있습니다. 그룹화 집계 등을 사용하여 JSON 데이터에 대한 일종의 보고서를 만들 수 있습니다.
select JSON_VALUE(Data, '$.Type') as type,
AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost
from Product
group by JSON_VALUE(Data, '$.Type')
having JSON_VALUE(Data, '$.Type') is not null
쿼리 결과에서 잘못된 JSON 텍스트 필터링
일부 JSON 텍스트의 형식이 올바르지 않은 경우 ISJSON 함수를 사용하여 쿼리에서 해당 항목을 제거 할 수 있습니다.
select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
and ISJSON(Data) > 0
JSON 열의 값 업데이트
JSON_MODIFY 함수를 사용하여 일부 경로의 값을 업데이트 할 수 있습니다. 이 함수를 사용하여 UPDATE 문에서 JSON 셀의 원래 값을 수정할 수 있습니다.
update Product
set Data = JSON_MODIFY(Data, '$.Price', 24.99)
where ProductID = 17;
JSON_MODIFY 함수는 Price 키를 업데이트하거나 생성합니다 (존재하지 않는 경우). 새 값이 NULL이면 키가 제거됩니다. JSON_MODIFY 함수는 새로운 값을 문자열로 처리합니다 (특수 문자를 이스케이프 처리하고 큰 따옴표로 묶어 올바른 JSON 문자열을 만듭니다). 새 값이 JSON 조각 인 경우 JSON_QUERY 함수로 래핑해야합니다.
update Product
set Data = JSON_MODIFY(Data, '$.tags', JSON_QUERY('["promo","new"]'))
where ProductID = 17;
두 번째 매개 변수가없는 JSON_QUERY 함수는 "JSON으로 변환"과 같이 동작합니다. JSON_QUERY의 결과가 유효한 JSON 프래그먼트 (객체 또는 배열)이므로 JSON_MODIFY는 입력 JSON을 수정할 때이 값을 이스케이프 처리하지 않습니다.
JSON 배열에 새로운 가치 추가
JSON_MODIFY 함수를 사용하여 JSON 내의 일부 배열에 새 값을 추가 할 수 있습니다.
update Product
set Data = JSON_MODIFY(Data, 'append $.tags', "sales")
where ProductID = 17;
배열의 끝에 새 값이 추가되거나 [ "sales"] 값을 가진 새 배열이 만들어집니다. JSON_MODIFY 함수는 새로운 값을 문자열로 처리합니다 (특수 문자를 이스케이프 처리하고 큰 따옴표로 묶어 올바른 JSON 문자열을 만듭니다). 새 값이 JSON 조각 인 경우 JSON_QUERY 함수로 래핑해야합니다.
update Product
set Data = JSON_MODIFY(Data, 'append $.tags', JSON_QUERY('{"type":"new"}'))
where ProductID = 17;
두 번째 매개 변수가없는 JSON_QUERY 함수는 "JSON으로 변환"과 같이 동작합니다. JSON_QUERY의 결과가 유효한 JSON 프래그먼트 (객체 또는 배열)이므로 JSON_MODIFY는 입력 JSON을 수정할 때이 값을 이스케이프 처리하지 않습니다.
내부 JSON 컬렉션이있는 JOIN 테이블
JSON 컬렉션으로 포맷 된 "하위 테이블"이 있고 JSON 열로 행에 저장되어있는 경우이 컬렉션의 압축을 풀고 테이블로 변환 한 다음 상위 행과 결합 할 수 있습니다. 표준 JOIN 연산자 대신 CROSS APPLY를 사용해야합니다. 이 예제에서 제품 부분은 JSON 객체의 컬렉션으로 형식화되고 데이터 열에 저장됩니다.
select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))
쿼리의 결과는 Product 테이블과 Part 테이블 간의 조인과 동일합니다.
JSON 배열에서 값을 포함하는 행 찾기
이 예제에서 Tags 배열에는 [ "promo", "sales"]와 같은 다양한 키워드가 포함될 수 있으므로이 배열과 필터 값을 열 수 있습니다.
select ProductID, Name, Color, Size, Price, Quantity
from Product
CROSS APPLY OPENJSON(Data, '$.Tags')
where value = 'sales'
OPENJSON은 내부 태그 컬렉션을 열어 테이블로 반환합니다. 그런 다음 표의 일부 값으로 결과를 필터링 할 수 있습니다.