mssql json파라미터 전달하기- openjson() 활용

 

SQL SERVER 2016부터 JSON형태의 STRING쉽게 DB에서 파싱이 가능해졌습니다.

OPENJSON()함수를 사용해서 JSON 문자열을 SELECT 결과로 쉽게 변환이 가능합니다.

 

1. OPENJSON() 기본 사용

 

아래 코드를 실행해보면,

DECLARE @json NVARCHAR(MAX) 

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'; 

SELECT * 
FROM OPENJSON(@json);

JSON 문자열을 파싱해 SELECT의 결과처럼 출력되는 것을 알 수 있습니다.

 

 

2. OPENJSON() 특정 속성만 출력

아래 케이스의 경우 특정 KEY값만 확인해서 추출도 가능합니다. 

WITH() 절을 활용해 $.JSONKEY값 형태로 스크립트를 작성하면 해당 속성값이 SELECT결과에 포함시킬 수 있습니다.

DECLARE @json NVARCHAR(MAX) 
SET @json =    
  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   
         }   
      }   
 ]'   
    
SELECT * FROM   
 OPENJSON ( @json )   
WITH (    
              Number   varchar(200) '$.Order.Number' ,   
              Date     datetime     '$.Order.Date',   
              Customer varchar(200) '$.AccountNumber',   
              Quantity int          '$.Item.Quantity'   
 )

 

 

3. OPENJSON() JSON문자열 그대로 출력

 

만약 SELECT 출력결과에 JSON형태 그대로를 유지하고 싶다면,

WITH절에 칼럼 타입을 NVARCHAR(MAX) AS JSON으로 주면 됩니다.

DECLARE @json NVARCHAR(MAX) =
N'{"someObject":   
    {"someArray":  
      [  
          {"k1": 11, "k2": null, "k3": "text"},  
          {"k1": 21, "k2": "text2", "k4": { "data": "text4" }},  
          {"k1": 31, "k2": 32},  
          {"k1": 41, "k2": null, "k4": { "data": false }}     
       ]  
    }  
 }'  
   
SELECT * FROM  
OPENJSON(@json, N'lax $.someObject.someArray')  
WITH ( k1 int,   
        k2 varchar(100),  
        col3 varchar(6) N'$.k3',  
        col4 varchar(10) N'lax $.k4.data',  
        col5 nvarchar(MAX) N'lax $.k4' AS JSON, 
        array_element nvarchar(MAX) N'$' AS JSON  
 )  

*COL5, ARRAY_ELEMENT 칼럼에는 JSON 문자열 그대로 출력되는 것을 확인 할 수 있습니다.

 

4. OPENJSON() 사용시 호환성레벨 130 이상 필요

OPENJSON은 호환성레벨이 130이상이어야만 사용가능합니다.

130보다 작을 경우 따로 설정을 해주셔야합니다.

호환성레벨은 아래 스크립트로 조회할 수 있습니다. WHERE절 NAME에 DATABASE명을 설정해주시면 됩니다.