Membuat json dari tabel database Oracle

Question

Hai, saya harus membuat file json dari tabel Oracle. Saya memiliki data dalam formulir di bawah ini.

Saya ingin data dalam format ini.

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

kode di bawah ini berfungsi dengan baik.

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;

Sekarang saya harus menambahkan satu kolom ekstra di tabel ini.

jadi kolom tablenameberisi nilai "ORG" dan "ITEM". jadi saya harus membuat 2 file satu akan menjadi item.json dan satu lagi akan menjadi ORG.json dan seterusnya. Saya perlu meletakkan data yang memiliki ITEM di item.json dan yang memiliki ORG di ORG.json. perubahan apa yang perlu saya lakukan di kueri di atas. Bahkan PL / SQL akan baik-baik saja. Bisakah Anda menyarankan perubahan pada kueri di atas?

Juga akan baik-baik saja jika kita dapat menyimpan hasilnya ke dalam beberapa larik dan kembali ke lingkungan pemanggilan

Answer 1

Inilah satu pendekatan. Anda tidak perlu mengetahui nilai di TABLENAMEkolom sebelumnya. Sebaliknya, keluaran kueri akan memiliki satu baris per nilai unik TABLENAME, disajikan dalam dua kolom: TABLENAME dan string JSON yang sesuai untuk TABLENAME tersebut.

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