Hi All,
I have two JSON which I am generating using SQL functions, now I want to combine them in one JSON. Here is my test script
create table test_tbl(id number, name varchar2(1000), parent_id number);
create table test_tb1(term_id number, term_description varchar2(100));
insert into test_tbl values(1, 'Test1');
insert into test_tbl values(2, 'Test2');
insert into test_tbl values(3, 'Test3', 1);
insert into test_tbl values(4, 'Test4', 2);
insert into test_tb1 values(1, 'test description');
The JSON query are as follows
select json_object('Data' VALUE json_objectagg(name value JSON_OBJECT(
'VALUE' VALUE to_char(id),
'PARAMS' VALUE (SELECT json_arrayagg(
json_object('id' VALUE T1.id,
'name' VALUE T1.name))
from test_tbl T1 where T1.parent_id is not null
and T1.parent_id = T.ID)
)))
from test_tbl T
where T.parent_id is null;
The output of the above is
{"Data":{"Test1":{"VALUE":"1","PARAMS":[{"id":3,"name":"Test3"}]},"Test2":{"VALUE":"2","PARAMS":[{"id":4,"name":"Test4"}]}}}
The second table JSON query
select json_objectagg('TC' VALUE JSON_OBJECT('VALUE' VALUE term_description)) from test_tb1;
The output of it is
{"TC":{"VALUE":"test description"}}
Now, my requirement is to combine this two JSON and generate final JSON as follows
{
"Data": {
"Test1": {
"VALUE": "1",
"PARAMS": [
{
"id": 3,
"name": "Test3"
}
]
},
"Test2": {
"VALUE": "2",
"PARAMS": [
{
"id": 4,
"name": "Test4"
}
]
},
"TC": {
"VALUE": "test description"
}
}
}
Is it possible to achieve this?
As requested in the comments, I have added an exact table structure.