Generating JSON data
By using SQL functions, you can generate formatted JSON data from relational tables.
We are going to work with relational data to generate JSON. We will start with the data in two tables. One contains basic employee data. The other one contains account information. They are created and populated as shown here.
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);
{"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" } }
Building a basic JSON object
Let's start with a simple example to see how easy it is to generate a basic JSON object.
select json_object ('id' value id,
'name' value last_name,
'office' value office_number)
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","office":null}
select json_object ('id' value id,
'name' value last_name,
'office' value office_number
absent on null)
from empdata;
Now
there is no office value for
904.{"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"}
Nesting JSON objects
select json_object ('first' value first_name,
'last' value last_name)
from empdata;
The
result of this query
is:{"first":"John","last":"Doe"}
{"first":"Peter","last":"Pan"}
{"first":"Mary","last":"Jones"}
{"first":"Sally","last":"Smith"}
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;
{"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"}}
Building a JSON array
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;
Here
is the result. The output for each entry is broken across two lines to show the complete
result.["{\"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\"}"]
[]
This is a bit unexpected. Why are all those extra \ characters in the result? This demonstrates the difference between processing normal character data and character data that has already been formatted as JSON. When JSON_ARRAY (or any of the JSON publishing functions) is looking at its arguments, it recognizes when the argument is the direct result of another JSON function. If it is, the string is interpreted as already formatted JSON data. That means that the function will not escape any of the special characters in the string. If the argument is not the direct result of a JSON function, it is interpreted as non-JSON character data and escape processing is performed on the value. Since this example embedded the JSON_OBJECT in a CASE expression, the fact that the string was already formatted JSON is not known. To avoid these unwanted escape sequences, you need to explicitly tell JSON_ARRAY that the argument is already JSON by using the FORMAT JSON clause.
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;
Now
the result
is:[{"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"}]
[]
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;
This
returns:{"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":[]}
Building an array from multiple rows of data
select json_array(json_object('number' value account))
from emp_account;
The
result of this query
is:[{"number":"36232"}]
[{"number":"73263"}]
[{"number":"76232"}]
[{"number":"72963"}]
That
generated one array for each account number. What we need is one array for each
ID value. That requires aggregating all the number
JSON objects for a single ID value into a single
array.select json_arrayagg(json_object('number' value account))
from emp_account group by id;
JSON_ARRAYAGG
is an aggregate function that works on groups of data. In this case, we are grouping on the
ID column. Each account for an ID generates an object,
then all of those objects are aggregated into a single array. This query returns only two rows, one
for each ID value which is exactly what we were looking
for.[{"number":"36232"},{"number":"73263"}]
[{"number":"76232"},{"number":"72963"}]
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;
Now
we have arrived at our final
result.{"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":[]}
Combining JSON into one object
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;
First
we wrap the results with a JSON_ARRAYAGG to create an array of employee information. Then we wrap
the array with a JSON_OBJECT so the final result is a single JSON object. Now the result is one JSON
object.{"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":[]}
]}
Using a common table expression to aggregate distinct values
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;
From
the sample table, the following three rows are
returned:{"GOUNOT":["Manitoba","Ontario-North","Ontario-South","Quebec"]}
{"LEE":["Manitoba","Ontario-North","Ontario-South","Quebec"]}
{"LUCCHESSI":["Manitoba","Ontario-South","Quebec"]}
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;
This
returns one row containing one JSON object. It is broken into several lines here to make it easy to
read.{"LEE":["Manitoba","Ontario-North","Ontario-South","Quebec"],
"GOUNOT":["Manitoba","Ontario-North","Ontario-South","Quebec"],
"LUCCHESSI":["Manitoba","Ontario-South","Quebec"]}