OPENJSON을 사용하여 JSON 데이터 구문 분석 및 변환

  • 아티클

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL 데이터베이스Azure SQL Managed InstanceAzure Synapse Analytics

OPENJSON 행 집합 함수는 JSON 텍스트를 행과 열의 집합으로 변환합니다. OPENJSON을 사용하여 JSON 컬렉션을 행 집합으로 변환한 후 반환된 데이터에 대해 SQL 쿼리를 실행하거나 SQL Server 테이블에 삽입할 수 있습니다. SQL Server 데이터베이스 엔진의 JSON 데이터 작업에 대한 자세한 내용은 SQL Server의 JSON 데이터를 참조하세요.

OPENJSON 함수는 단일 JSON 개체 또는 JSON 개체 컬렉션을 받아서 하나 이상의 행으로 변환합니다. 기본적으로 OPENJSON 함수는 다음 데이터를 반환합니다.

  • JSON 개체에서 함수는 첫 번째 수준에서 찾은 모든 키/값 쌍을 반환합니다.
  • JSON 배열에서 이 함수는 모든 요소 및 해당 인덱스를 반환합니다.

선택적 WITH 절을 추가하여 출력의 구조를 명시적으로 정의하는 스키마를 제공할 수 있습니다.

기본 출력이 있는 OPENJSON

결과에 대한 명시적 스키마를 제공하지 않고, 즉 OPENJSON 뒤에 WITH 절 없이 OPENJSON 함수를 사용하는 경우 함수에서 다음 세 개의 열이 있는 테이블을 반환합니다.

  1. 입력 개체의 속성 name(또는 입력 배열의 요소 인덱스).
  2. 속성의 또는 배열 요소의 value.
  3. type(예: 문자열, 숫자, 부울, 배열 또는 개체).

OPENJSON은 JSON 개체의 각 속성 또는 배열의 각 요소를 별도의 행으로 반환합니다.

다음 예제에서는 기본 스키마(선택적 WITH 절 없음)와 함께 OPENJSON을 사용하여 JSON 객체의 각 속성에 대해 하나의 행을 반환합니다.

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

결과 집합은 다음과 같습니다.

key 값 type
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

자세한 내용과 예제를 보려면 기본 스키마와 함께 OPENJSON 사용을 참조하세요.

구문 및 사용법은 OPENJSON을 참조하세요.

명시적 구조가 있는 OPENJSON 출력

OPENJSON 함수의 WITH 절을 사용하여 결과에 대한 스키마를 지정하면 이 함수는 WITH 절에서 정의한 열만 있는 테이블을 반환합니다. 선택적 WITH 절에 출력 열 집합, 해당 형식 및 각 출력 값에 대한 JSON 원본 속성의 경로를 지정합니다. OPENJSON는 JSON 개체 배열을 반복하고 각 열의 지정된 경로에서 값을 읽은 다음 값을 지정된 유형으로 변환합니다.

다음 예제에서는 WITH 절에서 명시적으로 지정한 출력 스키마와 함께 OPENJSON을 사용합니다.

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
    {
        "Order": {
            "Number": "SO43659",
            "Date": "2024-05-31T00:00:00"
        },
        "AccountNumber": "AW29825",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 1
        }
    },
    {
        "Order": {
            "Number": "SO43661",
            "Date": "2024-06-01T00:00:00"
        },
        "AccountNumber": "AW73565",
        "Item": {
            "Price": 2024.9940,
            "Quantity": 3
        }
    }
]';

SELECT *
FROM OPENJSON(@json) WITH (
    Number VARCHAR(200) '$.Order.Number',
    DATE DATETIME '$.Order.Date',
    Customer VARCHAR(200) '$.AccountNumber',
    Quantity INT '$.Item.Quantity'
);

결과 집합은 다음과 같습니다.

Number Date Customer 수량
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

이 함수는 JSON 배열의 요소를 반환하고 형식을 지정합니다.

  • OPENJSON은 출력 테이블에 JSON 배열의 각 요소에 대한 새 행을 생성합니다. JSON 배열의 두 요소는 반환된 테이블의 두 행으로 변환됩니다.

  • colName type json_path 구문을 사용하여 지정한 각 열에 대해 OPENJSON은 지정된 경로의 각 배열 요소에서 찾은 값을 지정된 유형으로 변환합니다. 이 예제에서 Date 열의 값은 경로 $.Order.Date의 각 요소에서 가져와 날짜/시간 값으로 변환합니다.

자세한 내용과 예제를 보려면 명시적 스키마와 함께 OPENJSON 사용(SQL Server)을 참조하세요.

구문 및 사용법은 OPENJSON을 참조하세요.

OPENJSON에는 호환성 수준 130이 필요합니다.

OPENJSON 함수는 호환성 수준 130 이상에서만 사용할 수 있습니다. 데이터베이스 호환성 수준이 130보다 낮으면 SQL Server에서 OPENJSON 함수를 찾아 실행할 수 없습니다. 다른 기본 제공 JSON 함수는 모든 호환성 수준에서 사용할 수 있습니다.

sys.databases 보기 또는 데이터베이스 속성에서 호환성 수준을 확인하고 다음 명령을 사용하여 데이터베이스의 호환성 수준을 변경할 수 있습니다.

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

관련 콘텐츠