MSSQL 2016과 이후 버전에서 JSON 처리 방법

MSSQL 2016과 이후 버전에서 JSON 처리 방법

MSSQL 2016 이후부터 SQL Server는 JSON 데이터를 직접 관리할 수 있는 다양한 기능을 제공합니다. 이를 통해 JSON 데이터를 저장, 조회, 수정하는 작업이 훨씬 더 쉬워졌습니다. 이번 글에서는 주니어와 시니어 개발자 모두가 JSON 데이터를 효과적으로 다룰 수 있도록 주요 JSON 관련 함수와 활용 방법을 예제와 함께 설명합니다. 또한, LIKE 구문과 WHERE 조건을 통해 JSON 데이터를 효율적으로 필터링하는 방법도 함께 다룹니다.

1. JSON 데이터를 저장하는 방법

MSSQL에서 JSON 데이터를 저장하려면 NVARCHAR(MAX) 타입의 컬럼을 사용합니다. 이를 통해 JSON 데이터를 문자열로 저장하고 관리할 수 있습니다.

테이블 생성 및 데이터 삽입

CREATE TABLE JsonTest (
    Id INT PRIMARY KEY,
    JsonData NVARCHAR(MAX)
);

INSERT INTO JsonTest (Id, JsonData) 
VALUES (1, N'{
    "EmployeeInfo": {
        "FirstName": "John",
        "LastName": "Doe",
        "Dob": "1970-11-06",
        "AnnualSalary": 85000
    }
}');

중첩된 JSON 데이터 저장

JSON 데이터는 단순한 텍스트뿐만 아니라 배열이나 중첩된 객체도 포함할 수 있습니다.

INSERT INTO JsonTest (Id, JsonData) 
VALUES (2, N'{
    "EmployeeInfo": {
        "FirstName": "Jane",
        "LastName": "Smith",
        "Dob": "1955-11-06",
        "AnnualSalary": 95000,
        "Dependents": [
            {"Name": "Alice", "Age": 10},
            {"Name": "Bob", "Age": 7}
        ]
    }
}');

2. JSON 관련 함수 소개

ISJSON: JSON 유효성 검사

ISJSON() 함수는 입력된 문자열이 유효한 JSON 형식인지 확인합니다.

SELECT ISJSON(JsonData) FROM JsonTest;

유효한 JSON이면 1, 그렇지 않으면 0을 반환합니다.

JSON_VALUE: 특정 값 추출

JSON_VALUE()는 JSON 데이터 내 특정 경로의 스칼라 값을 추출할 때 사용됩니다.

SELECT JSON_VALUE(JsonData, '$.EmployeeInfo.FirstName') FROM JsonTest;

이 쿼리는 직원의 이름인 "John"을 반환합니다.

중첩된 값 추출 예시

SELECT JSON_VALUE(JsonData, '$.EmployeeInfo.Dependents[0].Name') FROM JsonTest;

첫 번째 자녀의 이름인 "Alice"를 반환합니다.

JSON_QUERY: 복합 객체나 배열 추출

JSON_QUERY()는 JSON 데이터에서 배열이나 객체를 추출하는 데 사용됩니다.

SELECT JSON_QUERY(JsonData, '$.EmployeeInfo.Dependents') FROM JsonTest;

결과는 다음과 같습니다:

[
    {"Name": "Alice", "Age": 10},
    {"Name": "Bob", "Age": 7}
]

JSON_MODIFY: JSON 데이터 수정

JSON_MODIFY()는 JSON 데이터의 특정 값을 수정하는 데 사용됩니다.

UPDATE JsonTest
SET JsonData = JSON_MODIFY(JsonData, '$.EmployeeInfo.AnnualSalary', 95000)
WHERE JSON_VALUE(JsonData, '$.EmployeeInfo.AnnualSalary') = 85000;

이 쿼리는 연봉을 95,000으로 수정합니다.

OPENJSON: JSON 데이터를 테이블 형식으로 변환

OPENJSON() 함수는 JSON 데이터를 테이블 형식으로 변환하여 SQL 쿼리에서 쉽게 사용할 수 있게 합니다.

--Dependents가 무조건 존재해야 함.
SELECT * 
FROM OPENJSON((SELECT JSON_QUERY(JsonData, '$.EmployeeInfo.Dependents') FROM JsonTest))
WITH (
    Name NVARCHAR(50),
    Age INT
);

--Dependents가 있는 row 만 출력함.
SELECT jt.Id, 
       dependents.*
FROM JsonTest jt
CROSS APPLY OPENJSON(JSON_QUERY(jt.JsonData, '$.EmployeeInfo.Dependents'))
WITH (
    Name NVARCHAR(50),
    Age INT
) AS dependents;

 

결과는 다음과 같은 테이블 형식으로 변환됩니다:

 

 

FOR JSON: SQL 결과를 JSON으로 변환

FOR JSON 구문은 SQL 쿼리 결과를 JSON 형식으로 반환하는 데 사용됩니다.

SELECT Id, JsonData FROM JsonTest FOR JSON AUTO;

이 쿼리는 결과를 JSON 형식으로 자동 포맷합니다.

3. LIKE 구문과 WHERE 조건을 활용한 JSON 데이터 필터링

JSON 데이터 내에서 특정 조건에 맞는 값을 검색하거나 패턴 매칭을 할 때 LIKE 구문과 WHERE 절을 활용할 수 있습니다.

LIKE 구문을 사용한 JSON 데이터 검색

LIKE 구문을 사용하여 JSON 데이터 내에서 특정 패턴을 검색할 수 있습니다.

SELECT *
FROM JsonTest
WHERE JSON_VALUE(JsonData, '$.EmployeeInfo.FirstName') LIKE 'John%';

이 쿼리는 이름이 "John"으로 시작하는 직원을 검색합니다.

중첩된 JSON 배열에서 값 찾기

SELECT *
FROM JsonTest
WHERE JSON_VALUE(JsonData, '$.EmployeeInfo.Dependents[0].Name') LIKE 'A%';

이 쿼리는 첫 번째 자녀의 이름이 "A"로 시작하는 직원을 검색합니다.

WHERE 조건을 사용한 데이터 필터링

WHERE 절을 사용해 JSON 데이터에서 특정 조건에 맞는 값을 필터링할 수 있습니다.

SELECT *
FROM JsonTest
WHERE JSON_VALUE(JsonData, '$.EmployeeInfo.AnnualSalary') >= 90000;

이 쿼리는 연봉이 90,000 이상인 직원을 조회합니다.

날짜 필터링 예시

SELECT *
FROM JsonTest
WHERE CAST(JSON_VALUE(JsonData, '$.EmployeeInfo.Dob') AS DATE) > '1980-01-01';

이 쿼리는 1980년 이후에 태어난 직원들을 검색합니다.

4. 실전 팁

주니어 개발자를 위한 팁

• 경로 표현식을 정확하게 사용하세요. 경로는 $로 시작하며, 각 속성은 .으로 구분됩니다.
• JSON 데이터를 다루기 전 ISJSON() 함수로 데이터 유효성을 확인하는 것이 좋습니다.

시니어 개발자를 위한 팁

• 복잡한 JSON 구조는 OPENJSON()으로 테이블 형식으로 변환한 후, SQL의 WHERE 절을 활용해 더 복잡한 조건을 쉽게 처리할 수 있습니다.
• 성능을 최적화하려면 JSON 데이터에서 자주 사용되는 값에 대해 적절한 인덱스를 사용하거나, JSON_VALUE()의 호출을 최소화하는 것이 좋습니다.

결론

MSSQL 2016 이후부터 JSON을 저장하고 관리하는 것이 매우 쉬워졌습니다. 이 글에서는 JSON 데이터를 처리하는 데 필요한 주요 함수들과 실전 예제들을 다루었으며, LIKE 구문과 WHERE 절을 통해 원하는 데이터를 효율적으로 필터링하는 방법도 살펴보았습니다. 이 기능들을 적절히 활용하면 JSON 데이터 처리가 훨씬 더 유연하고 강력해집니다.

참고 자료:

• MSSQL JSON 함수 공식 문서
• SQL Server 2016의 JSON 지원

---

 

궁금한 점?

 

아래 쿼리:

SELECT * 
FROM OPENJSON((SELECT JSON_QUERY(JsonData, '$.EmployeeInfo.Dependents') FROM JsonTest))
WITH (
    Name NVARCHAR(50),
    Age INT
);

 

이 쿼리는 특정 형식의 데이터를 요구합니다. 주된 조건은 JSON 데이터의 특정 필드(Dependents)가 배열 형식으로 존재해야 한다는 것입니다. 이 쿼리는 Dependents 필드가 JSON 배열로 포함된 데이터를 처리할 수 있도록 설계되었습니다.

 

올바른 데이터 형식

이 쿼리를 사용하려면, JSON 데이터 안에 다음과 같은 형식으로 Dependents 배열이 포함되어 있어야 합니다:

{
    "EmployeeInfo": {
        "FirstName": "John",
        "LastName": "Doe",
        "Dob": "12-Jan-1970",
        "AnnualSalary": 85000,
        "Dependents": [
            {"Name": "Alice", "Age": 10},
            {"Name": "Bob", "Age": 7}
        ]
    }
}

핵심 요구 사항:

• Dependents는 JSON 배열이어야 합니다: 배열은 [ ]로 감싸져 있으며, 그 안에 Name과 Age라는 키가 포함된 객체들이 존재해야 합니다.
• 각 배열 요소는 객체여야 합니다: { "Name": "Alice", "Age": 10 }와 같은 형식.

이 형식을 유지하고 있어야만, OPENJSON 함수가 해당 배열을 테이블 형식으로 변환하여 Name과 Age 컬럼을 추출할 수 있습니다.

오류 발생 이유

만약 Dependents 필드가 없거나, 배열이 아닌 단일 객체로 저장되어 있거나, JSON 배열 형식이 잘못되었다면 쿼리가 예상대로 작동하지 않습니다. 예를 들어, 다음과 같은 경우 오류가 발생할 수 있습니다:

{
    "EmployeeInfo": {
        "FirstName": "John",
        "LastName": "Doe",
        "Dob": "12-Jan-1970",
        "AnnualSalary": 85000
    }
}

 

여기서는 Dependents 필드가 없기 때문에 OPENJSON은 빈 결과를 반환하게 됩니다.

해결 방법:

Dependents가 배열 형식으로 존재하는지 확인하고, CROSS APPLY와 같은 방식을 활용하여 모든 행에서 OPENJSON을 사용할 수 있도록 처리하는 것이 좋습니다.

 

SELECT jt.Id, 
       dependents.*
FROM JsonTest jt
CROSS APPLY OPENJSON(JSON_QUERY(jt.JsonData, '$.EmployeeInfo.Dependents'))
WITH (
    Name NVARCHAR(50),
    Age INT
) AS dependents;