Microsoft SQL Server
SQL Server의 JSON

통사론

• JSON_VALUE (expression, path) - JSON 문자열에서 스칼라 값을 추출합니다.
• JSON_QUERY (expression [, path]) - JSON 문자열에서 객체 또는 배열을 추출합니다.
• OPENJSON (jsonExpression [, path]) - JSON 텍스트를 구문 분석하고 JSON의 객체 및 속성을 행 및 열로 반환하는 테이블 값 함수입니다.
• ISJSON (expression) - 문자열에 유효한 JSON이 있는지 여부를 테스트합니다.
• JSON_MODIFY (expression, path, newValue) - JSON 문자열의 속성 값을 업데이트하고 업데이트 된 JSON 문자열을 반환합니다.

매개 변수

비고

ALTER DATABASE <Database-Name-Here> SET COMPATIBILITY_LEVEL = 130

FOR JSON을 사용하여 쿼리 결과를 JSON 형식으로 서식 지정

입력 테이블 데이터 (피인 테이블)

질문

SELECT Id, Name, Age
FROM People
FOR JSON PATH

결과

[
    {"Id":1,"Name":"John","Age":23},
    {"Id":2,"Name":"Jane","Age":31}
]

JSON 텍스트 구문 분석

JSON_VALUE 및 JSON_QUERY 함수는 JSON 텍스트를 구문 분석하고 JSON 텍스트의 경로에 스칼라 값 또는 객체 / 배열을 반환합니다.

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT
    JSON_VALUE(@json, '$.id') AS Id,
    JSON_VALUE(@json, '$.user.name') AS Name,
    JSON_QUERY(@json, '$.user') AS UserObject,
    JSON_QUERY(@json, '$.skills') AS Skills,
    JSON_VALUE(@json, '$.skills[0]') AS Skill0

결과

CROSS APPLY OPENJSON을 사용하여 부모 및 자식 JSON 엔터티에 가입

부모 엔티티를 자식 엔티티와 결합하십시오. 예를 들어, 각 개인의 관계형 테이블과 취미를 원합니다.

DECLARE @json nvarchar(1000) =
N'[
    {
        "id":1,
        "user":{"name":"John"},
        "hobbies":[
            {"name": "Reading"},
            {"name": "Surfing"}
        ]
    },
    {
        "id":2,
        "user":{"name":"Jane"},
        "hobbies":[
            {"name": "Programming"},
            {"name": "Running"}
        ]
    }
 ]'

질문

SELECT 
    JSON_VALUE(person.value, '$.id') as Id,
    JSON_VALUE(person.value, '$.user.name') as PersonName,
    JSON_VALUE(hobbies.value, '$.name') as Hobby
FROM OPENJSON (@json) as person
    CROSS APPLY OPENJSON(person.value, '$.hobbies') as hobbies

또는이 쿼리는 WITH 절을 사용하여 작성할 수 있습니다.

SELECT 
    Id, person.PersonName, Hobby
FROM OPENJSON (@json)
WITH(
    Id int '$.id',
    PersonName nvarchar(100) '$.user.name',
    Hobbies nvarchar(max) '$.hobbies' AS JSON
) as person
CROSS APPLY OPENJSON(Hobbies)
WITH(
    Hobby nvarchar(100) '$.name'
)

결과

계산 된 열을 사용하여 JSON 속성에 대한 색인 생성

JSON 문서를 SQL Server에 저장할 때 JSON 문서의 속성에 대한 쿼리 결과를 효율적으로 필터링하고 정렬 할 수 있어야합니다.

CREATE TABLE JsonTable
(
    id int identity primary key,
    jsonInfo nvarchar(max),
    CONSTRAINT [Content should be formatted as JSON]
    CHECK (ISJSON(jsonInfo)>0)
)

INSERT INTO JsonTable
VALUES(N'{"Name":"John","Age":23}'),
(N'{"Name":"Jane","Age":31}'),
(N'{"Name":"Bob","Age":37}'),
(N'{"Name":"Adam","Age":65}')
GO

위의 테이블에서 주어진 이름이 'Adam'인 행을 찾으려면 다음 쿼리를 실행합니다.

SELECT * 
FROM JsonTable Where 
JSON_VALUE(jsonInfo, '$.Name') = 'Adam'

그러나 이것은 대형 테이블에서 효율성이없는 전체 테이블을 수행하기 위해 SQL Server를 요구합니다.

이를 빠르게하기 위해 인덱스를 추가하고 싶지만 JSON 문서의 속성을 직접 참조 할 수는 없습니다. 해결 방법은 계산 된 열을 JSON 경로 $.Name 에 추가 한 다음 계산 된 열에 인덱스를 추가하는 것입니다.

ALTER TABLE JsonTable
ADD vName as JSON_VALUE(jsonInfo, '$.Name')

CREATE INDEX idx_name
ON JsonTable(vName)

이제 전체 테이블 검색 대신 동일한 쿼리를 실행하면 SQL Server는 인덱스를 사용하여 클러스터되지 않은 인덱스를 찾고 지정된 조건을 만족하는 행을 찾습니다.

참고 : SQL 서버가 인덱스를 사용하려면 쿼리에 사용할 식 JSON_VALUE(jsonInfo, '$.Name') 이 예제에서는 JSON_VALUE(jsonInfo, '$.Name') 과 동일한 표현식을 사용하여 계산 열을 만들어야하지만, 계산 된 열의 이름 vName

FOR JSON을 사용하여 하나의 테이블 행을 단일 JSON 객체로 포맷

FOR JSON 절의 WITHOUT_ARRAY_WRAPPER 옵션은 JSON 출력에서 ​​배열 대괄호를 제거합니다. 쿼리에서 단일 행을 반환하는 경우 유용합니다.

참고 : 두 개 이상의 행이 반환되면이 옵션은 잘못된 JSON 출력을 생성합니다.

입력 테이블 데이터 (피인 테이블)

질문

SELECT Id, Name, Age
FROM People
WHERE Id = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

결과

{"Id":1,"Name":"John","Age":23}

OPENJSON 함수를 사용하여 JSON 텍스트 구문 분석

OPENJSON 함수는 JSON 텍스트를 파싱하고 여러 출력을 반환합니다. 반환되어야하는 값은 WITH 절에 정의 된 경로를 사용하여 지정됩니다. 일부 열에 대해 경로가 지정되지 않은 경우 열 이름이 경로로 사용됩니다. 이 함수는 반환 된 값을 WITH 절에 정의 된 SQL 형식으로 캐스팅합니다. 일부 오브젝트 / 배열을 리턴해야하는 경우 JSON 옵션을 컬럼 정의에 지정해야합니다.

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT * 
FROM OPENJSON (@json)
    WITH(Id int '$.id',
        Name nvarchar(100) '$.user.name',
        UserObject nvarchar(max) '$.user' AS JSON,
        Skills nvarchar(max) '$.skills' AS JSON,
        Skill0 nvarchar(20) '$.skills[0]')

결과