SQL Server 2016 의 JSON PARSE 함수 중, JSON_VALUE 와 JSON_QUERY 알아보기

 

1. JSON_VALUE

JSON 문자열에서 스칼라 값을 추출합니다. 스칼라 값 대신 JSON 문자열에서 개체 또는 배열을 추출하려면 JSON_QUERY(Transact-SQL)를 참조하세요.

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15

JSON_VALUE(Transact-SQL) - SQL Server

Person 이라는 테이블에 json 형태를 담는 jsonInfo 필드가 있을 경우, JSON_VALUE 함수를 통해 WHERE 조건 및 ORDER 정렬을 사용할 수 있습니다.

샘플 예제

SELECT FirstName, LastName,
 JSON_VALUE(jsonInfo,'$.info.address[0].town') AS Town
FROM Person.Person
WHERE JSON_VALUE(jsonInfo,'$.info.address[0].state') LIKE 'US%'
ORDER BY JSON_VALUE(jsonInfo,'$.info.address[0].town')

다음 예에서는 JSON 속성 중, town의 값을 추출하는 예제입니다. 배열 형태를 갖추고 있으므로 [] 를 통해 0 부터 접근하여 가져올 수 있습니다.

DECLARE @jsonInfo NVARCHAR(MAX)
DECLARE @town NVARCHAR(32)

SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';

SET @town=JSON_VALUE(@jsonInfo,'$.info.address[0].town'); -- Paris
SET @town=JSON_VALUE(@jsonInfo,'$.info.address[1].town'); -- London

아래 샘플은 테이블 구성을 만들때 부터 JSON_VALUE 함수를 미리 가동하여 값을 넣는 방법도 제공합니다.

CREATE TABLE dbo.Store
 (
  StoreID INT IDENTITY(1,1) NOT NULL,
  Address VARCHAR(500),
  jsonContent NVARCHAR(8000),
  Longitude AS JSON_VALUE(jsonContent, '$.address[0].longitude'),
  Latitude AS JSON_VALUE(jsonContent, '$.address[0].latitude')
 )

 

2. JSON_QUERY

JSON 문자열에서 개체 또는 배열을 추출합니다.

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver15

JSON_QUERY(Transact-SQL) - SQL Server

3. JSON_VALUE 및 JSON_QUERY 비교

JSON_VALUE 와 JSON_QUERY 간의 주요 차이점은 JSON_VALUE 는 스칼라 값을 반환하고 JSON_QUERY 는 개체 또는 배열을 반환한다는 점입니다.

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

이 예제 JSON 텍스트에서 데이터 멤버 "a"와 "c"는 문자열 값이며, 데이터 멤버 "b"는 배열입입니다.. 

JSON_VALUE 및 JSON_QUERY 는 다음 결과를 반환합니다.

https://docs.microsoft.com/ko-kr/sql/relational-databases/json/validate-query-and-change-json-data-with-built-in-functions-sql-server?view=sql-server-ver15#JSONCompare

기본 함수를 사용하여 JSON 데이터 유효성 검사, 쿼리, 변경 - SQL Server

---

아래와 같은 형태의 JSON 이 테이블에 저장되어 있다는 가정하에 사용하는 방법을 알아보죠.

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

아래 JSON 은 doc 필드에 저장되어 있습니다.

{
  "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 형태로 구성되어 있는 값만 가져오도록 where 조건문에서 ISJSON 함수를 통해 해당 ROW 값들을 가져옵니다.

SELECT *
FROM Families
WHERE ISJSON(doc) > 0 

JSON_VALUE 함수는 JSON 문자열에서 스칼라 값을 추출합니다. 다음 쿼리는 JSON 필드에서 id 를 기준으로city 및 state JSON 필드를 정렬된 값 WakefieldFamily 값과 일치하는 row 값을 반환합니다.

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'WakefieldFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC, JSON_VALUE(f.doc, '$.address.state') ASC

결과내용

JSON_QUERY 함수는 JSON 문자열에서 개체 또는 배열을 추출합니다. 다음 예제는 쿼리 결과에 JSON 조각을 반환하는 방법을 보여줍니다.

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'WakefieldFamily'

결과내용

 

---

json 형태를 FOR JSON AUTO 그리고 ROOT 이름을 JOB 으로 명시했습니다.

INSERT INTO [dbo].[UNIV_JOB_LogDeleted]
           ([TableName]
           ,[ValueJson]
           ,[WriteUserID]
           ,[WriteName]
           ,[WriteDate])
select '',
(SELECT *
FROM UNIV_JOB_Applier where applierid = 8
FOR JSON AUTO, ROOT('JOB')) as json
,1
           ,''
           ,''

저장된 JSON 형태는 아래와 같습니다. 해당 값을 접근하기 위해서는 JSON_VALUE() 함수를 통해 가능합니다.

{
   "JOB":[
      {
         "ApplierID":8,
         "ApplierCode":"1-XXX-02",
         "NoticeID":4,
         "NoticeDutyID":3,
         "AuthType":"EMAIL",
         "AuthID":"XXXX@naver.com",
         "AuthPwd":"6033dc4b9a36c7663aaefd7fab3a40e18f7c7ae627006e97b8fb5631521f91e185b4acc6c9045c937c00d0531411795866c86cfa45e2aa1e70aa4e0d634d1bac",
         "AuthRePwdExpireDate":"2020-11-02T13:16:23.977",
         "AuthToken":"",
         "IsTermsAgree":"1",
         "Name":"홍길동",
         "Email":"XXX@naver.com",
         "JobType":"신입",
         "IsFinal":1,
         "IsResourcePool":"0",
         "WriteDate":"20200506165247270",
         "ModifyDate":"20200507135137410"
      }
   ]
}

JOB 상위 노드 아래는 하위 노드가 존재하며 여러개 가능하므로 배열 형태로 보시면 됩니다. 따라서 위의 샘플 JSON 은 하나의 자식만 있다는 가정하에 JOB[0] 을 통해 하나의 자식노드를 접근하고, 자식노드는 해당 값에 하위노드가 없는 관계로 곧바로 키값을 기재하시면 됩니다.

SELECT JSON_VALUE(f.ValueJson, '$.JOB[0].ApplierID')  AS ApplierID, 
       JSON_VALUE(f.ValueJson, '$.JOB[0].Name') AS Name,
       JSON_VALUE(f.ValueJson, '$.JOB[0].Email') AS Email
FROM [UNIV_JOB_LogDeleted] f 
WHERE JSON_VALUE(f.ValueJson, '$.JOB[0].ApplierID') = N'8'