I have a parent table and child table with a 1 to 0-many relationship. I need to create a json object that includes parent data and a list of child table data. The goal is an object that includes: "child":[] when there is no matching data in a child table.
If I do a simple outer join and group the parent data, I get: ...,"child":[{"CC":null,"XX":null}] (ex 1).
If I do an outer join to a subquery where I do the aggregation, I get: ...,"child":null (ex 2) which is better, but still not what was requested.
In the third I test for null in a case statement, and in the fourth I use a coalesce function to replace null with []. These result in extra quotes or backslash characters, making the json object invalid or incorrect.
Ex 5 is a kludge that is almost too awful to think about, but it "works."
Using json functions is new to me. Am I missing something simple?
I think you need to do a bit of fiddling to get the output you want. But there is a better way than using replace.
The ABSENT ON NULL clause excludes key with null values:
select json_object (
'this' value null
absent on null
) jo
from dual;
JO
{}If you use this you'll get an array containing an empty object:
select p.pk,
json_object (
'child' value json_arrayagg (
json_object(
'CC' value c.CC,
'XX' value c.XX
absent on null
)
)
) as J
from MY_PARENT_TABLE p
left join MY_CHILD_TABLE c
on c.PK = p.PK
group by p.PK, p.TNAME, p.DEPT;
PK J
1 {"child":[{"CC":20,"XX":22},{"CC":40,"XX":44},{"CC":30,"XX":33}]}
2 {"child":[{}]} Close to what you want, but not quite...
To return an empty array, you could check if JSON_object returns an empty object. And return null instead if it does.
Giving:
select p.pk,
json_object (
'child' value json_arrayagg (
case json_object(
'CC' value c.CC,
'XX' value c.XX
absent on null
)
when '{}' then null
else json_object(
'CC' value c.CC,
'XX' value c.XX
absent on null
)
end
absent on null
)
) as J
from MY_PARENT_TABLE p
left join MY_CHILD_TABLE c
on c.PK = p.PK
group by p.PK, p.TNAME, p.DEPT;
PK J
1 {"child":[{"CC":20,"XX":22},{"CC":40,"XX":44},{"CC":30,"XX":33}]}
2 {"child":[]} I
think that's the best you can manage at the moment.