sql - Creating json from Oracle database table

Question

Hi I have to create json file from Oracle table. I have data in the below form.

I want data in this format.

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

the below code is working properly.

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;

Now I have to add one extra column in this table.

so column tablename contains "ORG" and "ITEM" values. so I have to create 2 files one would be item.json and another one would be ORG.json and so on. I need to put data which has ITEM in item.json and which has ORG in ORG.json. what changes i need to do in above query. Even PL/SQL would be OK. Can you suggest changed on above query?

It would be also fine if we can store the result into some array and return to calling environment

Answer 1

Here is one approach. You don't need to know the values in the TABLENAME column in advance. Rather, the query output will have one row per unique value in TABLENAME, presented in two columns: the TABLENAME and the corresponding JSON string for that 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"}]}