SQL Server의 JSON 데이터JSON data in 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

JSON은 최신 웹 및 모바일 응용 프로그램에서 데이터를 교환하는 데 사용되는 일반적인 텍스트 데이터 형식입니다.JSON is a popular textual data format that's used for exchanging data in modern web and mobile applications. JSON은 로그 파일 또는 Microsoft Azure Cosmos DB와 같은 NoSQL 데이터베이스에 구조화되지 않은 데이터를 저장하는 데에도 사용됩니다.JSON is also used for storing unstructured data in log files or NoSQL databases such as Microsoft Azure Cosmos DB. 많은 REST 웹 서비스에서 JSON 텍스트로 형식이 지정된 결과를 반환하거나 JSON으로 형식이 지정된 데이터를 허용합니다.Many REST web services return results that are formatted as JSON text or accept data that's formatted as JSON. 예를 들어 Azure Search, Azure Storage, Azure Cosmos DB 등 대부분의 Azure 서비스에는 JSON을 반환하거나 사용하는 REST 끝점이 있습니다.For example, most Azure services, such as Azure Search, Azure Storage, and Azure Cosmos DB, have REST endpoints that return or consume JSON. 또한 JSON은 AJAX 호출을 사용하여 웹 페이지와 웹 서버 간에 데이터를 교환하는 기본 형식입니다.JSON is also the main format for exchanging data between webpages and web servers by using AJAX calls.

SQL Server의 JSON 함수를 사용하면 NoSQL과 관계형 개념을 동일한 데이터베이스에서 결합할 수 있습니다.JSON functions in SQL Server enable you to combine NoSQL and relational concepts in the same database. 이제 기본 관계형 열을 동일한 테이블에서 JSON 텍스트로 형식이 지정된 문서를 포함하는 열과 결합하여 관계형 구조로 JSON 문서를 구문 분석하고 가져오거나 관계형 데이터를 JSON 텍스트로 형식을 지정할 수 있습니다.Now you can combine classic relational columns with columns that contain documents formatted as JSON text in the same table, parse and import JSON documents in relational structures, or format relational data to JSON text. 다음 비디오에서 JSON 함수가 관계형 및 NoSQL 개념을 SQL Server 및 Azure SQL 데이터베이스에서 연결하는 방법을 볼 수 있습니다.You see see how JSON functions connect relational and NoSQL concepts in SQL Server and Azure SQL Database in the following video:

NoSQL과 관계형 간에 브리지인 JSONJSON as a bridge between NoSQL and relational worlds

JSON 텍스트의 예는 다음과 같습니다.Here's an example of JSON text:

[{
    "name": "John",
    "skills": ["SQL", "C#", "Azure"]
}, {
    "name": "Jane",
    "surname": "Doe"
}]

SQL Server 기본 제공 함수 및 연산자를 사용하여 JSON 텍스트로 다음 작업을 수행할 수 있습니다.By using SQL Server built-in functions and operators, you can do the following things with JSON text:

  • JSON 텍스트를 구문 분석하고 값을 읽거나 수정합니다.Parse JSON text and read or modify values.
  • JSON 개체의 배열을 테이블 형식으로 변환합니다.Transform arrays of JSON objects into table format.
  • 변환된 JSON 개체에서 모든 Transact-SQL 쿼리를 실행합니다.Run any Transact-SQL query on the converted JSON objects.
  • JSON 형식으로 Transact-SQL 쿼리의 결과 서식을 지정합니다.Format the results of Transact-SQL queries in JSON format.

기본 제공 JSON 지원 개요Overview of built-in JSON support

SQL Server 및 SQL Database의 주요 JSON 기능Key JSON capabilities of SQL Server and SQL Database

다음 섹션에서는 SQL Server에서 기본 제공 JSON 지원을 통해 제공하는 주요 기능을 설명합니다.The next sections discuss the key capabilities that SQL Server provides with its built-in JSON support. 다음 동영상에서 JSON 함수 및 연산자를 사용하는 방법을 볼 수 있습니다.You can see how to use JSON functions and operators in the following video:

SQL Server 2016 및 JSON 지원SQL Server 2016 and JSON Support

JSON 텍스트에서 값을 추출하여 쿼리에서 사용Extract values from JSON text and use them in queries

데이터베이스 테이블에 저장된 JSON 텍스트가 있는 경우 다음과 같은 기본 제공 함수를 사용하여 JSON 텍스트의 값을 읽거나 수정할 수 있습니다.If you have JSON text that's stored in database tables, you can read or modify values in the JSON text by using the following built-in functions:

예제Example

다음 예제에서 쿼리는 테이블의 관계형 및 JSON 데이터(jsonCol 열에 저장됨)를 사용합니다.In the following example, the query uses both relational and JSON data (stored in a column named jsonCol) from a table:

SELECT Name,Surname,
 JSON_VALUE(jsonCol,'$.info.address.PostCode') AS PostCode,
 JSON_VALUE(jsonCol,'$.info.address."Address Line 1"')+' '
  +JSON_VALUE(jsonCol,'$.info.address."Address Line 2"') AS Address,
 JSON_QUERY(jsonCol,'$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol)>0
 AND JSON_VALUE(jsonCol,'$.info.address.Town')='Belgrade'
 AND Status='Active'
ORDER BY JSON_VALUE(jsonCol,'$.info.address.PostCode')

응용 프로그램 및 도구는 스칼라 테이블 열에서 가져온 값과 JSON 열에서 가져온 값 간의 차이점을 표시하지 않습니다.Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. Transact-SQL 쿼리의 모든 부분(WHERE, ORDER BY, GROUP BY 절, 창 집계 등 포함)에서 JSON 텍스트 값을 사용할 수 있습니다.You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). JSON 함수는 JavaScript 형식의 구문을 사용하여 JSON 텍스트 내의 값을 참조합니다.JSON functions use JavaScript-like syntax for referencing values inside JSON text.

자세한 내용은 기본 함수를 사용하여 JSON 데이터 유효성 검사, 쿼리, 변경(SQL Server), JSON_VALUE(Transact-SQL)JSON_QUERY(Transact-SQL)를 참조하세요.For more information, see Validate, query, and change JSON data with built-in functions (SQL Server), JSON_VALUE (Transact-SQL), and JSON_QUERY (Transact-SQL).

JSON 값 변경Change JSON values

JSON 텍스트의 일부를 수정해야 하는 경우 JSON_MODIFY(Transact-SQL) 함수를 사용하여 JSON 문자열에서 속성 값을 업데이트하고 업데이트된 JSON 문자열을 반환할 수 있습니다.If you must modify parts of JSON text, you can use the JSON_MODIFY (Transact-SQL) function to update the value of a property in a JSON string and return the updated JSON string. 다음 예제에서는 JSON이 포함된 변수에서 속성 값을 업데이트합니다.The following example updates the value of a property in a variable that contains JSON:

DECLARE @json NVARCHAR(MAX);
SET @json = '{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}';
SET @json = JSON_MODIFY(@jsonInfo,'$.info.address[1].town','London');
SELECT modifiedJson = @json;

결과Results

modifiedJsonmodifiedJson
{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}

JSON 컬렉션을 행 집합으로 변환Convert JSON collections to a rowset

SQL Server의 JSON 쿼리에는 사용자 지정 쿼리 언어가 필요 없습니다.You don't need a custom query language to query JSON in SQL Server. JSON 데이터를 쿼리하려면 표준 T-SQL을 사용하면 됩니다.To query JSON data, you can use standard T-SQL. JSON 데이터에 대한 쿼리 또는 보고서를 만들어야 하는 경우 OPENJSON 행 집합 함수를 호출하여 JSON 데이터를 행 및 열로 쉽게 변환할 수 있습니다.If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. 자세한 내용은 OPENJSON을 사용하여 JSON 데이터를 행과 열로 변환(SQL Server)을 참조하세요.For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

다음 예제에서는 OPENJSON을 호출하고 @json 변수에 저장된 개체 배열을 표준 SQL SELECT 문을 사용하여 쿼리할 수 있는 행 집합으로 변환합니다.The following example calls OPENJSON and transforms the array of objects that is stored in the @json variable to a rowset that can be queried with a standard SQL SELECT statement:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob')  

결과Results

idid firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth
22 JohnJohn SmithSmith 2525
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00

OPENJSON은 JSON 개체 배열을 테이블로 변환합니다. 각 개체는 하나의 행으로 표현되고 키/값 쌍은 셀로 반환됩니다.OPENJSON transforms the array of JSON objects into a table in which each object is represented as one row, and key/value pairs are returned as cells. 출력은 다음 규칙을 준수합니다.The output observes the following rules:

  • OPENJSON은 JSON 값을 WITH 절에 지정된 형식으로 변환합니다.OPENJSON converts JSON values to the types that are specified in the WITH clause.
  • OPENJSON 은 기본 키:값 쌍과 계층적으로 구성된 중첩된 개체를 모두 처리할 수 있습니다.OPENJSON can handle both flat key/value pairs and nested, hierarchically organized objects.
  • JSON 텍스트에 포함된 모든 필드를 반환할 필요가 없습니다.You don't have to return all the fields that are contained in the JSON text.
  • OPENJSON 은 JSON 값이 없을 경우 NULL 값을 반환합니다.If JSON values don't exist, OPENJSON returns NULL values.
  • 중첩된 속성을 참조하거나 다른 이름의 속성을 참조하려면 형식 지정 후 필요에 따라 경로를 지정할 수 있습니다.You can optionally specify a path after the type specification to reference a nested property or to reference a property by a different name.
  • 경로에서 선택적 strict 접두사는 지정된 속성의 값이 JSON 텍스트에 있어야 하도록 지정합니다.The optional strict prefix in the path specifies that values for the specified properties must exist in the JSON text.

자세한 내용은 OPENJSON을 사용하여 JSON 데이터를 행과 열로 변환(SQL Server)OPENJSON(Transact-SQL)을 참조하세요.For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server) and OPENJSON (Transact-SQL).

JSON 문서에는 표준 관계형 열에 직접 매핑할 수 없는 하위 요소 및 계층적 데이터가 있을 수 있습니다.JSON documents may have sub-elements and hierarchical data that cannot be directly mapped into the standard relational columns. 이 경우 하위 배열과 부모 엔터티를 조인하여 JSON 계층 구조를 평면화할 수 있습니다.In this case, you can flatten JSON hierarchy by joining parent entity with sub-arrays.

다음 예제에서 배열의 두 번째 개체에는 사람 기술을 나타내는 하위 배열이 있습니다.In the following example, the second object in the array has sub-array representing person skills. 모든 하위 개체는 추가 OPENJSON 함수 호출을 사용하여 구문 분석할 수 있습니다.Every sub-object can be parsed using additional OPENJSON function call:

DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[  
       { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 },  
       { "id" : 5,"info": { "name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"] }, "dob": "2005-11-04T12:00:00" }  
 ]'  

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.id',  
        firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
        age int, dateOfBirth datetime2 '$.dob',
    skills nvarchar(max) '$.skills' as json) 
    outer apply openjson( a.skills ) 
                     with ( skill nvarchar(8) '$' ) as b

기술 배열은 첫 번째 OPENJSON에서 원래 JSON 텍스트 조각으로 반환되고 APPLY 연산자를 사용하여 다른 OPENJSON 함수로 전달됩니다.skills array is returned in the first OPENJSON as original JSON text fragment and passed to another OPENJSON function using APPLY operator. 두 번째 OPENJSON 함수는 JSON 배열을 구분 분석하고 문자열 값을 첫 번째 OPENJSON의 결과와 조인될 단일 열 행 집합으로 반환합니다.The second OPENJSON function will parse JSON array and return string values as single column rowset that will be joined with the result of the first OPENJSON. 이 쿼리의 결과는 다음 표에 표시됩니다.The result of this query is shown in the following table:

결과Results

idid firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth skillskill
22 JohnJohn SmithSmith 2525
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00 SQLSQL
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00 C#C#
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00 AzureAzure

OUTER APPLY OPENJSON은 하위 배열과 첫 번째 수준 엔터티를 조인하고, 결과 집합을 평면화합니다.OUTER APPLY OPENJSON will join first level entity with sub-array and return flatten resultset. JOIN으로 인해 모든 기술에 대해 두 번째 행이 반복됩니다.Due to JOIN, the second row will be repeated for every skill.

SQL Server 데이터를 JSON으로 변환하거나 JSON 내보내기Convert SQL Server data to JSON or export JSON

SELECT 문에 FOR JSON 절을 추가하여 SQL Server 데이터 또는 SQL 쿼리 결과를 JSON으로 서식 지정합니다.Format SQL Server data or the results of SQL queries as JSON by adding the FOR JSON clause to a SELECT statement. FOR JSON을 사용하여 JSON 출력의 형식을 클라이언트 응용 프로그램에서 SQL Server로 위임합니다.Use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server. 자세한 내용은 FOR JSON을 사용하여 쿼리 결과 서식을 JSON으로 지정(SQL Server)을 참조하세요.For more information, see Format Query Results as JSON with FOR JSON (SQL Server).

다음 예제에서는 FOR JSON 절에서 PATH 모드를 사용합니다.The following example uses PATH mode with the FOR JSON clause:

SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob  
FROM People  
FOR JSON PATH  

SELECT 절은 JSON을 이해하는 모든 응용 프로그램에 제공될 수 있는 JSON 텍스트로 SQL 결과의 형식을 지정합니다.The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands JSON. PATH 옵션은 SELECT 절에서 점으로 구분된 별칭을 사용하여 쿼리 결과에 개체를 중첩합니다.The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.

결과Results

[{
    "id": 2,
    "info": {
        "name": "John",
        "surname": "Smith"
    },
    "age": 25
}, {
    "id": 5,
    "info": {
        "name": "Jane",
        "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
}] 

자세한 내용은 FOR JSON을 사용하여 쿼리 결과 서식을 JSON으로 지정(SQL Server)FOR 절(Transact-SQL)을 참조하세요.For more information, see Format query results as JSON with FOR JSON (SQL Server) and FOR Clause (Transact-SQL).

SQL Server에서 JSON 데이터에 대한 사용 사례Use cases for JSON data in SQL Server

SQL Server 및 Azure SQL Database의 JSON 지원을 통해 관계형 및 NoSQL 개념을 결합할 수 있습니다.JSON support in SQL Server and Azure SQL Database lets you combine relational and NoSQL concepts. 관계형을 반구조화된 데이터로 쉽게 변환하거나 반대로 변환할 수 있습니다.You can easily transform relational to semi-structured data and vice-versa. 하지만 JSON은 기존 관계형 모델에 대한 대체입니다.JSON is not a replacement for existing relational models, however. SQL Server와 SQL Database의 JSON 지원을 활용하는 몇 가지 특정 사용 사례는 다음과 같습니다.Here are some specific use cases that benefit from the JSON support in SQL Server and in SQL Database. 자세한 내용은 SQL Server의 JSON – 사용 사례를 참조하세요.For more info, see JSON in SQL Server – Use cases.

복잡한 데이터 모델 간소화Simplify complex data models

여러 자식 테이블 대신 JSON 필드를 사용하여 데이터 모델을 비정규화하는 것이 좋습니다.Consider denormalizing your data model with JSON fields in place of multiple child tables. 자세한 내용은 비정규화된 모델을 사용하여 데이터 액세스 간소화를 참조하세요.For more info, see Simplify data access using de-normalized models.

소매 및 전자 상거래 데이터 저장Store retail and e-commerce data

유연성을 위해 정규화되지 않은 모델에서 다양한 범위의 변수 특성을 사용하여 제품에 대한 정보를 저장합니다.Store info about products with a wide range of variable attributes in a denormalized model for flexibility. 자세한 내용은 JSON을 사용하여 SQL Server에서 제품 카탈로그 디자인JSON 제품 카탈로그에서 데이터 인덱싱을 참조하세요.For more info, see Designing Product Catalogs in SQL Server using JSON and Indexing data in JSON product catalogs.

로그 및 원격 분석 데이터 처리Process log and telemetry data

Transact-SQL 언어의 모든 기능을 사용하여 JSON 파일로 저장된 로그 데이터를 로드, 쿼리 및 분석합니다.Load, query, and analyze log data stored as JSON files with all the power of the Transact-SQL language. 자세한 내용은 SQL Server의 JSON – 사용 사례에서 로그 및 원격 분석 데이터 분석 섹션을 참조하세요.For more info, see the section Log and telemetry data analysis in JSON in SQL Server – Use cases.

반구조화된 IoT 데이터 저장Store semi-structured IoT data

IoT 데이터의 실시간 분석이 필요할 때 들어오는 데이터를 저장소 위치에 준비하는 대신 데이터베이스에 직접 로드합니다.When you need real-time analysis of IoT data, load the incoming data directly into the database instead of staging it in a storage location. 자세한 내용은 Azure SQL Database에서 Azure IoT 데이터로 작업을 참조하세요.For more info, see Working with Azure IoT data in Azure SQL Database.

REST API 개발 간소화Simplify REST API development

데이터베이스의 관계형 데이터를 웹 사이트 지원 REST API에서 사용하는 JSON 형식으로 쉽게 변환합니다.Transform relational data from your database easily into the JSON format used by the REST APIs that support your web site. 자세한 내용은 SQL Server를 사용하여 최신 단일 페이지 앱에 대한 REST API 개발 간소화를 참조하세요.For more info, see Simplify REST API development for modern Single-page apps with SQL Server.

관계형 및 JSON 데이터 결합Combine relational and JSON data

SQL Server는 표준 Transact-SQL 언어를 사용하여 관계형 및 JSON 데이터를 저장하고 처리할 수 있는 하이브리드 모델을 제공합니다.SQL Server provides a hybrid model for storing and processing both relational and JSON data by using standard Transact-SQL language. JSON 문서 컬렉션을 테이블 단위로 구성하고, 둘 간의 관계를 설정하고, 테이블에 저장된 강력한 형식의 스칼라 열을 JSON 열에 저장된 유연한 키/값 쌍과 결합하고, 전체 Transact-SQL을 사용하여 하나 이상의 테이블에서 스칼라 값과 JSON 값을 모두 쿼리할 수 있습니다.You can organize collections of your JSON documents in tables, establish relationships between them, combine strongly typed scalar columns stored in tables with flexible key/value pairs stored in JSON columns, and query both scalar and JSON values in one or more tables by using full Transact-SQL.

JSON 텍스트는 varchar 또는 nvarchar 열에 저장되며 일반 텍스트 형식으로 인덱싱됩니다.JSON text is stored in varchar or nvarchar columns and is indexed as plain text. 텍스트를 지원하는 모든 SQL Server 기능 또는 구성 요소는 JSON을 지원하므로 JSON 및 기타 SQL Server 기능 간의 상호 작용에 제한이 거의 없습니다.Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features. JSON을 메모리 내 또는 임시 테이블에 저장할 수 있으며 행 수준 보안 조건자를 JSON 텍스트 등에 적용할 수 있습니다.You can store JSON in In-memory or Temporal tables, apply Row-Level Security predicates on JSON text, and so on.

JSON 문서를 처리하기 위해 사용자 지정된 쿼리 언어를 사용하려는 순수 JSON 워크로드가 있는 경우 Microsoft Azure Cosmos DB를 사용하는 것이 좋습니다.If you have pure JSON workloads where you want to use a query language that's customized for the processing of JSON documents, consider Microsoft Azure Cosmos DB.

다음은 SQL ServerSQL Server에서 기본 제공 JSON 지원을 사용하는 방법을 보여 주는 몇 가지 사용 사례입니다.Here are some use cases that show how you can use the built-in JSON support in SQL ServerSQL Server.

SQL Server에서 JSON 데이터 저장 및 인덱싱Store and index JSON data in SQL Server

JSON은 텍스트 형식이므로 JSON 문서는 SQL Database의 NVARCHAR 열에 저장될 수 있습니다.JSON is a textual format so the JSON documents are can stored in NVARCHAR columns in SQL Database. 모든 SQL Server 하위 시스템에서 NVARCHAR 형식이 지원되므로 CLUSTERED COLUMNSTORE 인덱스, 메모리 최적화 테이블 또는 OPENROWSET 또는 Polybase를 사용하여 읽을 수 있는 외부 파일이 있는 테이블에 JSON 문서를 넣을 수 있습니다.Since NVARCHAR type is supported in all SQL Server sub-systems you can put JSON documents in tables with CLUSTERED COLUMNSTORE indexes, memory optimized tables, or external files that can be read using OPENROWSET or Polybase.

SQL Server에서 JSON 데이터를 저장, 인덱싱 및 최적화하는 옵션에 대해 자세히 알아보려면 다음 문서를 참조하세요.To learn more about your options for storing, indexing, and optimizing JSON data in SQL Server, see the following articles:

SQL Server로 JSON 파일 로드Load JSON files into SQL Server

표준 JSON 또는 행으로 구분된 JSON으로 파일에 저장된 정보의 형식을 지정할 수 있습니다.You can format information that's stored in files as standard JSON or line-delimited JSON. SQL Server는 JSON 파일의 콘텐츠를 가져와 OPENJSON 또는 JSON_VALUE 함수를 사용하여 구문 분석하고 테이블에 로드할 수 있습니다.SQL Server can import the contents of JSON files, parse it by using the OPENJSON or JSON_VALUE functions, and load it into tables.

  • JSON 문서가 로컬 파일, 공유 네트워크 드라이브 또는 SQL Server에서 액세스할 수 있는 Azure Files 위치에 저장된 경우 대량 가져오기를 사용하여 JSON 데이터를 SQL Server로 로드할 수 있습니다.If your JSON documents are stored in local files, on shared network drives, or in Azure Files locations that can be accessed by SQL Server, you can use bulk import to load your JSON data into SQL Server. 이 시나리오에 대한 자세한 내용은 OPENROWSET를 사용하여 SQL Server로 JSON 파일 가져오기(BULK)를 참조하세요.For more information about this scenario, see Importing JSON files into SQL Server using OPENROWSET (BULK).

  • 행으로 구분된 JSON 파일이 Azure Blob Storage 또는 Hadoop 파일 시스템에 저장된 경우 PolyBase를 사용하여 JSON 텍스트를 로드하고 Transact-SQL 코드에서 구문 분석한 후 테이블에 로드할 수 있습니다.If your line-delimited JSON files are stored in Azure Blob storage or the Hadoop file system, you can use PolyBase to load JSON text, parse it in Transact-SQL code, and load it into tables.

SQL Server 테이블로 JSON 데이터 가져오기Import JSON data into SQL Server tables

외부 서비스에서 SQL Server로 JSON 데이터를 로드해야 하는 경우 응용 프로그램 계층에서 데이터를 구문 분석하는 대신 OPENJSON을 사용하여 데이터를 SQL Server로 가져올 수 있습니다.If you must load JSON data from an external service into SQL Server, you can use OPENJSON to import the data into SQL Server instead of parsing the data in the application layer.

DECLARE @jsonVariable NVARCHAR(MAX)

SET @jsonVariable = 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  
          }  
       }  
  ]'

INSERT INTO SalesReport  
SELECT SalesOrderJsonData.*  
FROM OPENJSON (@jsonVariable, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData;  

JSON 변수의 콘텐츠를 외부 REST 서비스에서 제공하거나, 클라이언트 쪽 JavaScript 프레임워크에서 매개 변수로 전송하거나, 외부 파일에서 로드할 수 있습니다.You can provide the content of the JSON variable by an external REST service, send it as a parameter from a client-side JavaScript framework, or load it from external files. JSON 텍스트의 결과를 SQL Server 테이블에 손쉽게 삽입, 업데이트 또는 병합할 수 있습니다.You can easily insert, update, or merge results from JSON text into a SQL Server table. 이 시나리오에 대한 자세한 내용은 다음 블로그 게시물을 참조하세요.For more information about this scenario, see the following blog posts:

SQL 쿼리를 사용하여 JSON 데이터 분석Analyze JSON data with SQL queries

보고를 위해 JSON 데이터를 필터링하거나 집계해야 하는 경우 OPENJSON을 사용하여 JSON을 관계형 형식으로 변환할 수 있습니다.If you must filter or aggregate JSON data for reporting purposes, you can use OPENJSON to transform JSON to relational format. 그런 다음, 표준 Transact-SQLTransact-SQL 및 기본 제공 함수를 사용하여 보고서를 작성할 수 있습니다.You can then use standard Transact-SQLTransact-SQL and built-in functions to prepare the reports.

SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date  
FROM   SalesOrderRecord AS Tab  
          CROSS APPLY  
     OPENJSON (Tab.json, N'$.Orders.OrdersArray')  
           WITH (  
              Number   varchar(200) N'$.Order.Number',   
              Date     datetime     N'$.Order.Date',  
              Customer varchar(200) N'$.AccountNumber',   
              Quantity int          N'$.Item.Quantity'  
           )  
  AS SalesOrderJsonData  
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'  
ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified  

같은 쿼리에서 표준 테이블 열과 JSON 텍스트의 값을 모두 사용할 수 있습니다.You can use both standard table columns and values from JSON text in the same query. JSON_VALUE(Tab.json, '$.Status') 식에서 인덱스를 추가하여 쿼리 성능을 개선할 수 있습니다.You can add indexes on the JSON_VALUE(Tab.json, '$.Status') expression to improve the performance of the query. 자세한 내용은 JSON 데이터 인덱싱를 참조하세요.For more information, see Index JSON data.

SQL Server 테이블에서 JSON으로 서식이 지정된 데이터 반환Return data from a SQL Server table formatted as JSON

데이터베이스 계층에서 데이터를 가져와서 JSON 형식으로 반환하는 웹 서비스가 있거나 JSON 형식의 데이터를 허용하는 JavaScript 프레임워크 또는 라이브러리가 있는 경우 SQL 쿼리에서 직접 JSON 출력의 형식을 지정할 수 있습니다.If you have a web service that takes data from the database layer and returns it in JSON format, or if you have JavaScript frameworks or libraries that accept data formatted as JSON, you can format JSON output directly in a SQL query. 테이블 형식 쿼리 결과와 직렬화 개체를 차례로 JSON 형식으로 변환하기 위해 코드를 작성하거나 라이브러리를 포함하는 대신, FOR JSON을 사용하여 JSON 형식 지정을 SQL Server에 위임할 수 있습니다.Instead of writing code or including a library to convert tabular query results and then serialize objects to JSON format, you can use FOR JSON to delegate the JSON formatting to SQL Server.

예를 들어 OData 사양을 준수하는 JSON 출력을 생성할 수 있습니다.For example, you might want to generate JSON output that's compliant with the OData specification. 웹 서비스에는 다음과 같은 형식의 요청 및 응답이 필요합니다.The web service expects a request and response in the following format:

  • 요청: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductNameRequest: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

  • 응답: {"@odata.context":"http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}Response: {"@odata.context":"http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}

다음 OData URL은 ProductID 및 ProductName 열에서 id가 1인 제품에 대한 요청을 나타냅니다.This OData URL represents a request for the ProductID and ProductName columns for the product with id 1. FOR JSON을 사용하여 SQL Server에 필요한 형식으로 출력 형식을 지정할 수 있습니다.You can use FOR JSON to format the output as expected in SQL Server.

SELECT 'http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity'
 AS '@odata.context',   
 ProductID, Name as ProductName   
FROM Production.Product  
WHERE ProductID = 1  
FOR JSON AUTO  

이 쿼리의 출력은 OData 사양을 완벽하게 준수하는 JSON 텍스트입니다. 형식 지정 및 이스케이프는 SQL Server에서 처리됩니다.The output of this query is JSON text that's fully compliant with the OData spec. Formatting and escaping are handled by SQL Server. SQL Server는 OData JSON 또는 GeoJSON과 같은 형식으로 쿼리 결과의 형식을 지정할 수 있습니다.SQL Server can also format query results in any format, such as OData JSON or GeoJSON. 자세한 내용은 GeoJSON 형식으로 공간 데이터 반환을 참조하세요.For more information, see Returning spatial data in GeoJSON format.

AdventureWorks 예제 데이터베이스를 사용하여 드라이브 기본 제공 JSON 지원 시험 사용Test drive built-in JSON support with the AdventureWorks sample database

AdventureWorks 샘플 데이터베이스를 가져오려면 Microsoft 다운로드 센터에서 데이터베이스 파일 및 샘플과 스크립트 파일을 다운로드합니다.To get the AdventureWorks sample database, download at least the database file and the samples and scripts file from Microsoft Download Center.

샘플 데이터베이스를 SQL Server 2016 인스턴스로 복원한 후에 샘플 파일을 추출한 다음, JSON 폴더에서 JSON 샘플 쿼리 프로시저 뷰 및 indexes.sql 파일을 엽니다.After you restore the sample database to an instance of SQL Server 2016, extract the samples file, and then open the JSON Sample Queries procedures views and indexes.sql file from the JSON folder. 이 파일의 스크립트를 실행하여 일부 기존 데이터의 서식을 JSON 데이터로 다시 지정하고, JSON 데이터에 대한 샘플 쿼리 및 보고서를 테스트하고, JSON 데이터를 인덱싱하고, JSON을 가져오고 내보냅니다.Run the scripts in this file to reformat some existing data as JSON data, test sample queries and reports over the JSON data, index the JSON data, and import and export JSON.

파일에 포함된 스크립트를 통해 수행할 수 있는 작업은 다음과 같습니다.Here's what you can do with the scripts that are included in the file:

  • 기존 스키마를 비정규화하여 JSON 데이터의 열을 만듭니다.Denormalize the existing schema to create columns of JSON data.

    • SalesReasons, SalesOrderDetails, SalesPerson, Customer 및 기타 판매 주문 관련 정보가 JSON 열에 포함된 테이블의 정보를 SalesOrder_json 테이블에 저장합니다.Store information from SalesReasons, SalesOrderDetails, SalesPerson, Customer, and other tables that contain information related to sales order into JSON columns in the SalesOrder_json table.

    • EmailAddresses/PersonPhone 테이블의 정보를 JSON 개체 배열로 Person_json 테이블에 저장합니다.Store information from EmailAddresses/PersonPhone tables in the Person_json table as arrays of JSON objects.

  • JSON 데이터를 쿼리하는 프로시저 및 뷰를 만듭니다.Create procedures and views that query JSON data.

  • JSON 데이터 인덱싱Index JSON data. JSON 속성의 인덱스 및 전체 텍스트 인덱스를 만듭니다.Create indexes on JSON properties and full-text indexes.

  • JSON 가져오기 및 내보내기Import and export JSON. Person 및 SalesOrder 테이블의 콘텐츠를 JSON 결과로 내보내고, JSON 입력을 사용하여 Person 및 SalesOrder 테이블을 가져오고 업데이트하는 프로시저를 만들고 실행합니다.Create and run procedures that export the content of the Person and the SalesOrder tables as JSON results, and import and update the Person and the SalesOrder tables by using JSON input.

  • 쿼리 예제 실행Run query examples. 2단계와 4단계에서 만든 저장 프로시저 및 뷰를 호출하는 몇 가지 쿼리를 실행합니다.Run some queries that call the stored procedures and views that you created in steps 2 and 4.

  • 스크립트 정리Clean up scripts. 2단계와 4단계에서 만든 저장 프로시저 및 뷰를 유지하려면 이 부분을 실행하지 마세요.Don't run this part if you want to keep the stored procedures and views that you created in steps 2 and 4.

SQL Server 및 Azure SQL Database에서 JSON에 대한 자세한 정보Learn more about JSON in SQL Server and Azure SQL Database

Microsoft 블로그 게시물Microsoft blog posts

특정 솔루션, 사용 사례 및 권장 사항은 SQL Server 및 Azure SQL Database의 기본 제공 JSON 지원에 대한 블로그 게시물을 참조하세요.For specific solutions, use cases, and recommendations, see these blog posts about the built-in JSON support in SQL Server and Azure SQL Database.

Microsoft 비디오Microsoft videos

SQL Server 및 Azure SQL Database에서 기본 제공 JSON 지원에 대한 시각적 소개는 다음 비디오를 참조하세요.For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following video:

SQL Server 2016 및 Azure SQL Database에서 JSON 사용Using JSON in SQL Server 2016 and Azure SQL Database

JSON 함수를 사용하여 SQL Server로 REST API 빌드하기Building REST API with SQL Server using JSON functions

참조 문서Reference articles