Development and testing done on Oracle 12.2.0.1, not patched in any way.
Problem:
Given a hierarchical dataset (a single-rooted tree with additional information at each node), generate a single JSON string representing that data.
The input may be a single table, something like this (copied from odie_63 with two small changes: I made one of the "names" NULL and I have spaces in the names, to see if the code can handle correctly):
drop table h purge;
create table h (id number primary key, parent_id number, name varchar2(10));
alter table h modify (parent_id references h);
insert into h
select 1, null, 'Item 1' from dual union all
select 2, 1, 'Item 2' from dual union all
select 3, 1, 'Item 3' from dual union all
select 4, 3, null from dual union all
select 5, 4, 'Item 5' from dual
;
commit;
select * from h;
ID PARENT_ID NAME
1 Item 1
2 1 Item 2
3 1 Item 3
4 3
5 4 Item 5
The desired output is a single JSON object - represented as either CLOB or BLOB, it doesn't matter much; I chose CLOB for this post, although there are some non-trivial advantages to using BLOB. All of that is unrelated to the problem discussed here.
For the data shown above, the desired output looks like this. Formatted for easier reading, but that's not part of the requirement; in fact my solution below doesn't format the output, it just generates the correct JSON but without the pretty printing.
HJ
{
"id" : 1,
"name" : "Item 1",
"children" :
[
{
"id" : 2,
"name" : "Item 2"
},
{
"id" : 3,
"name" : "Item 3",
"children" :
[
{
"id" : 4,
"name" : null,
"children" :
[
{
"id" : 5,
"name" : "Item 5"
}
]
}
]
}
]
}
Other approaches:
@odie-63 wrote a (user-defined) aggregate function for this, see https://odieweblog.wordpress.com/2018/06/17/hierarchical-json-aggregation-the-jsonnest-function/
@stew-ashton and @paulzip proposed SQL-only solutions in an older thread on this forum; paulzip combined the solutions, showing the result in that thread and also on his blog, see https://paulzipblog.wordpress.com/2020/04/29/hierarchical-json-structures/
A link to the old thread from this forum is included at the top of paulzip's blog post.
What I am proposing here:
The problem is a perfect application of the general concept of "recurrence" in programming. Let me note in passing that "recursive WITH clause" is not an implementation of "recurrence" - it is rather a clever and advanced implementation of "iteration". There is no true "recurrence" in plain SQL - a function calling itself until reaching base cases that don't require further self-calls. In this thread I propose a recursive solution to generating the JSON from the hierarchical data.
I am posting this for review and critique; I haven't run any performance tests to see if the solution is any good, since I suspect there may be improvements that more experienced readers will be able to spot right away. In particular, this is the first time I've done anything with the PL/SQL JSON data types (json_*_t).
To keep posts to a reasonable length, I will describe the solution in the first "reply" below.
NOTE: to get to the "first reply below" one needs to skip over the accepted answer(s) first!