기본 제공 함수를 사용하여 JSON 데이터 유효성 검사, 쿼리 및 변경(SQL Server)

아티클
08/23/2023

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL Database Azure SQL Managed Instance

JSON에 대한 기본 제공 지원에는 이 항목에 간략하게 설명된 다음과 같은 기본 제공 함수가 포함되어 있습니다.

ISJSON 은 문자열에 유효한 JSON이 포함되어 있는지 테스트합니다.
JSON_VALUE JSON 문자열에서 스칼라 값을 추출합니다.
JSON_QUERY JSON 문자열에서 개체 또는 배열을 추출합니다.
JSON_MODIFY JSON 문자열의 속성 값을 업데이트하고 업데이트된 JSON 문자열을 반환합니다.

이 페이지의 예제에 대한 JSON 텍스트

이 페이지의 예제는 다음 예제에 표시된 콘텐츠와 비슷한 JSON 텍스트를 사용합니다.

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

중첩된 복합 요소를 포함하는 이 JSON 문서는 다음 샘플 테이블에 저장됩니다.

CREATE TABLE Families (
   id int identity constraint PK_JSON_ID primary key,
   doc nvarchar(max)
)

ISJSON 함수를 사용하여 JSON 텍스트 유효성 검사

ISJSON 함수는 문자열에 유효한 JSON이 포함되어 있는지 여부를 테스트합니다.

다음 예제에서는 JSON 열에 유효한 JSON 텍스트가 포함된 행을 반환합니다. 명시적 JSON 제약 조건이 없으면 NVARCHAR 열에 텍스트를 입력할 수 있습니다.

SELECT *
FROM Families
WHERE ISJSON(doc) > 0

자세한 내용은 ISJSON(Transact-SQL)을 참조하세요.

JSON_VALUE 함수를 사용하여 JSON 텍스트에서 값 추출

JSON_VALUE 함수는 JSON 문자열에서 스칼라 값을 추출합니다. 다음 쿼리는 idJSON 필드가 city 및 state JSON 필드를 기준으로 정렬된 값 AndersenFamily과 일치하는 문서를 반환합니다.

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC

이 쿼리의 결과는 다음 표에 나와 있습니다.

입력	구/군/시	군
AndersenFamily	NY	Manhattan

자세한 내용은 JSON_VALUE(Transact-SQL)를 참조 하세요.

JSON_QUERY 함수를 사용하여 JSON 텍스트에서 개체 또는 배열 추출

JSON_QUERY 함수는 JSON 문자열에서 개체 또는 배열을 추출합니다. 다음 예제에서는 쿼리 결과에서 JSON 조각을 반환하는 방법을 보여줍니다.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
       JSON_QUERY(f.doc, '$.parents') AS Parents,
       JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'

이 쿼리의 결과는 다음 표에 나와 있습니다.

주소	부모	Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" }	[{ "familyName": "Wakefield", "givenName": "Robin" }, {"familyName": "Miller", "givenName": "Ben" } ]	{ "familyName": "Wakefield", "givenName": "Robin" }

자세한 내용은 JSON_QUERY(Transact-SQL)를 참조하세요.

중첩된 JSON 컬렉션 구문 분석

OPENJSON 함수를 사용하면 JSON 하위 배열을 행 집합으로 변환한 다음, 부모 요소와 조인할 수 있습니다. 예를 들어 모든 패밀리 문서를 반환하고 내부 JSON 배열로 저장된 개체와 children "조인"할 수 있습니다.

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       c.givenName, c.grade
FROM Families f
		CROSS APPLY OPENJSON(f.doc, '$.children')
			WITH(grade int, givenName nvarchar(100))  c

이 쿼리의 결과는 다음 표에 나와 있습니다.

입력	City	givenName	등급
AndersenFamily	NY	Jesse	6
AndersenFamily	NY	Lisa	8

한 부모 행이 자식 하위 배열의 두 요소를 구문 분석하여 생성된 두 개의 자식 행과 조인되기 때문에 결과적으로 두 개의 행을 가져옵니다. OPENJSON 함수는 doc 열에서 children 조각을 구문 분석하고 각 요소에서 grade 및 givenName을 행 세트로 반환합니다. 이 행 집합은 부모 문서와 조인할 수 있습니다.

중첩된 계층적 JSON 하위 배열 쿼리

중첩된 JSON 구조를 쿼리하기 위해 여러 CROSS APPLY OPENJSON 호출을 적용할 수 있습니다. 이 예제에 사용된 JSON 문서에는 각 자식의 중첩 배열이 있는 중첩된 배열pets이 있습니다children. 다음 쿼리는 각 문서의 자식을 구문 분석하고 각 배열 개체를 행으로 반환한 다음 배열을 pets 구문 분석합니다.

SELECT	familyName,
	c.givenName AS childGivenName,
	c.firstName AS childFirstName,
	p.givenName AS petName 
FROM Families f 
	CROSS APPLY OPENJSON(f.doc) 
		WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
		CROSS APPLY OPENJSON(children) 
		WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
			OUTER APPLY OPENJSON (pets)
			WITH (givenName nvarchar(100))  as p

첫 번째 OPENJSON 호출은 AS JSON 절을 사용하여 배열 조각을 children 반환합니다. 이 배열 조각은 각 자식의 두 번째 함수와 배열을 pets반환 firstNamegivenName하는 두 번째 OPENJSON 함수에 제공됩니다. pets 배열은 애완 동물의 반환 세 번째 OPENJSON 함수에 givenName 제공됩니다. 이 쿼리의 결과는 다음 표에 나와 있습니다.

familyName	childGivenName	childFirstName	petName
AndersenFamily	Jesse	Merriam	바보 같은
AndersenFamily	Jesse	Merriam	Shadow
AndersenFamily	Lisa	Miller	`NULL`

루트 문서는 첫 번째 OPENJSON(children) 호출에서 두 개의 children 행(또는 튜플)을 만들어 반환된 두 개의 행과 조인됩니다. 그런 다음 각 행이 연산자를 사용하여 OUTER APPLY 생성된 새 행과 조인됩니다OPENJSON(pets). 제시는 두 마리의 애완 동물을 가지고 있으므로 (AndersenFamily, Jesse, Merriam) 구피와 그림자에 대해 생성 된 두 개의 행과 결합됩니다. 리사는 애완 동물이 없기 때문에이 튜플에 대해 반환 된 OPENJSON(pets) 행이 없습니다. 그러나 OUTER APPLY를 사용하고 있기 때문에 열에 NULL이 반환됩니다. OUTER APPLY 대신, CROSS APPLY를 사용하는 경우 이 튜플에 조인할 수 있는 애완 동물 행이 없기 때문에 Lisa는 결과에 반환되지 않습니다.

JSON_VALUE 및 JSON_QUERY 비교

JSON_VALUE JSON_QUERY 간의 주요 차이점은 JSON_VALUE 스칼라 값을 반환하는 반면 JSON_QUERY 개체 또는 배열을 반환한다는 것입니다.

다음 샘플 JSON 텍스트를 고려합니다.

{
	"a": "[1,2]",
	"b": [1, 2],
	"c": "hi"
}

이 샘플 JSON 텍스트에서 데이터 멤버 "a" 및 "c"는 문자열 값이고 데이터 멤버 "b"는 배열입니다. JSON_VALUE 및 JSON_QUERY 는 다음 결과를 반환합니다.

Path	JSON_VALUE 는 다음을 반환합니다.	JSON_QUERY 반환
$	NULL 또는 오류	`{ "a": "[1,2]", "b": [1,2], "c":"hi"}`
$.a	[1,2]	NULL 또는 오류
$.b	NULL 또는 오류	[1,2]
$.b[0]	6	NULL 또는 오류
$.c	hi	NULL 또는 오류

AdventureWorks 샘플 데이터베이스를 사용하여 JSON_VALUE 테스트 및 JSON_QUERY

AdventureWorks 예제 데이터베이스로 다음 예제를 실행하여 이 항목에서 설명하는 기본 제공 함수를 테스트합니다. AdventureWorks를 가져올 위치 및 스크립트를 실행하여 테스트용 JSON 데이터를 추가하는 방법에 대한 자세한 내용은 시험 사용 기본 제공 JSON 지원을 참조하세요.

다음 예제에서 SalesOrder_json 테이블의 Info 열에는 JSON 텍스트가 포함되어 있습니다.

예제 1 - 표준 열과 JSON 데이터 모두 반환

다음 쿼리는 표준 관계형 열과 JSON 열의 값을 모두 반환합니다.

SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, AccountNumber, TotalDue,
 JSON_QUERY(Info,'$.ShippingInfo') ShippingInfo,
 JSON_QUERY(Info,'$.BillingInfo') BillingInfo,
 JSON_VALUE(Info,'$.SalesPerson.Name') SalesPerson,
 JSON_VALUE(Info,'$.ShippingInfo.City') City,
 JSON_VALUE(Info,'$.Customer.Name') Customer,
 JSON_QUERY(OrderItems,'$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0

예제 2- JSON 값 집계 및 필터링

다음 쿼리는 고객 이름(JSON에 저장)과 상태(일반 열에 저장)를 기준으로 소계를 집계합니다. 그런 다음 도시(JSON에 저장됨) 및 OrderDate(일반 열에 저장됨)로 결과를 필터링합니다.

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid=3;

SET @city=N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer, Status, SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID=@territoryid
 AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
 AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'), Status
HAVING SUM(SubTotal)>1000

JSON_MODIFY 함수를 사용하여 JSON 텍스트의 속성 값 업데이트

JSON_MODIFY 함수는 JSON 문자열의 속성 값을 업데이트하고 업데이트된 JSON 문자열을 반환합니다.

다음 예제에서는 JSON이 포함된 변수에서 JSON 속성의 값을 업데이트합니다.

SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London')

자세한 내용은 JSON_MODIFY(Transact-SQL)를 참조 하세요.

SQL Server 및 Azure SQL Database의 JSON에 대해 자세히 알아보기

Microsoft 비디오

참고 항목

이 섹션의 일부 비디오 링크는 현재 작동하지 않을 수 있습니다. Microsoft는 이전에 Channel 9에 있던 콘텐츠를 새 플랫폼으로 마이그레이션하고 있습니다. 비디오가 새 플랫폼으로 마이그레이션되면 링크를 업데이트할 예정입니다.

참고 항목

ISJSON(Transact-SQL)
JSON_VALUE(Transact-SQL)
JSON_QUERY(Transact-SQL)
JSON_MODIFY(Transact-SQL)
JSON 경로 식(SQL Server)

기본 제공 함수를 사용하여 JSON 데이터 유효성 검사, 쿼리 및 변경(SQL Server)

이 페이지의 예제에 대한 JSON 텍스트

ISJSON 함수를 사용하여 JSON 텍스트 유효성 검사

JSON_VALUE 함수를 사용하여 JSON 텍스트에서 값 추출

JSON_QUERY 함수를 사용하여 JSON 텍스트에서 개체 또는 배열 추출

중첩된 JSON 컬렉션 구문 분석

중첩된 계층적 JSON 하위 배열 쿼리

JSON_VALUE 및 JSON_QUERY 비교

AdventureWorks 샘플 데이터베이스를 사용하여 JSON_VALUE 테스트 및 JSON_QUERY

예제 1 - 표준 열과 JSON 데이터 모두 반환

예제 2- JSON 값 집계 및 필터링

JSON_MODIFY 함수를 사용하여 JSON 텍스트의 속성 값 업데이트

SQL Server 및 Azure SQL Database의 JSON에 대해 자세히 알아보기

Microsoft 비디오

참고 항목

피드백

피드백

추가 리소스