嗨,我必须从Oracle表创建json文件。我有以下表格的数据。
我想要这种格式的数据。
{
"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"
]
}
]
}
下面的代码正常工作。
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;
现在,我必须在此表中添加一列。
因此列tablename
包含“ ORG”和“ ITEM”值。所以我必须创建2个文件,一个是item.json,另一个是ORG.json,依此类推。我需要将具有ITEM的数据放在item.json中,并将具有ORG的数据放在ORG.json中。我需要在上面的查询中做哪些更改。即使是PL / SQL也可以。您能否建议对上述查询进行更改?
如果我们可以将结果存储到某个数组中并返回到调用环境,那也很好
这是一种方法。您无需TABLENAME
事先知道列中的值。相反,查询输出中的中的每个唯一值将有一行TABLENAME
,分为两列:TABLENAME和该TABLENAME的对应JSON字符串。
with
prep1 (tablename, operation, orgname, fragment) as (
select tablename, 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 tablename, orgname, operation
)
, prep2 (tablename, operation, org_str) as (
select tablename, operation, json_arrayagg(fragment order by orgname)
from prep1
group by tablename, operation
)
select tablename, json_objectagg(key operation value org_str) as json_str
from prep2
group by tablename
;
TABLENAME JSON_STR
--------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ITEM {"add":[{"canonicalName":"Apple Computers","synonyms":["Apple","Apple Inc"]},{"canonicalName":"Google India","synonyms":["Google"]},{"canonicalName":"IBM","synonyms":["IBM Corporation"]}],"update":[{"canonicalName":"Infosys","synonyms":["Infosys Tech"]},{"canonicalName":"Wipro Tech","synonyms":["Wipro Technology"]}],"delete":[{"canonicalName":"IBM","synonyms":["IBM Corporation"]},{"canonicalName":"TCS"}]}
ORG {"add":[{"canonicalName":"Apple Computers","synonyms":["Apple","Apple Inc"]},{"canonicalName":"Google India","synonyms":["Google"]},{"canonicalName":"IBM","synonyms":["IBM Corporation"]}],"update":[{"canonicalName":"Infosys","synonyms":["Infosys Tech"]},{"canonicalName":"Wipro Tech","synonyms":["Wipro Technology"]}],"delete":[{"canonicalName":"IBM","synonyms":["IBM Corporation"]},{"canonicalName":"TCS"}]}
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句