OPENJSON을 사용하여 JSON 데이터를 행과 열로 변환(SQL Server)Convert JSON Data to Rows and Columns with OPENJSON (SQL Server)

이 항목은 다음에 적용됩니다. 예SQL Server(2016부터)예Azure SQL Database아니요Azure SQL Data Warehouse아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

OPENJSON 행 집합 함수는 JSON 텍스트를 일련의 행과 열로 변환합니다.The OPENJSON rowset function converts JSON text into a set of rows and columns. OPENJSON을 사용하여 JSON 컬렉션을 행 집합으로 변환한 후 반환된 데이터에 대해 SQL 쿼리를 실행하거나 SQL Server 테이블에 삽입할 수 있습니다.After you transform a JSON collection into a rowset with OPENJSON, you can run any SQL query on the returned data or insert it into a SQL Server table.

OPENJSON 함수는 단일 JSON 개체 또는 JSON 개체 컬렉션을 받아서 하나 또는 여러 개의 행으로 변환합니다.The OPENJSON function takes a single JSON object or a collection of JSON objects and transforms them into one or more rows. 기본적으로 OPENJSON 함수는 다음 데이터를 반환합니다.By default, the OPENJSON function returns the following data:

  • JSON 개체에서 이 함수는 첫 번째 수준에서 발견된 모든 키:값 쌍을 반환합니다.From a JSON object, the function returns all the key:value pairs that it finds at the first level.
  • JSON 배열에서 이 함수는 모든 요소 및 해당 인덱스를 반환합니다.From a JSON array, the function returns all the elements of the array with their indexes.

선택적 WITH 절을 추가하여 출력의 구조를 명시적으로 정의하는 스키마를 제공할 수 있습니다.You can add an optional WITH clause to provide a schema that explicitly defines the structure of the output.

옵션 1 - 기본 출력을 사용하는 OPENJSONOption 1 - OPENJSON with the default output

결과에 대한 명시적 스키마를 제공하지 않고, 즉 OPENJSON 뒤에 WITH 절 없이 OPENJSON 함수를 사용하는 경우 함수에서 다음 세 개의 열이 있는 테이블을 반환합니다.When you use the OPENJSON function without providing an explicit schema for the results - that is, without a WITH clause after OPENJSON - the function returns a table with the following three columns:

  1. 입력 개체의 속성 이름(또는 입력 배열의 요소 인덱스)The name of the property in the input object (or the index of the element in the input array).
  2. 속성 또는 배열 요소의 The value of the property or the array element.
  3. 형식(예: 문자열, 숫자, 부울, 배열 또는 개체)The type (for example, string, number, boolean, array, or object).

OPENJSON은 JSON 개체의 각 속성 또는 배열의 각 요소를 개별 행으로 반환합니다.OPENJSON returns each property of the JSON object, or each element of the array, as a separate row.

다음은 기본 스키마와 함께 즉, 선택적 WITH 절 없이 OPENJSON을 사용하고 JSON 개체의 각 속성에 대해 행 한 개를 반환하는 간단한 예제입니다.Here's a quick example that uses OPENJSON with the default schema - that is, without the optional WITH clause - and returns one row for each property of the JSON object.

예제Example

DECLARE @json NVARCHAR(MAX)

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

SELECT *
FROM OPENJSON(@json);

결과Results

Keykey valuevalue 유형type
namename JohnJohn 11
surname DoeDoe 11
나이age 4545 22
기술skills ["SQL","C#","MVC"]["SQL","C#","MVC"] 44

기본 스키마를 사용하는 OPENJSON에 대한 추가 정보More info about OPENJSON with the default schema

자세한 내용과 예제를 보려면 기본 스키마와 함께 OPENJSON 사용(SQL Server)을 참조하세요.For more info and examples, see Use OPENJSON with the Default Schema (SQL Server).

구문 및 사용법은 OPENJSON(Transact-SQL)에서만 사용할 수 있습니다.For syntax and usage, see OPENJSON (Transact-SQL).

옵션 2 - 명시적 구조를 사용하는 OPENJSONOption 2 - OPENJSON output with an explicit structure

OPENJSON 함수의 WITH 절을 사용하여 결과의 스키마를 지정하면 함수에서 WITH 절에 정의한 열만 포함된 테이블이 반환됩니다.When you specify a schema for the results by using the WITH clause of the OPENJSON function, the function returns a table with only the columns that you define in the WITH clause. 선택적 WITH 절에 출력 열 집합, 해당 형식 및 각 출력 값에 대한 JSON 원본 속성의 경로를 지정합니다.In the optional WITH clause, you specify a set of output columns, their types, and the paths of the JSON source properties for each output value. OPENJSON은 JSON 개체 배열을 반복하고, 각 열에 대해 지정된 경로에서 값을 읽고 지정된 형식으로 변환합니다.OPENJSON iterates through the array of JSON objects, reads the value on the specified path for each column, and converts the value to the specified type.

다음은 WITH 절에 명시적으로 지정한 출력의 스키마와 함께 OPENJSON을 사용하는 간단한 예제입니다.Here's a quick example that uses OPENJSON with a schema for the output that you explicitly specify in the WITH clause.

예제Example

DECLARE @json NVARCHAR(MAX)
SET @json =   
  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'  
 ) 

결과Results

NumberNumber 날짜Date CustomerCustomer 수량Quantity
SO43659SO43659 2011-05-31T00:00:002011-05-31T00:00:00 AW29825AW29825 11
SO43661SO43661 2011-06-01T00:00:002011-06-01T00:00:00 AW73565AW73565 33

이 함수는 JSON 배열의 요소를 반환하고 형식을 지정합니다.This function returns and formats the elements of a JSON array.

  • OPENJSON 은 출력 테이블에 JSON 배열의 각 요소에 대한 새 행을 생성합니다.For each element in the JSON array, OPENJSON generates a new row in the output table. JSON 배열의 두 요소는 반환된 테이블에서 두 개의 행으로 변환됩니다.The two elements in the JSON array are converted into two rows in the returned table.

  • colName type json_path 구문을 사용하여 지정된 각 열에 대해 OPENJSON은 지정된 경로의 각 배열 요소에 있는 값을 지정된 형식으로 변환합니다.For each column, specified by using the colName type json_path syntax, OPENJSON converts the value found in each array element on the specified path to the specified type. 이 예제에서는 $.Order.Date 경로의 각 요소에서 Date 열의 값을 가져와 datetime 값으로 변환합니다.In this example, values for the Date column are taken from each element on the path $.Order.Date and converted to datetime values.

명시적 스키마를 사용하는 OPENJSON에 대한 추가 정보More info about OPENJSON with an explicit schema

자세한 내용과 예제를 보려면 명시적 스키마와 함께 OPENJSON 사용(SQL Server)을 참조하세요.For more info and examples, see Use OPENJSON with an Explicit Schema (SQL Server).

구문 및 사용법은 OPENJSON(Transact-SQL)에서만 사용할 수 있습니다.For syntax and usage, see OPENJSON (Transact-SQL).

OPENJSON은 호환성 수준 130을 필요로 함OPENJSON requires Compatibility Level 130

OPENJSON 함수는 호환성 수준 130에서만 사용할 수 있습니다.The OPENJSON function is available only under compatibility level 130. 데이터베이스 호환성 수준이 130보다 낮으면 SQL Server에서 OPENJSON 함수를 찾아 실행할 수 없습니다.If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function. 다른 기본 제공 JSON 함수는 모든 호환성 수준에서 사용할 수 있습니다.Other built-in JSON functions are available at all compatibility levels.

sys.databases 뷰 또는 데이터베이스 속성에서 호환성 수준을 확인할 수 있습니다.You can check compatibility level in the sys.databases view or in database properties.

다음 명령을 사용하여 데이터베이스의 호환성 수준을 변경할 수 있습니다.You can change the compatibility level of a database by using the following command:
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

SQL Server의 기본 제공 JSON 지원에 대한 자세한 정보Learn more about the built-in JSON support in SQL Server

많은 특정 솔루션, 사용 사례 및 권장 사항은 Microsoft 프로그램 관리자인 Jovan Popovic이 제공하는 SQL Server 및 Azure SQL Database의 기본 제공 JSON 지원에 대한 블로그 게시물을 참조하세요.For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.

관련 항목:See Also

OPENJSON(Transact-SQL)OPENJSON (Transact-SQL)