Db2 SQL 함수로 JSON 데이터 만들기

통합 로그, 오픈 API, 생성형 AI 를 비롯한 많은 영역에서 JSON 데이터 형식이 사용이 되고 있습니다. 

 

이번 글에서는 Db2 for i의 SQL 함수로 JSON 데이터를 만드는 방법들을 정리해보려고 합니다.

 

빌트인 SQL 함수를 사용해서, Db2의 관계형 데이터 테이블에서 JSON 형식의 데이터를 생성할 수가 있습니다. 

 

먼저, Db2 테이블을 2개 만들어보겠습니다. empdata 테이블은 기본 직원 데이터를 포함하고 있고, emp_account 테이블은 직원의 계정 정보를 포함합니다. 아래의 SQL문을 실행하면, 테이블을 만들고 데이터가 저장될 것입니다.

 

create table empdata (id int,
                      last_name varchar(10),
                      first_name varchar(10), 
                      office_number varchar(10),
                      work_phone varchar(20),
                      home_phone varchar(20));
 
create table emp_account (id int,
                          account varchar(20));                      
                      
insert into empdata values (901, 'Doe', 'John', 'E-334', '555-7242', '555-3762');
insert into emp_account values (901, '36232'), (901, '73263');

insert into empdata values (902, 'Pan', 'Peter', 'E-216', '555-8925', null);
insert into emp_account values (902, '76232'), (902, '72963');

insert into empdata values (903, 'Jones', 'Mary', 'E-739', '555-4311', '555-6312');

insert into empdata values (904, 'Smith', 'Sally', null, null, null);

 

 

최종 목표

SQL 함수로 하려고 하는 최종 목표는 다음과 같은 4개의 직원 정보를 JSON 데이터로 생성하는 것입니다.

{"id":901, "name" : { "first":"John", "last":"Doe" }, "office" : "E-334",   
      "phones" : [ { "type":"home", "number":"555-3762" }, 
                   { "type":"work", "number":"555-7242" } ],      
      "accounts" : [ { "number":"36232"}, { "number":"73263"}] } 

{"id":902, "name" : { "first":"Peter", "last":"Pan" }, "office" : "E-216", 
      "phones" : [ { "type":"work", "number":"555-8925" } ], 
      "accounts" : [ { "number":"76232"}, {"number":"72963"}] }

{"id":903, "name" : { "first":"Mary", "last":"Jones" }, "office" : "E-739", 
      "phones" : [ { "type":"home", "number":"555-6312" }, 
                   { "type":"work", "number":"555-4311" } ], }

{"id":904, "name" : { "first":"Sally", "last":"Smith" } }

 

1단계: 하나의 테이블에 있는 컬럼 정보로 간단한 JSON 데이터 생성하기

select json_object ('id' value id, 
                    'name' value last_name, 
                    'office' value office_number) 
from empdata;

 

이 예제에서는 JSON_OBJECT 스칼라 함수를 사용하여 JSON 오브젝트를 생성하였습니다. VALUE로 EMPDATA 테이블의 ID, LAST_NAME및 OFFICE_NUMBER 컬럼을 사용하여 세 개의 키: 값 쌍을 정의합니다. 각 키 이름은 출력에 나타나는 대로 정확하게 문자열로 지정됩니다. 이 쿼리의 결과로 아래와 같이 EMPDATA 테이블의 각 행에 대해 하나씩 총 네 개의 행이 JSON 데이터로 생성되었습니다.

{"id":901,"name":"Doe","office":"E-334"}
{"id":902,"name":"Pan","office":"E-216"}
{"id":903,"name":"Jones","office":"E-739"}
{"id":904,"name":"Smith","office":null}

 

 

위의 값을 보면 네번째 행 Smith의 경우, office 의 값이 없어서 null로 표현된 것을 확인할 수 있는데요. 이런 경우, NULL 일 때는 office 값을 안 보이게 할 수 있습니다. 방법은 ABSENT ON NULL절을 사용하는 것입니다. 이는 JSON 오브젝트의 값이 NULL값이면 NULL인 키: 값 쌍이 결과에 포함되지 않아야 함을 나타냅니다.

select json_object ('id' value id, 
                    'name' value last_name, 
                    'office' value office_number
                    absent on null) 
from empdata;

 

이제 ID 904의 office 값이 표시되지 않습니다.

{"id":901,"name":"Doe","office":"E-334"}
{"id":902,"name":"Pan","office":"E-216"}
{"id":903,"name":"Jones","office":"E-739"}
{"id":904,"name":"Smith"}

 

2단계: JSON 오브젝트 중첩 (Embed)

다음으로, 복합 이름 오브젝트를 임베드하는 작업입니다. 먼저 임베드할 데이터만 JSON으로 생성해서 정상인지 확인해보겠습니다.

select json_object ('first' value first_name, 
                    'last' value last_name) 
from empdata;

 

이 쿼리의 결과는 다음과 같습니다.

{"first":"John","last":"Doe"}
{"first":"Peter","last":"Pan"}
{"first":"Mary","last":"Jones"}
{"first":"Sally","last":"Smith"}

 

정상적으로 보이므로, 이 데이터를 이전에 했던 쿼리에 포함시켜 보겠습니다. name 에 

JSON 오브젝트를 추가하여 임베드할 것입니다.

select json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number  
                    absent on null) 
from empdata;

 

결과는 다음과 같이 name에 중첩된 JSON 데이터로 보이게 됩니다.

{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334"}
{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216"}
{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739"}
{"id":904,"name":{"first":"Sally","last":"Smith"}}

 

3단계: JSON 배열 빌드

다음으로 전화번호 배열을 처리해 보겠습니다. 하나의 키/값이 아닌 여러 개의 값을 배열 요소로 나열할 때는 JSON 오브젝트가 아닌 JSON 배열이어야 합니다. 따라서, JSON 배열이 되도록, JSON_ARRAY 스칼라 함수를 사용할 것입니다. 또한, 전화번호 데이터가 없을 때, 배열이 생성되지 않도록 CASE 문을 추가하겠습니다. 전화번호가 집 또는 직장인지 여부를 알기 때문에 type 값을 home 또는 work로 지정합니다. 

select json_array 
        (case when home_phone is not null then 
            json_object('type' value 'home', 
                        'number' value home_phone) end, 
         case when work_phone is not null then 
            json_object('type' value 'work', 
                        'number' value work_phone) end 
         )
from empdata;

 

결과는 다음과 같습니다. 각 항목의 출력은 두 행으로 구분되어 완전한 결과를 표시합니다.

["{\"type\":\"home\",\"number\":\"555-3762\"}",
    "{\"type\":\"work\",\"number\":\"555-7242\"}"]

["{\"type\":\"work\",\"number\":\"555-8925\"}"]

["{\"type\":\"home\",\"number\":\"555-6312\"}",
    "{\"type\":\"work\",\"number\":\"555-4311\"}"]

[]

 

그런데, 모든 결과값에 추가 \ (역슬래시) 문자가 생겼습니다! JSON 으로 이미 형식화된 일반 문자 데이터와 문자 데이터의 차이를 알려 주기 위함이라고 합니다. JSON_ARRAY 함수(또는 일부 JSON publishing 함수)의 인수를 살펴보면 다른 JSON 함수의 결과값이 인수로 사용되고 있음을 알 수 있습니다. 이 경우 문자열은 이미 형식화된 JSON 데이터로 해석되며, 함수가 문자열의 특수 문자를 이스케이프하지 않음을 의미합니다. 인수가 JSON 함수의 결과값이 아닌 경우, JSON이 아닌 일반 문자 데이터로 해석되고, 그 값에 대해 이스케이프 처리가 수행됩니다. 이 예제에서는 CASE 표현식에 JSON_OBJECT를 임베드했으므로 문자열이 이미 형식화된 JSON이라는 사실은 알 수 없습니다. 이스케이프 시퀀스를 피하기 위해서는, format json 절을 사용하여 인수가 이미 JSON임을 JSON_ARRAY 함수에 명시적으로 알려야 합니다.

아래 구문은 format json을 적용한 SQL문입니다.

select json_array 
  (case when home_phone is not null then 
      json_object('type' value 'home', 
                  'number' value home_phone) end 
      format json,
   case when work_phone is not null then 
      json_object('type' value 'work', 
                  'number' value work_phone) end 
      format json)  
from empdata;

 

결과는 다음과 같습니다. \ (역슬래시) 문자가 사라졌습니다. 

[{"type":"home","number":"555-3762"},{"type":"work","number":"555-7242"}]
[{"type":"work","number":"555-8925"}]
[{"type":"home","number":"555-6312"},{"type":"work","number":"555-4311"}]
[]

 

이제 이 정보는 더 큰 JSON 오브젝트의 한 부분으로 포함할 준비가 되었으니, phones 에 포함시켜보겠습니다.

select json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number, 
                    'phones' value json_array 
                                 (case when home_phone is not null then 
                                     json_object('type' value 'home', 
                                                 'number' value home_phone 
                                                 ) end format json,
                                  case when work_phone is not null then 
                                     json_object('type' value 'work', 
                                                  'number' value work_phone 
                                                  ) end format json)
                     absent on null) 
from empdata;

 

결과는 다음과 같습니다.

{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334",
     "phones":[{"type":"home","number":"555-3762"},{"type":"work","number":"555-7242"}]}

{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216",
     "phones":[{"type":"work","number":"555-8925"}]}

{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739",
     "phones":[{"type":"home","number":"555-6312"},{"type":"work","number":"555-4311"}]}

{"id":904,"name":{"first":"Sally","last":"Smith"},
     "phones":[]}

 

 

4단계. JSON을 하나의 오브젝트로 결합

이제 모든 정보를 하나로 묶어서 하나의 JSON document로 만들어보겠습니다.

select json_object('employees' value json_arrayagg(

       json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number, 
                    'phones' value json_array 
                                 (case when home_phone is not null then 
                                     json_object('type' value 'home', 
                                                 'number' value home_phone 
                                                 ) end format json,
                                  case when work_phone is not null then 
                                     json_object('type' value 'work', 
                                                  'number' value work_phone 
                                                  ) end format json),
                    'accounts' value (select json_arrayagg(
                                                json_object('number' value account)) 
                                      from emp_account a 
                                      where a.id = e.id group by id) format json
                    absent on null))) 
from empdata e;

 

먼저 결과를 JSON_ARRAYAGG로 Wrapping하여 직원 정보의 배열을 작성합니다. 그런 다음 JSON_OBJECT를 사용하여 배열을 Wrapping함으로써 최종 결과는 하나의 JSON 오브젝트가 됩니다. 

{"employees":[
{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334",
      "phones":[{"type":"home","number":"555-3762"},
                {"type":"work","number":"555-7242"}],
      "accounts":[{"number":"36232"},{"number":"73263"}]},
{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216",
      "phones":[{"type":"work","number":"555-8925"}],
      "accounts":[{"number":"76232"},{"number":"72963"}]},
{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739",
      "phones":[{"type":"home","number":"555-6312"},
                {"type":"work","number":"555-4311"}]},
{"id":904,"name":{"first":"Sally","last":"Smith"},"phones":[]}
]}

 

결과 데이터 유형 설정

각 공개 기능을 사용하여 함수 결과의 데이터 유형 및 형식을 지정할 수 있습니다. 지금까지 실행한 위의 예제들과 같이 RETURNING절을 생략하면, 결과값은 길이가 2G 이고 CCSID가 1208인 CLOB 타입의 JSON 데이터가 됩니다. 다른 크기와 타입으로 변경하기를 원한다면, 아래의 예제와 같이 해볼 수 있습니다. 아래 예제는 VARBINARY (2000) 컬럼에서 BSON과 같은 다른 결과가 필요한 경우 RETURNING절을 응용하여 설정한 것입니다.

select json_object ('id' value id, 
                    'name' value last_name, 
                    'office' value office_number
                    returning varbinary(2000) format bson) 
from empdata;