Generating json file from table in Oracle PL/SQL

Question

I have data in the below form in table.

I want to generate data in the below format.

{
    "delete": [
        {
            "canonicalName": "TCS"          
        }
    ],
    "update": [
         {
            "canonicalName": "Wipro tech"
            "synonyms": [
                "Wipro technology"
            ]
         },
         {
            "canonicalName": "Infosys"
            "synonyms": [
                "Infosys tech"
            ]
         }
    ]       
    "add": [
        {
            "canonicalName": "Apple Computers",
            "synonyms": [
                "Apple"
            ]
        },
        {
            "canonicalName": "Google India",
            "synonyms": ["Google"]
        }
    ]
}

I can write same in java using some libraries and POJO class. Is there any possibility I can create in Oracle PL/SQL? earlier I was reading data from jdbc and creating this file in java code. Now I have to keep this code as part of pl/sql procedure. Is there anyway i can generate data in the above format?

Answer 1

In your output, when an organization only has a canonical name (no synonyms), you show the corresponding object with only one property, the canonical name. It would be easier if you allowed the "synonyms" property to exist in all cases - the corresponding array can be empty - but that is not what you show in your desired output.

If you allow empty arrays as the value of "synonyms" then Barbaros Ozhan provided the solution already. EDIT Upon inspection, that may not be entirely true; the solution doesn't aggregate synonyms for a single organization into a single array (with two or more synonyms), showing them instead as distinct objects. See the sample data I added to my Answer, below. END EDIT

MASSIVE EDITS HERE I am adding sample data for testing. I assume that if an organization has two or more synonyms (under the same operation), that should result in a single member of the operation array (the canonical name appears only once, with an array of two or more synonyms).

I modified my solution too - found a simpler way. One can see the change to the solution by clicking on the "edited" link below the answer.

Test data:

drop table t purge;
create table t (orgname, synonyms, operation) as
  select 'Apple Computers', 'Apple'           , 'add'    from dual union all
  select 'Apple Computers', 'Apple Inc'       , 'add'    from dual union all
  select 'Google India'   , 'Google'          , 'add'    from dual union all
  select 'Wipro Tech'     , 'Wipro Technology', 'update' from dual union all
  select 'Infosys'        , 'Infosys Tech'    , 'update' from dual union all
  select 'TCS'            , null              , 'delete' from dual union all
  select 'IBM'            , null              , 'delete' from dual union all
  select 'IBM'            , 'IBM Corporation' , 'delete' from dual union all
  select 'IBM'            , 'IBM Corporation' , 'add'    from dual
;

Query:

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
;

Output (pretty printed):

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

Answer 2

If your DB version is 12cR2+, then you can use json_objectagg(), json_object() and json_array() functions together as

SELECT json_query(
       json_objectagg
                 ( operation VALUE 
                   json_array(json_object('canonicalName' VALUE orgname,
                                          'synonyms'      VALUE json_array(synonyms) ) ) ),
       '$' returning VARCHAR2(4000) pretty )
    AS "Result JSON" 
  FROM tab

Don't forget to add json_query( ... , '$' returning VARCHAR2(4000) pretty ) wrapping whole expression to get a nice appearance.

Demo