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

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

JSON에 대한 기본 지원에는 이 문서에 간략하게 설명된 다음과 같은 기본 제공 함수가 포함됩니다.

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

이 글은 AdventureWorks2022샘플 데이터터베이스를 필요로 하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

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

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

{
    "id": "DesaiFamily",
    "parents": [
        { "familyName": "Desai", "givenName": "Prashanth" },
        { "familyName": "Miller", "givenName": "Helen" }
    ],
    "children": [
        {
            "familyName": "Desai",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1,
            "pets": [
                { "givenName": "Goofy" },
                { "givenName": "Shadow" }
            ]
        },
        {
            "familyName": "Desai",
            "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 필드를 기준으로 정렬된 값 DesaiFamily과 일치하는 문서를 반환합니다.

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'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
    JSON_VALUE(f.doc, '$.address.state') ASC

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

속성	구/군/시	군
DesaiFamily	NY	Manhattan

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

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

JSON_QUERY 함수는 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'DesaiFamily';

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

주소	부모	Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" }	[ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ]	{ "familyName": "Desai", "givenName": "Prashanth" }

자세한 내용은 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

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

속성	구/군/시	givenName	등급
DesaiFamily	NY	Jesse	1
DesaiFamily	NY	Lisa	8

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

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

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

SELECT c.familyName,
    c.givenName AS childGivenName,
    p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
    familyName NVARCHAR(100),
    children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
    familyName NVARCHAR(100),
    givenName NVARCHAR(100),
    pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;

첫 번째 OPENJSON 호출은 AS JSON 절을 사용하여 children 배열의 조각을 반환합니다. 이 배열 조각은 OPENJSON 배열과 각 자식의 givenName, firstName을(를) 반환하는 두 번째 pets 함수에 제공됩니다. pets 배열은 애완동물의 OPENJSON을(를) 반환하는 세 번째 givenName 함수에 제공됩니다.

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

familyName	childGivenName	petName
Desai	Jesse	Goofy
Desai	Jesse	Shadow
Desai	Lisa	NULL

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

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은(는) 다음과 같은 결과를 반환합니다.

경로	JSON_VALUE 반환	JSON_QUERY 반환
$	NULL 또는 오류	{ "a": "[1,2]", "b": [1, 2], "c": "hi" }
$.a	[1,2]	NULL 또는 오류
$.b	NULL 또는 오류	[1,2]
$.b[0]	1	NULL 또는 오류
$.c	hi	NULL 또는 오류

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

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

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

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

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

SELECT SalesOrderNumber,
    OrderDate,
    Status,
    ShipDate,
    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)를 참조하세요.

관련 콘텐츠

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