OPENJSON (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later versions Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL analytics endpoint in Microsoft FabricWarehouse in Microsoft FabricSQL database in Microsoft Fabric

테이블 반환 함수는 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 ] )

The OPENJSON table-valued function parses the jsonExpression provided as the first argument and returns one or more rows containing data from the JSON objects in the expression. jsonExpression can contain nested sub-objects. If you want to parse a sub-object from within jsonExpression, you can specify a path parameter for the JSON sub-object.

openjson

By default, the OPENJSON table-valued function returns three columns, which contain the key name, the value, and the type of each key:value pair found in jsonExpression. As an alternative, you can explicitly specify the schema of the result set that OPENJSON returns by providing with_clause.

with_clause

The with_clause contains a list of columns with their types for OPENJSON to return. By default, OPENJSON matches keys in jsonExpression with the column names in with_clause (in this case, matches keys implies that it is case sensitive). If a column name does not match a key name, you can provide an optional column_path, which is a JSON Path Expressions that references a key within the jsonExpression.

Arguments

jsonExpression

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

OPENJSON은 JSON 식에 있는 배열의 요소 또는 개체의 속성을 반복하고 각 요소 또는 속성마다 하나의 행을 반환합니다. The following example returns each property of the object provided as 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

Is an optional JSON path expression that references an object or an array within jsonExpression. OPENJSON 는 지정된 위치에 있는 JSON 텍스트로 이동하고 참조된 조각만 구문 분석합니다. 자세한 내용은 JSON 경로 식을 참조하세요.

You can provide a variable as the value of path. (SQL Server 2016(13.x) 및 이전 버전에서는 지원되지 않습니다.)

The following example returns a nested object by specifying the 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"')

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 명시적으로 정의합니다. The optional with_clause can contain the following elements:

colName

출력 열의 이름입니다.

기본적으로 OPENJSON 열의 이름을 사용하여 JSON 텍스트의 속성과 일치합니다. 예를 들어 스키마 OPENJSON 에서 열을 name 지정하는 경우 이 열을 JSON 텍스트의 속성 "name"으로 채우려고 합니다. You can override this default mapping by using the column_path argument.

type

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

column_path

지정된 열에서 반환할 속성을 지정하는 JSON 경로입니다. For more info, see the description of the path parameter previously in this topic.

Use column_path to override default mapping rules when the name of an output column doesn't match the name of the property.

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

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

AS JSON

열 정의의 AS JSON 옵션을 사용하여 참조된 속성에 내부 JSON 개체 또는 배열이 포함되도록 지정합니다. If you specify the AS JSON option, the type of the column must be nvarchar(MAX).

• If you don't specify AS JSON for a column, the function returns a scalar value (for example, int, string, true, false) from the specified JSON property on the specified path. 경로가 개체 또는 배열을 나타내고 지정된 경로에서 속성을 찾을 수 없는 경우 함수는 모드에서 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  
 )

Results

Return value

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

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

  • Key. An nvarchar(4000) value that contains the name of the specified property or the index of the element in the specified array. key 열에는 BIN2 데이터 정렬이 있습니다.

  • Value. An nvarchar(MAX) value that contains the value of the property. The value column inherits its collation from jsonExpression.

  • Type. An int value that contains the type of the value. 열은 Type 기본 스키마와 함께 사용하는 OPENJSON 경우에만 반환됩니다. 열에는 type 다음 값 중 하나가 있습니다.

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

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

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

Remarks

json_path used in the second argument of OPENJSON or in with_clause can start with the lax or strict keyword.

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

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

Column names in with_clause are matched with keys in the JSON text. 열 이름 [Address.Country]를 지정하면 키 Address.Country와 일치합니다. 개체 Country 내에서 중첩된 키 Address를 참조하려면 열 경로에 경로 $.Address.Country를 지정해야 합니다.

json_path can contain keys with alphanumeric characters. Escape the key name in json_path with double quotes if you have special characters in the keys. 예를 들어 $."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 개체의 모든 속성의 합집합을 선택합니다. The two objects have a duplicate name property. 이 예에서는 키 값을 사용하여 중복 행을 결과에서 제외합니다.

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 텍스트 열이 있습니다. The SalesOrderReasons objects contain properties like Quality and 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

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 사용