MSSQL을 이용한 JSON데이터 처리 방법

1. JSON형식으로 저장된 데이터를 불러와 원하는 값 추출

 

   json형태의 데이터를 SQL변수로 받아들여 원하는 값을 추출하는 것입니다. 

   openjson을 이용하여, 손쉽게 처리를 할 수가 있는데, 배치작업의 경우 넘어오는 데이터를 프로시저에서

   임의의 변수로 전달받아 MSSQL의 OPENJSON을 이용하여 원하는 값을 뽑아내는 방법입니다. 

 

DECLARE @json nvarchar(max)
SET @json = '{"info":{"address":[{"city":"seoul"},{"town":"Paris"},{"city":"busan"}]}}'

SELECT city
FROM OPENJSON(@json, '$.info.address')
WITH (
   city nvarchar(100) '$.city'
)

 

 JSON 데이터 형식을 @json 변수로 할당 후, OPENJSON을 이용하여, json 배열 이름과 데이터 이름을 지정하여 값을 읽어올 수가 있습니다. 

OPENJSON(@json, '$.info.address')에서 @json  문자열에서 info.address 배열명을 지정합니다. 

그런 후  with(city nvarchar(100) '$.city') 를 이용하여 배열 안에 있는 city라는 데이터 이름을 지정하여 city에 해당되는

데이터를 가져올 수 있습니다.

 

 

2. DB에 저장되어있는 데이터를 JSON형태로 뿌리기

 

   DECLARE @jsonString navarchar(max)
   SET @jsonString=( Select city, age From member FOR JSON AUTO, ROOT('member'))

   SELECT @jsonString

SELECT문을 통하여 테이블에 속해있는 city, age의 값을 JSON형태로 @jsonString변수에 담습니다. 

여기서 json다음에 auto 또는 path를 지정할 수 있는데, 여기서는 auto에 대해 알아보겠습니다. 

auto를 지정하게 되면, SQL의 하나의 칼럼에 single Line으로 SQL을 쿼터 분석기에서 조회했을 때처럼 보입니다. 

 

또한 옵션으로 ROOT('member')를 지정할 수 있는데 이는 json의 데이터를 배열의 이름으로 추가를 해주는 옵션입니다. 

 

{"member" :[{"city":"seoul", "age":"20"},{"city":"busan", "age":"30"},{"city":"daegu", "age":"40"},{"city":"daejeon", "age":"50"}]}

위와 같은 형태로 member라는 배열의 이름을 갖고 있는 데이터의 형태로 읽히게 되며,

이를 저장한 @jsonString을 select 하여 데이터를 JSON 형태의 데이터로 뿌려줄 수가 있습니다.

 

단, 위에서 언급한 FOR JSON이나 OPENJSON의 기능은 MSSQL 2016 버전부터 지원하는 기능으로, 2016 밑 버전에서는

사용할 수 없는 명령어이긴 합니다. 

json데이터를 애플리케이션 자체에서 파싱하여 데이터를 처리할 수도 있지만, 배치작업 같은 대량의 데이터를 처리할때는 애플리케이션 자체의 파싱보다 좀 더 간단하게 구현할 수 있다는 장점이 있습니다.