JSON 데이터 생성
SQL 함수를 사용하여 관계형 테이블에서 형식화된 JSON 데이터를 생성할 수 있습니다.
JSON을 생성하기 위해 관계형 데이터에 대해 작업할 것입니다. 두 개의 테이블에 있는 데이터로 시작합니다. 하나는 기본 직원 데이터를 포함합니다. 다른 하나는 계정 정보를 포함합니다. 여기에 표시된 대로 작성되고 채워집니다.
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);
JSON_TABLE 예제의 입력으로 사용된 다음 JSON 오브젝트와 유사한 네 가지 직원 정보를 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" } }기본 JSON 오브젝트 빌드
간단한 예제부터 시작하여 기본 JSON 오브젝트를 얼마나 쉽게 생성할 수 있는지 살펴보겠습니다.
select json_object ('id' value id,
'name' value last_name,
'office' value office_number)
from empdata;이 예에서는 JSON_OBJECT 스칼라 함수를 사용하여 JSON 오브젝트를 생성합니다. 이는 값에 대해 EMPDATA 테이블의 ID, LAST_NAME및 OFFICE_NUMBER열을 사용하여 세 개의 키: 값 쌍을 정의합니다. 각 키 이름은 출력에 표시되는 대로 정확하게 문자열로 지정됩니다. 이 조회의 결과는 EMPDATA 테이블의 각 행에 대해 하나씩 네 개의 행입니다.
{"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}이는 최종 목표에 대한 좋은 시작이지만 office 값이 널 (NULL) 인 경우 이 값을 포함하지 않고 생략할 수 있습니다. ABSENT ON NULL절을 사용하여 쉽게 수행할 수 있습니다. 이는 JSON 오브젝트의 값이 널 (NULL) 값인 경우 널 (NULL) key: value 쌍이 결과에 포함되지 않아야 함을 나타냅니다.
select json_object ('id' value id,
'name' value last_name,
'office' value office_number
absent on null)
from empdata;{"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"}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;이제 외부 JSON 오브젝트 내에 중첩된 name 오브젝트가 있는 결과는 다음과 같습니다.
{"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"}}JSON 배열 빌드
다음으로 전화 어레이를 다룰 것입니다. 이는 JSON 오브젝트가 아닌 JSON 배열이어야 합니다. 즉, JSON_ARRAY 스칼라 함수를 사용합니다. 제공되지 않은 전화번호에 대한 배열 항목이 생성되지 않도록 하기 위해 CASE 문이 사용됩니다. 전화번호가 없으면 비어 있는 JSON 배열이 생성됩니다. 집 또는 직장 전화번호를 생성하는지 여부를 알고 있으므로 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 발행 함수 중 하나) 가 해당 인수를 보는 경우 인수가 다른 JSON 함수의 직접 결과인 경우를 인식합니다. 이 경우 문자열은 이미 형식화된 JSON 데이터로 해석됩니다. 이는 함수가 문자열의 특수 문자를 이스케이프하지 않음을 의미합니다. 인수가 JSON 함수의 직접 결과가 아닌 경우에는 비JSON 문자 데이터로 해석되며 값에 대해 이스케이프 처리가 수행됩니다. 이 예에서는 CASE 표현식에 JSON_OBJECT를 임베드했으므로 문자열이 이미 형식화된 JSON이라는 사실을 알 수 없습니다. 이러한 원하지 않는 이스케이프 시퀀스를 방지하려면 FORMAT JSON절을 사용하여 인수가 이미 JSON임을 JSON_ARRAY에 명시적으로 알려야 합니다.
그 지식을 가지고 다시 한번 시도해 보자.
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 오브젝트의 다음 부분으로 포함될 준비가 되었습니다.
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":[]}여러 데이터 행에서 배열 빌드
거의 다 끝났습니다. 별도의 테이블에 저장된 계정 정보를 선택하여 배열에 넣어야 합니다. 계정 번호를 포함하는 오브젝트의 배열을 생성합니다.
select json_array(json_object('number' value account))
from emp_account;[{"number":"36232"}]
[{"number":"73263"}]
[{"number":"76232"}]
[{"number":"72963"}]select json_arrayagg(json_object('number' value account))
from emp_account group by id;[{"number":"36232"},{"number":"73263"}]
[{"number":"76232"},{"number":"72963"}]이 부분은 생성된 JSON 오브젝트를 완료하기 위해 지금까지의 내용에 추가할 수 있습니다. 현재 ID에 대한 EMP_ACCOUNT 테이블의 계정만 리턴하려면 WHERE절이 필요합니다. 또한 계정 배열이 조회에서 리턴되므로 외부 JSON_OBJECT가 이미 형식화된 JSON으로 값을 올바르게 식별하도록 FORMAT JSON절을 추가해야 합니다.
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),
'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;{"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":[]}JSON을 하나의 오브젝트에 결합
한 단계 더 나아갑시다. 모든 정보를 포함하는 단일 JSON 문서가 필요한 경우가 있습니다. 개별 결과를 가져와서 함께 묶을 것입니다.
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;{"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가 됩니다. 이는 FORMAT 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;공통 테이블 표현식을 사용하여 구별 값 집계
각 영업 사원에 대한 영업 정보를 집계하여 영업이 이루어진 각 지역을 나열한다고 가정합니다. SALES 테이블에는 작성된 모든 판매에 대한 항목이 있습니다. 고유한 영업 사원 목록과 각 영업 사원에 대한 지역 목록이 필요합니다. 이는 각각에 대해 공통 테이블 표현식을 사용하여 생성됩니다. 이러한 임시 결과는 각 영업 사원에 대해 하나씩 JSON 오브젝트를 생성하는 데 사용됩니다.
with sales_tmp(sales_person) as (
select distinct(sales_person) from sales),
region_tmp(region,sales_person) as (
select distinct region, sales_person from sales)
select json_object( key sales_person
value (select JSON_ARRAYAGG(region order by region)
from region_tmp r where r.sales_person = s.sales_person)
format json)
FROM sales_tmp s order by sales_person;{"GOUNOT":["Manitoba","Ontario-North","Ontario-South","Quebec"]}
{"LEE":["Manitoba","Ontario-North","Ontario-South","Quebec"]}
{"LUCCHESSI":["Manitoba","Ontario-South","Quebec"]}모든 결과를 포함하는 단일 JSON 오브젝트를 리턴하려는 경우 JSON_OBJECTAGG를 사용하는 다음 쿼리가 이를 생성합니다.
with sales_tmp(sales_person) as (
select distinct(sales_person) from sales),
region_tmp(region,sales_person) as (
select distinct region, sales_person from sales)
select json_objectagg( key sales_person
value (select JSON_ARRAYAGG(region order by region)
from region_tmp r where r.sales_person = s.sales_person)
format json)
FROM sales_tmp s;{"LEE":["Manitoba","Ontario-North","Ontario-South","Quebec"],
"GOUNOT":["Manitoba","Ontario-North","Ontario-South","Quebec"],
"LUCCHESSI":["Manitoba","Ontario-South","Quebec"]}