기본 함수를 사용하여 JSON 데이터 유효성 검사, 쿼리, 변경(SQL Server)Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)
적용 대상:Applies to: SQL Server 2016(13.x)SQL Server 2016 (13.x)
SQL Server 2016(13.x)SQL Server 2016 (13.x)
SQL Server 2016(13.x)SQL Server 2016 (13.x)
SQL Server 2016(13.x)SQL Server 2016 (13.x)
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. 다음 쿼리는 id
JSON 필드가 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:
NameName | CityCity | 국가County |
---|---|---|
AndersenFamilyAndersenFamily | NYNY | ManhattanManhattan |
자세한 내용은 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:
주소Address | 부모 항목Parents | Parent0Parent0 |
---|---|---|
{ "state": "NY", "county": "Manhattan", "city": "NY" }{ "state": "NY", "county": "Manhattan", "city": "NY" } | [{ "familyName": "Wakefield", "givenName": "Robin" }, {"familyName": "Miller", "givenName": "Ben" } ][{ "familyName": "Wakefield", "givenName": "Robin" }, {"familyName": "Miller", "givenName": "Ben" } ] | { "familyName": "Wakefield", "givenName": "Robin" }{ "familyName": "Wakefield", "givenName": "Robin" } |
자세한 내용은 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:
NameName | CityCity | givenNamegivenName | gradegrade |
---|---|---|---|
AndersenFamilyAndersenFamily | NYNY | JesseJesse | 11 |
AndersenFamilyAndersenFamily | NYNY | LisaLisa | 88 |
하나의 부모 행이 생성된 두 개의 자식 행과 조인되기 때문에 자식 하위 배열의 두 요소를 구문 분석하면 두 행이 결과로 반환됩니다.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:
familyNamefamilyName | childGivenNamechildGivenName | childFirstNamechildFirstName | petNamepetName |
---|---|---|---|
AndersenFamilyAndersenFamily | JesseJesse | MerriamMerriam | GoofyGoofy |
AndersenFamilyAndersenFamily | JesseJesse | MerriamMerriam | 그림자Shadow |
AndersenFamilyAndersenFamily | LisaLisa | MillerMiller | NULL |
루트 문서는 첫 번째 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:
경로Path | JSON_VALUE 는 다음을 반환합니다.JSON_VALUE returns | JSON_QUERY 는 다음을 반환합니다.JSON_QUERY returns |
---|---|---|
$ | NULL 또는 오류NULL or error | { "a": "[1,2]", "b": [1,2], "c":"hi"} |
$.a$.a | [1,2][1,2] | NULL 또는 오류NULL or error |
$.b$.b | NULL 또는 오류NULL or error | [1,2][1,2] |
$.b[0]$.b[0] | 11 | NULL 또는 오류NULL or error |
$.c$.c | hihi | NULL 또는 오류NULL or error |
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 및 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)