Creazione di json dalla tabella del database Oracle

Question

Ciao devo creare il file json dalla tabella Oracle. Ho i dati nel modulo sottostante.

Voglio i dati in questo formato.

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

il codice seguente funziona correttamente.

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;

Ora devo aggiungere una colonna in più in questa tabella.

quindi la colonna tablenamecontiene i valori "ORG" e "ITEM". quindi devo creare 2 file uno sarebbe item.json e un altro sarebbe ORG.json e così via. Devo inserire i dati che hanno ITEM in item.json e che hanno ORG in ORG.json. quali modifiche devo fare nella query sopra. Anche PL / SQL andrebbe bene. Puoi suggerire cambiato sulla query sopra?

Sarebbe anche bello se potessimo memorizzare il risultato in un array e tornare all'ambiente chiamante

Answer 1

Ecco un approccio. Non è necessario conoscere TABLENAMEin anticipo i valori nella colonna. Piuttosto, l'output della query avrà una riga per valore univoco in TABLENAME, presentato in due colonne: TABLENAME e la stringa JSON corrispondente per quel TABLENAME.

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