Hello All,
I want to change the following query result.
SELECT
JSON_SERIALIZE(
JSON_OBJECT(
KEY 'companyid' VALUE COMPANY_ID,
key 'invoice' VALUE
JSON_OBJECTAGG(
'I dont want this tag' value
json_object(
KEY 'refNo' VALUE INVOICE_NO,
key 'cheques' VALUE JSON_ARRAY(
JSON_OBJECT(KEY 'cheque_no' VALUE CHEQUE_NO)
)
)
RETURNING CLOB)
RETURNING CLOB)
RETURNING CLOB PRETTY)
FROM (
SELECT 'A1' INVOICE_NO, '10' CHEQUE_NO, '101010' COMPANY_ID FROM DUAL
UNION ALL
SELECT 'A1' INVOICE_NO, '20' CHEQUE_NO, '101010' COMPANY_ID FROM DUAL
UNION ALL
SELECT 'A2' INVOICE_NO, '20' CHEQUE_NO, '101010' COMPANY_ID FROM DUAL
) GROUP BY COMPANY_ID;
{
"companyid" : "101010",
"invoice" :
{
"I dont want this tag" :
{
"refNo" : "A1",
"cheques" :
[
{
"cheque_no" : "10"
}
]
},
"I dont want this tag" :
{
"refNo" : "A2",
"cheques" :
[
{
"cheque_no" : "20"
}
]
},
"I dont want this tag" :
{
"refNo" : "A1",
"cheques" :
[
{
"cheque_no" : "20"
}
]
}
}
}
I want to display below output and order by refno. I use Oracle 19c.
{
"companyid" : "101010",
"invoice" :
{
"refNo" : "A1",
"cheques" :
[
{
"cheque_no" : "10"
}
],
"refNo" : "A2",
"cheques" :
[
{
"cheque_no" : "20"
}
],
"refNo" : "A1",
"cheques" :
[
{
"cheque_no" : "20"
}
]
}
}
Thanks