Hi All,
I use Oracle 19c. I have the following table and data. I am trying to make the following JSON format.
Only the nested invoices should be include to the JSON. In other words, if you consider the data as a tree. I am interested in nested part. If you look at the diagram, it can be seen that cheque 10 and 20 has multiple levels. So, I am only interested in childs which is level greater than 1. Only these part of data should be represented in JSON. For example cheque 30 does not attached.
CREATE TABLE T_TEST
(
INVOICE VARCHAR2(30),
CHEQUE VARCHAR2(30),
LVL NUMBER
);
INSERT INTO T_TEST(INVOICE, CHEQUE, LVL) VALUES('A1', '10', 1);
INSERT INTO T_TEST(INVOICE, CHEQUE, LVL) VALUES('A1', '20', 1);
INSERT INTO T_TEST(INVOICE, CHEQUE, LVL) VALUES('A1', '30', 1);
INSERT INTO T_TEST(INVOICE, CHEQUE, LVL) VALUES('A2', '10', 2);
INSERT INTO T_TEST(INVOICE, CHEQUE, LVL) VALUES('A2', '35', 3);
INSERT INTO T_TEST(INVOICE, CHEQUE, LVL) VALUES('A3', '20', 2);
INSERT INTO T_TEST(INVOICE, CHEQUE, LVL) VALUES('A4', '10', 2);
INSERT INTO T_TEST(INVOICE, CHEQUE, LVL) VALUES('A4', '25', 3);
INSERT INTO T_TEST(INVOICE, CHEQUE, LVL) VALUES('A5', '25', 4);
COMMIT;
{
"invoices" :
{
"invoice_no" : "A1",
"cheques" :
[
{
"cheque_no" : "10",
"invoice_list" :
[
{
"invoice_no" : "A2",
"cheques" :
[
{
"cheque_no" : "35",
"invoice_list" :
[
]
}
]
},
{
"invoice_no" : "A4",
"cheques" :
[
{
"cheque_no" : "25",
"invoice_list" :
[
{
"invoice_no" : "A5",
"invoice_list" :
[
]
}
]
}
]
}
]
},
{
"cheque_no" : "20",
"invoice_list" :
[
{
"invoice_no" : "A3",
"invoice_list" :
[
]
}
]
}
]
}
}
Thanks in advance