DONGBUM KIM

Beat Yesterday

2 분 소요

MSSQL에서 JSON을 지원하기 시작했다. 아래에서는 JSON을 다루기 위한 방법들이다.

중요사항 : MSSQL 2016 버전부터 JSON 기능을 지원한다.

2026년 수정 안내 이 글에 세 군데 틀린 내용이 있어 바로잡았다. ISJSON의 조건문 작성법, JSON_MODIFY로 테이블을 갱신하는 방법, 그리고 “OPENJSON은 SELECT에서 바로 쓸 수 없다”는 서술이다. 마지막 것은 사실이 아니다.

JSON 데이터를 저장하기 위한 컬럼 타입은 NVARCHAR(MAX) 를 사용한다.

INSERT 시 다음 처럼 입력한다.

INSERT INTO jsontest VALUES (1, N'{"EmployeeInfo": {
            "FirstName": "John",
            "LastName": "Doe",
            "Dob": "12-Jan-1970",
            "AnnualSalary": 85000
        }}')

SELECT 하면 JSON 데이터가 그대로 나온다.

DB의 한 컬럼에 그냥 JSON 데이터를 그대로 넣는다. 다만, MSSQL에 JSON 형식의 데이터를 다루기 위한 함수가 추가되어 있을 뿐이다.

JSON 관련 함수

JSON 관련 중요 함수들.

ISJSON

JSON 데이터의 유효성을 판단한다. JSON 데이터가 맞다면 1, 아니면 0을 리턴한다.

T-SQL의 IF는 C 계열 언어와 달리 논리식을 요구한다. 정수를 그대로 조건에 넣을 수 없으므로 반드시 비교 연산자를 써야 한다.

IF ISJSON( @data ) = 1        -- IF ( ISJSON(@data) ) 는 구문 오류다
    PRINT '유효한 JSON'

CHECK 제약 조건으로 걸어두면 잘못된 데이터가 아예 들어오지 못하게 막을 수 있다.

ALTER TABLE jsontest
    ADD CONSTRAINT CK_jsontest_json CHECK ( ISJSON( json_data ) = 1 )

JSON_VALUE

SELECT JSON_VALUE(json_data, '$.EmployeeInfo.FirstName') FROM jsontest

의 방법으로 json_data 안에 있는 특정 데이터를 가져올 수 있다.

참고자료에 있는 대로 같은 속성이 여러개 있다면 인덱스로 접근 가능하다. 인덱스는 0부터 시작한다.

하나 불편한 점은, 이렇게 SQL을 입력하는 과정에서 코드힌트 등의 기능은 작동하지 않는다. 내가 조회하려는 데이터의 속성명을 정확하게 입력해야만 한다.

JSON_QUERY

지정된 속성 밑에 있는 문자열들을 JSON 데이터로써 리턴한다.

예를 들면,

SELECT JSON_VALUE(json_data, '$.EmployeeInfo') FROM jsontest
SELECT JSON_QUERY(json_data, '$.EmployeeInfo') FROM jsontest

EmployeeInfo 에 있는 데이터를 사용하고 싶을 때, 위의 것은 NULL이 반환되지만 밑에는 정상적으로 EmployeeInfo 밑의 데이터들이 출력된다.

JSON_MODIFY

JSON 데이터의 값을 수정한다.

JSON_MODIFY는 값을 바꾼 새 JSON 문자열을 반환하는 함수다. 원본을 그 자리에서 고치는 것이 아니다.

따라서 SELECT에서 쓰면 결과 집합에만 반영되고 테이블은 그대로다.

-- 변수의 값을 바꾸는 경우 (테이블은 건드리지 않는다)
DECLARE @info NVARCHAR(MAX)
SET @info = JSON_MODIFY( @jsonInfo, '$.info.address[0].town', 'London' )

경로는 반드시 작은따옴표로 감싼다. QUOTED_IDENTIFIER가 켜진 기본 설정에서 큰따옴표는 문자열이 아니라 식별자로 해석되어 오류가 난다.

테이블의 JSON 컬럼을 실제로 갱신하려면 UPDATE의 SET 절에 써야 한다.

UPDATE [GameDB].[dbo].[ItemEquip]
SET [Value] = JSON_MODIFY([Value], '$.DyeList[1]', 0)
WHERE JSON_VALUE([Value], '$.DyeList[1]') = 4278190080

OPENJSON

간단히 얘기해서, JSON 데이터를 RDBMS에서 흔히 사용하던 테이블 형식 즉, 컬럼/레코드 형식으로 바꾸어 반환한다.

테이블 형식으로 바뀐 데이터에 기존의 SQL처럼 SELECT 등을 할 수 있다.

다음처럼 사용 가능하다.

DECLARE @json NVARCHAR(MAX)
SET @json = (SELECT JSON_QUERY(json_data, '$.EmployeeInfo') FROM jsontest)

SELECT * FROM OPENJSON(@json)

OPENJSON은 테이블 반환 함수이므로 FROM 절에 온다. 위처럼 변수에 담아 쓸 수도 있지만, CROSS APPLY를 쓰면 변수 없이 SELECT에서 바로 사용할 수 있다. 행마다 JSON을 펼쳐야 할 때는 이 방법이 필수적이다.

SELECT  t.id
,       j.[key]
,       j.[value]
FROM    jsontest AS t
CROSS APPLY OPENJSON( t.json_data, '$.EmployeeInfo' ) AS j

WITH 절로 스키마를 지정하면 원하는 컬럼 형태로 바로 꺼낼 수 있다.

SELECT  t.id
,       e.FirstName
,       e.AnnualSalary
FROM    jsontest AS t
CROSS APPLY OPENJSON( t.json_data, '$.EmployeeInfo' )
    WITH (
        FirstName     NVARCHAR(100) '$.FirstName'
    ,   AnnualSalary  INT           '$.AnnualSalary'
    ) AS e

FOR JSON

for json은 테이블에 있는 데이터들을 JSON 형식으로 내보내기 위한 함수이다.

SELECT * FROM jsontest FOR JSON AUTO

를 입력하면 테이블에 있는 데이터를 JSON 형식으로 변경하여 보여주게 된다.

알아둘 점

  • JSON 컬럼에 인덱스를 직접 걸 수는 없다. 자주 조회하는 속성이 있다면 JSON_VALUE로 계산 열(computed column) 을 만들고 거기에 인덱스를 거는 것이 표준적인 방법이다.

    ALTER TABLE jsontest
    ADD FirstName AS JSON_VALUE( json_data, '$.EmployeeInfo.FirstName' )

CREATE INDEX IX_jsontest_FirstName ON jsontest( FirstName )

  • SQL Server 2025부터는 네이티브 json 데이터 타입이 추가되었다. 그 이전 버전에서는 이 글처럼 NVARCHAR(MAX)를 쓴다.

참고자료

댓글 남기기

참고