기본 함수를 사용하여 JSON 데이터 유효성 검사, 쿼리, 변경(SQL Server)Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)

• 07/17/2017
• 읽는 데 5분
• • 
  • 
  • 
  • 

적용 대상: SQL Server Azure SQL DatabaseAzure Synapse Analytics(SQL DW) 병렬 데이터 웨어하우스 APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

JSON의 기본 지원에는 이 항목에서 간단히 설명하는 다음과 같은 기본 제공 함수가 포함됩니다.The built-in support for JSON includes the following built-in functions described briefly in this topic.

• ISJSON 은 문자열에 유효한 JSON이 포함되어 있는지 여부를 테스트합니다.ISJSON tests whether a string contains valid JSON.

• JSON_VALUE JSON 문자열에서 스칼라 값을 추출합니다.JSON_VALUE extracts a scalar value from a JSON string.

• JSON_QUERY 는 JSON 문자열에서 개체 또는 배열을 추출합니다.JSON_QUERY extracts an object or an array from a JSON string.

• JSON_MODIFY 는 JSON 문자열의 속성 값을 업데이트하고 업데이트된 JSON 문자열을 반환합니다.JSON_MODIFY updates the value of a property in a JSON string and returns the updated JSON string.

이 페이지의 예제에 대한 JSON 텍스트JSON text for the examples on this page

이 페이지의 예제는 다음 예제에 표시된 콘텐츠와 비슷한 JSON 텍스트를 사용합니다.The examples on this page use the JSON text similar to the content shown in the following example:

{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
        "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}

중첩된 복합 요소를 포함하는 이 JSON 문서는 다음 샘플 테이블에 저장됩니다.This JSON document, which contains nested complex elements, is stored in the following sample table:

CREATE TABLE Families (
   id int identity constraint PK_JSON_ID primary key,
   doc nvarchar(max)
)

ISJSON 함수를 사용하여 JSON 텍스트의 유효성을 검사합니다.Validate JSON text by using the ISJSON function

ISJSON 함수는 문자열에 유효한 JSON이 포함되어 있는지 여부를 테스트합니다.The ISJSON function tests whether a string contains valid JSON.

다음 예제는 JSON 열에 유효한 JSON 텍스트가 포함된 행을 반환합니다.The following example returns rows in which the JSON column contains valid JSON text. 명시적 JSON 제약 조건이 없으면 NVARCHAR 열에 텍스트를 입력할 수 있습니다.Note that without explicit JSON constraint, you can enter any text in the NVARCHAR column:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0 

자세한 내용은 ISJSON(Transact-SQL)을 참조하세요.For more info, see ISJSON (Transact-SQL).

JSON_VALUE 함수를 사용하여 JSON 텍스트에서 값을 추출합니다.Extract a value from JSON text by using the JSON_VALUE function

JSON_VALUE 함수는 JSON 문자열에서 스칼라 값을 추출합니다.The JSON_VALUE function extracts a scalar value from a JSON string. 다음 쿼리는 idJSON 필드가 city 및 state JSON 필드를 기준으로 정렬된 값 AndersenFamily과 일치하는 문서를 반환합니다.The following query will return the documents where the id JSON field matches the value AndersenFamily, ordered by city and state JSON fields:

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC

이 쿼리의 결과는 다음 표에 나와 있습니다.The results of this query are shown in the following table:

자세한 내용은 JSON_VALUE(Transact-SQL)을 참조하세요.For more info, see JSON_VALUE (Transact-SQL).

JSON_QUERY 함수를 사용하여 JSON 텍스트에서 개체 또는 배열 추출Extract an object or an array from JSON text by using the JSON_QUERY function

JSON_QUERY 함수는 JSON 문자열에서 개체 또는 배열을 추출합니다.The JSON_QUERY function extracts an object or an array from a JSON string. 다음 예제는 쿼리 결과에 JSON 조각을 반환하는 방법을 보여줍니다.The following example shows how to return a JSON fragment in query results.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
       JSON_QUERY(f.doc, '$.parents') AS Parents,
       JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f 
WHERE JSON_VALUE(f.doc, '$.id') = N'AndersenFamily'

이 쿼리의 결과는 다음 표에 나와 있습니다.The results of this query are shown in the following table:

자세한 내용은 JSON_QUERY(Transact-SQL)를 참조하세요.For more info, see JSON_QUERY (Transact-SQL).

중첩된 JSON 컬렉션 구문 분석Parse nested JSON collections

OPENJSON 함수를 사용하면 JSON 하위 배열을 행 집합으로 변환한 다음, 부모 요소와 조인할 수 있습니다.OPENJSON function enables you to transform JSON sub-array into the rowset and then join it with the parent element. 예를 들어, 모든 제품군 문서를 반환하고 내부 JSON 배열로 저장된 children 개체와 "조인"할 수 있습니다.As an example, you can return all family documents, and "join" them with their children objects that are stored as an inner JSON array:

SELECT JSON_VALUE(f.doc, '$.id')  AS Name, 
       JSON_VALUE(f.doc, '$.address.city') AS City,
       c.givenName, c.grade
FROM Families f
        CROSS APPLY OPENJSON(f.doc, '$.children')
            WITH(grade int, givenName nvarchar(100))  c

이 쿼리의 결과는 다음 표에 나와 있습니다.The results of this query are shown in the following table:

하나의 부모 행이 생성된 두 개의 자식 행과 조인되기 때문에 자식 하위 배열의 두 요소를 구문 분석하면 두 행이 결과로 반환됩니다.We are getting two rows as a result because one parent row is joined with two child rows produced by parsing two elements of the children subarray. OPENJSON 함수는 doc 열에서 children 조각을 구문 분석하고 각 요소에서 grade 및 givenName을 행 세트로 반환합니다.OPENJSON function parses children fragment from the doc column and returns grade and givenName from each element as a set of rows. 이 행 집합은 부모 문서와 조인할 수 있습니다.This rowset can be joined with the parent document.

중첩된 계층적 JSON 하위 배열 쿼리Query nested hierarchical JSON sub-arrays

중첩된 JSON 구조를 쿼리하기 위해 여러 CROSS APPLY OPENJSON 호출을 적용할 수 있습니다.You can apply multiple CROSS APPLY OPENJSON calls in order to query nested JSON structures. 이 예제에 사용된 JSON 문서에는 children이라는 중첩 배열이 있습니다. 여기에서 각 자식에는 pets의 중첩된 배열이 있습니다.The JSON document used in this example has a nested array called children, where each child has nested array of pets. 다음 쿼리는 각 문서의 자식을 구문 분석하고, 각 배열 개체를 행으로 반환한 다음, pets 배열을 구문 분석합니다.The following query will parse children from each document, return each array object as row, and then parse pets array:

SELECT  familyName,
    c.givenName AS childGivenName,
    c.firstName AS childFirstName,
    p.givenName AS petName 
FROM Families f 
    CROSS APPLY OPENJSON(f.doc) 
        WITH (familyName nvarchar(100), children nvarchar(max) AS JSON)
        CROSS APPLY OPENJSON(children) 
        WITH (givenName nvarchar(100), firstName nvarchar(100), pets nvarchar(max) AS JSON) as c
            OUTER APPLY OPENJSON (pets)
            WITH (givenName nvarchar(100))  as p

첫 번째 OPENJSON 호출은 AS JSON 절을 사용하여 children 배열의 조각을 반환합니다.The first OPENJSON call will return fragment of children array using AS JSON clause. 이 배열 조각은 pets 배열뿐만 아니라 각 자식의 givenName, firstName을 반환하는 두 번째 OPENJSON 함수에 제공됩니다.This array fragment will be provided to the second OPENJSON function that will return givenName, firstName of each child, as well as the array of pets. pets 배열은 애완 동물의 givenName을 반환하는 세 번째 OPENJSON 함수에 제공됩니다.The array of pets will be provided to the third OPENJSON function that will return the givenName of the pet. 이 쿼리의 결과는 다음 표에 나와 있습니다.The results of this query are shown in the following table:

루트 문서는 첫 번째 OPENJSON(children) 호출로 반환된 두 children 행과 조인되어 두 개의 행(또는 튜플)을 만듭니다.The root document is joined with two children rows returned by first OPENJSON(children) call making two rows (or tuples). 그런 다음, 각 행이 OUTER APPLY 연산자를 사용하여 OPENJSON(pets)에서 생성된 새 행과 조인됩니다.Then each row is joined with the new rows generated by OPENJSON(pets) using OUTER APPLY operator. Jesse에는 두 마리의 애완 동물이 있으므로 (AndersenFamily, Jesse, Merriam)이 Goofy 및 Shadow에 대해 생성된 두 개의 행과 조인됩니다.Jesse has two pets, so (AndersenFamily, Jesse, Merriam) is joined with two rows generated for Goofy and Shadow. Lisa에게는 애완 동물이 없으므로 이 튜플에 대해 OPENJSON(pets)에서 반환되는 행이 없습니다.Lisa doesn't have the pets, so there are no rows returned by OPENJSON(pets) for this tuple. 그러나 OUTER APPLY를 사용하고 있기 때문에 열에 NULL이 반환됩니다.However, since we are using OUTER APPLY we are getting NULL in the column. OUTER APPLY 대신, CROSS APPLY를 사용하는 경우 이 튜플에 조인할 수 있는 애완 동물 행이 없기 때문에 Lisa는 결과에 반환되지 않습니다.If we put CROSS APPLY instead of OUTER APPLY, Lisa would not be returned in the result because there are no pets rows that could be joined with this tuple.

JSON_VALUE 및 JSON_QUERY 비교Compare JSON_VALUE and JSON_QUERY

JSON_VALUE 와 JSON_QUERY 간의 주요 차이점은 JSON_VALUE 는 스칼라 값을 반환하고 JSON_QUERY 는 개체 또는 배열을 반환한다는 점입니다.The key difference between JSON_VALUE and JSON_QUERY is that JSON_VALUE returns a scalar value, while JSON_QUERY returns an object or an array.

다음 예제의 JSON 쿼리를 살펴보십시오.Consider the following sample JSON text.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}  

이 예제 JSON 텍스트에서 데이터 멤버 "a"와 "c"는 문자열 값이며 데이터 멤버 "b"는 배열입니다.In this sample JSON text, data members "a" and "c" are string values, while data member "b" is an array. JSON_VALUE 및 JSON_QUERY 는 다음 결과를 반환합니다.JSON_VALUE and JSON_QUERY return the following results:

AdventureWorks 예제 데이터베이스로 JSON_VALUE 및 JSON_QUERY 테스트Test JSON_VALUE and JSON_QUERY with the AdventureWorks sample database

AdventureWorks 예제 데이터베이스로 다음 예제를 실행하여 이 항목에서 설명하는 기본 제공 함수를 테스트합니다.Test the built-in functions described in this topic by running the following examples with the AdventureWorks sample database. AdventureWorks를 다운로드할 수 있는 위치에 대한 정보와 스크립트를 실행하여 테스트용 JSON 데이터를 추가하는 방법은 기본 제공 JSON 지원 시험 사용을 참조하세요.For info about where to get AdventureWorks, and about how to add JSON data for testing by running a script, see Test drive built-in JSON support.

다음 예제에서 SalesOrder_json 테이블의 Info 열에는 JSON 텍스트가 포함되어 있습니다.In the following examples, the Info column in the SalesOrder_json table contains JSON text.

예제 1 - 두 표준 열과 JSON 데이터 반환Example 1 - Return both standard columns and JSON data

다음 쿼리는 표준 관계형 열과 JSON 열의 값을 모두 반환합니다.The following query returns values from both standard relational columns and from a JSON column.

SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, AccountNumber, TotalDue,
 JSON_QUERY(Info,'$.ShippingInfo') ShippingInfo,
 JSON_QUERY(Info,'$.BillingInfo') BillingInfo,
 JSON_VALUE(Info,'$.SalesPerson.Name') SalesPerson,
 JSON_VALUE(Info,'$.ShippingInfo.City') City,
 JSON_VALUE(Info,'$.Customer.Name') Customer,
 JSON_QUERY(OrderItems,'$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0

예제 2 - JSON 값 집계 및 필터링Example 2- Aggregate and filter JSON values

다음 쿼리는 고객 이름(JSON에 저장)과 상태(일반 열에 저장)를 기준으로 소계를 집계합니다.The following query aggregates subtotals by customer name (stored in JSON) and status (stored in an ordinary column). 그런 다음 시(JSON에 저장) 및 OrderDate(일반 열에 저장)를 기준으로 결과를 필터링합니다.Then it filters the results by city (stored in JSON) and OrderDate (stored in an ordinary column).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid=3;

SET @city=N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer, Status, SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID=@territoryid
 AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
 AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'), Status
HAVING SUM(SubTotal)>1000

JSON_MODIFY 함수를 사용하여 JSON 텍스트의 속성 값을 업데이트합니다.Update property values in JSON text by using the JSON_MODIFY function

JSON_MODIFY 함수는 JSON 문자열의 속성 값을 업데이트하고 업데이트된 JSON 문자열을 반환합니다.The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.

다음 예제에서는 JSON이 포함된 변수에서 JSON 속성 값을 업데이트합니다.The following example updates the value of a JSON property in a variable that contains JSON.

SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London')    

자세한 내용은 JSON_MODIFY (Transact-SQL)을 참조하세요.For more info, see JSON_MODIFY (Transact-SQL).

SQL Server 및 Azure SQL Database에서 JSON에 대한 자세한 정보Learn more about JSON 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 videos:

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

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

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

참고 항목See Also

ISJSON(Transact-SQL) ISJSON (Transact-SQL)
JSON_VALUE(Transact-SQL) JSON_VALUE (Transact-SQL)
JSON_QUERY(Transact-SQL) JSON_QUERY (Transact-SQL)
JSON_MODIFY(Transact-SQL) JSON_MODIFY (Transact-SQL)
JSON 경로 식(SQL Server)JSON Path Expressions (SQL Server)