Hi I have to create json file from Oracle table. I have data in the below form.
I want data in this format.
{
"add" :
[
{
"canonicalName" : "Apple Computers",
"synonyms" :
[
"Apple",
"Apple Inc"
]
},
{
"canonicalName" : "Google India",
"synonyms" :
[
"Google"
]
},
{
"canonicalName" : "IBM",
"synonyms" :
[
"IBM Corporation"
]
}
],
"delete" :
[
{
"canonicalName" : "IBM",
"synonyms" :
[
"IBM Corporation"
]
},
{
"canonicalName" : "TCS"
}
],
"update" :
[
{
"canonicalName" : "Infosys",
"synonyms" :
[
"Infosys Tech"
]
},
{
"canonicalName" : "Wipro Tech",
"synonyms" :
[
"Wipro Technology"
]
}
]
}
the below code is working properly.
with
prep (operation, orgname, fragment) as (
select operation, orgname,
json_object( key 'canonicalName' value orgname,
key 'synonyms'
value nullif(json_arrayagg(synonyms order by synonyms), '[]')
FORMAT JSON ABSENT ON NULL
)
from t
group by orgname, operation
)
select json_objectagg( key operation
value json_arrayagg(fragment order by orgname)
) as json_str
from prep
group by operation;
Now I have to add one extra column in this table.
so column tablename
contains "ORG" and "ITEM" values. so I have to create 2 files one would be item.json and another one would be ORG.json and so on.
I need to put data which has ITEM in item.json and which has ORG in ORG.json.
what changes i need to do in above query.
Even PL/SQL would be OK. Can you suggest changed on above query?
It would be also fine if we can store the result into some array and return to calling environment
See Question&Answers more detail:os