Oracleデータベーステーブルからのjsonの作成
Aug 23 2020
こんにちは私は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;
次に、このテーブルに1つの列を追加する必要があります。

したがって、列にtablename
は「ORG」と「ITEM」の値が含まれます。したがって、2つのファイルを作成する必要があります。1つはitem.jsonで、もう1つはORG.jsonなどです。item.jsonにITEMがあり、ORG.jsonにORGがあるデータを配置する必要があります。上記のクエリでどのような変更を行う必要がありますか。PL / SQLでも問題ありません。上記のクエリで変更を提案できますか?
結果を配列に格納して呼び出し環境に戻ることができれば、それも問題ありません。
回答
3 mathguy Aug 23 2020 at 06:07
これが1つのアプローチです。TABLENAME
列の値を事前に知っている必要はありません。むしろ、クエリ出力には、の一意の値ごとに1つの行がありTABLENAME
、2つの列(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"}]}