OPENJSON (Transact-SQL)

적용 대상: SQL Server 2016(13.x) 이상 버전Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric의 SQL 분석 엔드포인트Microsoft Fabric의 WarehouseMicrosoft Fabric Preview의 SQL Database

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

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

Transact-SQL 구문 규칙

Syntax

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

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

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

openjson

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

with_clause

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

Arguments

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 2016(13.x) 및 이전 버전에서는 지원되지 않습니다.)

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

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"')

Results

JSON 배열을 구문 분석할 때 OPENJSON 함수는 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

Results

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

with_clause

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

colName

출력 열의 이름입니다.

기본적으로 OPENJSON 열의 이름을 사용하여 JSON 텍스트의 속성과 일치합니다. 예를 들어 스키마 name 에서 열을 OPENJSON 지정하는 경우 이 열을 JSON 텍스트의 속성 "name"으로 채우려고 합니다. column_path 인수를 사용하여 이 기본 매핑을 재정의할 수 있습니다.

type

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

column_path

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

출력 열의 이름이 속성 이름과 일치하지 않는 경우 column_path 사용하여 기본 매핑 규칙을 재정의합니다.

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

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

AS JSON

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

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

• 열에 대해 지정 AS JSON 하는 경우 함수는 지정된 경로의 지정된 JSON 속성에서 JSON 조각을 반환합니다. 경로가 스칼라 값을 나타내고 지정된 경로에서 속성을 찾을 수 없는 경우 함수는 모드로 NULL 반환 lax 되거나 모드에서 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  
 )

Results

Return value

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

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

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

  • Value. 속성의 값을 포함하는 nvarchar(MAX) 값입니다. 열은 valuejsonExpression에서 데이터 정렬을 상속합니다.

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

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

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

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

Remarks

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

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

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

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

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

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

Examples

예 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 개체의 모든 속성의 합집합을 선택합니다. 두 개체에는 중복된 이름 속성이 있습니다. 이 예에서는 키 값을 사용하여 중복 행을 결과에서 제외합니다.

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 형식의 SalesReason 배열을 포함하는 SalesOrderReasons 텍스트 열이 있습니다. 개체에는 SalesOrderReasons품질 및 제조업체와 같은 속성 이 포함됩니다. 이 예에서는 모든 판매 주문 행을 관련된 판매 이유에 조인하는 보고서를 만듭니다. 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

Results

예 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

Related content

• JSON 경로 식
• OPENJSON을 사용하여 JSON 데이터 구문 분석 및 변환
• 기본 스키마와 함께 OPENJSON 사용
• 명시적 스키마와 함께 OPENJSON 사용