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"}]}