OPENJSON(Transact-SQL)

적용 대상: Microsoft Fabric의 Microsoft FabricWarehouse에 있는 SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL 분석 엔드포인트

OPENJSON은 JSON 텍스트를 구문 분석하고 JSON 입력의 개체 및 속성을 행 및 열로 반환하는 테이블 반환 함수입니다. 즉, OPENJSON은 JSON 문서를 통해 행 집합 뷰를 제공합니다. 행 집합의 열과 열을 채우는 데 사용되는 JSON 속성 경로를 명시적으로 지정할 수 있습니다. OPENJSON은 행 세트를 반환하기 때문에 다른 테이블, 뷰 또는 테이블 반환 함수를 사용할 수 있는 것처럼, Transact-SQL 문의 FROM 절에서 OPENJSON을 사용할 수 있습니다.

OPENJSON을 사용하여 JSON 데이터를 SQL Server로 가져오거나 JSON을 직접 사용할 수 없는 앱이나 서비스에 대한 관계형 형식으로 JSON 데이터를 변환합니다.

Transact-SQL 구문 규칙

구문

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

OPENJSON 테이블 반환 함수는 첫 번째 인수로 제공된 jsonExpression를 구문 분석하고 식의 JSON 개체에서 데이터를 포함하는 하나 이상의 행을 반환합니다. jsonExpression은 중첩된 하위 개체를 포함할 수 있습니다. jsonExpression 내에서 하위 개체를 구문 분석하려는 경우 JSON 하위 개체에 대한 path 매개 변수를 지정할 수 있습니다.

openjson

기본적으로 OPENJSON 테이블 반환 함수는 키 이름, 값 및 jsonExpression에 있는 각 {키:값} 쌍의 형식을 포함하는 세 개의 열을 반환합니다. 대신, with_clause을 제공하여 OPENJSON이 반환하는 결과 집합의 스키마를 명시적으로 지정할 수 있습니다.

with_clause

with_clause는 반환할 OPENJSON에 대한 열과 형식의 목록을 포함합니다. 기본적으로 OPENJSON은 jsonExpression의 키와 with_clause의 열 이름을 일치시킵니다(이 경우 matches 키는 대/소문자를 구분함을 의미). 열 이름이 키 이름과 일치하지 않으면 jsonExpression 내의 키를 참조하는 JSON 경로 식인 선택적 column_path를 제공할 수 있습니다.

인수

jsonExpression

JSON 텍스트를 포함하는 유니코드 문자 식입니다.

OPENJSON은 JSON 식에 있는 배열의 요소 또는 개체의 속성을 반복하고 각 요소 또는 속성마다 하나의 행을 반환합니다. 다음 예제에서는 jsonExpression으로 제공된 개체의 각 속성을 반환합니다.

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

Results:

• DoublePrecisionFloatingPoint_value는 IEEE-754를 준수합니다.

path

jsonExpression 내의 개체 또는 배열을 참조하는 선택적 JSON 경로 식입니다. OPENJSON은 지정된 위치에서 JSON 텍스트를 검색하고 참조된 조각만 구문 분석합니다. 자세한 내용은 JSON 경로 식(SQL Server)을 참조하세요.

SQL Server 2017(14.x) 및 Azure SQL Database에서 path 값으로 변수를 제공할 수 있습니다.

다음 예제에서는 path를 지정하여 중첩된 개체를 반환합니다.

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

결과

OPENJSON이 JSON 배열을 구문 분석할 때, 이 함수는 JSON 텍스트에 있는 요소의 인덱스를 키로 반환합니다.

경로 단계와 JSON 식의 속성을 일치시키는 데 사용되는 비교는 대/소문자를 구분하고 데이터 정렬을 인식하지 못합니다(즉, BIN2 비교).

배열 요소 ID

Azure Synapse Analytics의 서버리스 SQL 풀에서 OPENJSON 함수는 결과로 반환되는 각 행의 ID를 자동으로 생성할 수 있습니다. ID 열은 JSON 경로에서 열 정의 뒤에 있는 $.sql:identity() 식을 사용하여 지정됩니다. JSON 경로 식에 이 값이 있는 열은 함수가 구문 분석하는 JSON 배열의 각 요소에 대해 고유한 0 기반 번호를 생성합니다. ID 값은 배열 요소의 위치/인덱스를 나타냅니다.

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

결과

ID는 Synapse Analytics의 서버리스 SQL 풀에서만 사용할 수 있습니다.

with_clause

반환할 OPENJSON 함수의 출력 스키마를 명시적으로 정의합니다. 선택적 with_clause는 다음 요소를 포함할 수 있습니다.

colName은 출력 열의 이름입니다.

기본적으로 OPENJSON은 열의 이름을 사용하여 JSON 텍스트의 속성을 일치시킵니다. 예를 들어, 스키마에 열 name을 지정하면 OPENJSON은 이 열을 JSON 텍스트의 "name" 속성으로 채웁니다. column_path 인수를 사용하여 이 기본 매핑을 재정의할 수 있습니다.

type
출력 열의 데이터 형식입니다.

column_path
지정된 열에서 반환할 속성을 지정하는 JSON 경로입니다. 자세한 내용은 이 토픽 이전의 path 매개 변수에 대한 설명을 참조하세요.

출력 열의 이름이 속성의 이름과 일치하지 않으면, column_path를 사용하여 기본 매핑 규칙을 재정의하세요.

경로 단계와 JSON 식의 속성을 일치시키는 데 사용되는 비교는 대/소문자를 구분하고 데이터 정렬을 인식하지 못합니다(즉, BIN2 비교).

경로에 대한 자세한 내용은 JSON 경로 식(SQL Server)을 참조하세요.

AS JSON
열 정의에 AS JSON 옵션을 사용하여 참조된 속성이 JSON 개체 또는 배열 내부에 포함되도록 지정합니다. AS JSON 옵션을 지정하면 열의 형식이 NVARCHAR(MAX)여야 합니다.

• 열에 AS JSON을 지정하지 않으면 지정된 경로의 지정된 JSON 속성에서 스칼라 값(예: int, string, true, false)이 반환됩니다. 경로가 개체 또는 배열을 나타내고, 지정된 경로에서 속성을 찾을 수 없으면, 이 함수는 lax 모드에서 null을 반환하거나 strict 모드에서 오류를 반환합니다. 이 동작은 JSON_VALUE 함수의 동작과 비슷합니다.

• 열에 AS JSON을 지정하면 지정된 경로의 지정된 JSON 속성에서 JSON 조각이 반환됩니다. 경로가 스칼라 값을 나타내고 지정된 경로에서 속성을 찾을 수 없으면, 이 함수는 lax 모드에서 null을 반환하거나 strict 모드에서 오류를 반환합니다. 이 동작은 JSON_QUERY 함수의 동작과 비슷합니다.

예를 들어 다음 쿼리는 배열 요소를 반환하고 형식을 지정합니다.

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "Order": {  
      "Number":"SO43659",  
      "Date":"2011-05-31T00:00:00"  
    },  
    "AccountNumber":"AW29825",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":1  
    }  
  },  
  {  
    "Order": {  
      "Number":"SO43661",  
      "Date":"2011-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',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

결과

반환 값

OPENJSON 함수가 반환하는 열은 WITH 옵션에 따라 달라집니다.

1. 기본 스키마를 사용하여 OPENJSON을 호출하면(즉, WITH 절에 명시적 스키마를 지정하지 않으면) 이 함수는 다음 열이 있는 테이블을 반환합니다.

   1. Key. 지정된 속성의 이름 또는 지정된 배열의 요소 인덱스를 포함하는 nvarchar(4000) 값입니다. 키 열에는 BIN2 데이터 정렬이 있습니다.

   2. 값. 속성 값을 포함하는 nvarchar(max) 값입니다. 값 열은 jsonExpression에서 해당 데이터 정렬을 상속받습니다.

   3. Type. 값의 형식을 포함하는 int 값입니다. OPENJSON을 기본 스키마와 함께 사용하는 경우에만 Type 열이 반환됩니다. Type 열은 다음 값 중 하나를 가집니다.

      Type 열의 값	JSON 데이터 형식
      0	null
      1	문자열
      2	number
      3	true/false
      4	array
      5	object

   첫 번째 수준 속성만 반환됩니다. JSON 텍스트의 형식이 올바르지 않으면 문이 실패합니다.

2. OPENJSON을 호출하고 WITH 절에 명시적 스키마를 지정하면, 함수는 WITH 절에 정의한 스키마가 있는 테이블을 반환합니다.

설명

OPENJSON의 두 번째 인수 또는 with_clause에 사용된 json_path는 lax 또는 strict 키워드로 시작될 수 있습니다.

• lax 모드에서 지정된 경로의 개체 또는 값을 찾을 수 없는 경우 OPENJSON에서 오류를 반환하지 않습니다. 경로를 찾을 수 없는 경우 OPENJSON은 빈 결과 집합 또는 NULL 값을 반환합니다.
• strict 모드에서 경로를 찾을 수 없는 경우 OPENJSON은 오류를 반환합니다.

이 페이지의 예제 중 일부는 경로 모드인 lax 또는 strict를 명시적으로 지정합니다. 경로 모드는 선택적입니다. 경로 모드를 명시적으로 지정하지 않으면 lax 모드가 기본값입니다. 경로 모드 및 경로 식에 대한 자세한 내용은 JSON 경로 식(SQL Server)을 참조하세요.

with_clause의 열 이름은 JSON 텍스트의 키와 일치합니다. 열 이름 [Address.Country]를 지정하면 키 Address.Country와 일치합니다. 개체 Address 내에서 중첩된 키 Country를 참조하려면 열 경로에 경로 $.Address.Country를 지정해야 합니다.

json_path에는 영숫자가 포함된 키가 포함될 수 있습니다. 키에 특수 문자가 있으면 json_path에서 키 이름을 큰따옴표로 이스케이프합니다. 예를 들어 $."my key $1".regularKey."key with . dot"은 다음 JSON 텍스트에서 값 1과 일치합니다.

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

예제

예 1 - JSON 배열을 임시 테이블로 변환

다음 예에서는 JSON 숫자 배열로 식별자 목록을 제공합니다. 이 쿼리는 JSON 배열을 식별자 테이블로 변환하고 지정된 ID를 사용하여 모든 제품을 필터링합니다.

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

이 쿼리는 다음 예와 동일합니다. 그러나 아래 예에서는 숫자를 매개 변수로 전달하는 대신 쿼리에 포함해야 합니다.

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

예 2 - 두 JSON 개체의 속성 병합

다음 예에서는 두 JSON 개체의 모든 속성의 합집합을 선택합니다. 두 개체에는 중복된 name 속성이 있습니다. 이 예에서는 키 값을 사용하여 중복 행을 결과에서 제외합니다.

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

예 3 - CROSS APPLY를 사용하여 테이블 셀에 저장된 JSON 데이터와 행 조인

다음 예에서 SalesOrderHeader 테이블에는 JSON 형식의 SalesOrderReasons 배열을 포함하는 SalesReason 텍스트 열이 있습니다. SalesOrderReasons 개체에는 Quality 및 Manufacturer와 같은 속성이 포함됩니다. 이 예에서는 모든 판매 주문 행을 관련된 판매 이유에 조인하는 보고서를 만듭니다. OPENJSON 연산자는 이유가 별도의 자식 테이블에 저장된 것처럼 JSON 판매 이유 배열을 확장합니다. 그런 다음, CROSS APPLY 연산자가 각 판매 주문 행을 OPENJSON 테이블 반환 함수가 반환한 행에 조인합니다.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

반환할 행의 명시적으로 정의된 스키마와 OPENJSON을 사용하여 동일한 쿼리를 다시 작성할 수 있습니다.

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

이 예에서 $ 경로는 배열에 있는 각 요소를 참조합니다. 반환된 값을 명시적으로 캐스팅하려는 경우 이러한 형식의 쿼리를 사용할 수 있습니다.

예 4 - CROSS APPLY를 사용하여 관계형 행과 JSON 요소 결합

다음 쿼리는 관계형 행과 JSON 요소를 다음 테이블에 나와 있는 결과에 결합합니다.

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street VARCHAR(500) ,  postcode VARCHAR(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

결과

예 5 - SQL Server로 JSON 데이터 가져오기

다음 예에는 전체 JSON 개체를 SQL Server 테이블에 로드합니다.

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  
   
  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id INT,  
        firstName NVARCHAR(50), lastName NVARCHAR(50),   
        isAlive BIT, age INT,  
        dateOfBirth DATETIME, spouse NVARCHAR(50))

예 6 - JSON 콘텐츠를 사용하는 간단한 예제

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

참고 항목

• JSON 경로 식(SQL Server)
• OPENJSON을 사용하여 JSON 데이터를 행과 열로 변환(SQL Server)
• 기본 스키마와 함께 OPENJSON 사용(SQL Server)
• 명시적 스키마와 함께 JSON 사용(SQL Server)