0

I have data in the below form in table.

enter image description here

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?

Improve this question
4
  • 2
    What's your oracle version? Is it >=12.2? Commented Aug 22, 2020 at 12:32
  • Suppose you have one more row, with ORGNAME = 'Apple Computers', SYNONYMS = 'Apple Inc', OPERATION = 'add'. What is the desired structure in that case? Will two distinct elements with canonicalName = 'Apple Computers' appear in the array for "add", each with exactly one synonym? Or will they be combined into a single element with canonicalName = 'Apple Computers', with all the synonyms collected into a single (possible multi-element) array of synonyms? Commented Aug 22, 2020 at 15:53
  • @SayanMalakshinov yes it is 18c Commented Aug 22, 2020 at 18:16
  • Canonical name would be unique Commented Aug 22, 2020 at 18:17

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

If my assumption (described below) is correct, then your solution doesn't quite do it. Namely: suppose that in the inputs, for the same "operation" and same "orgname", there are several synonyms. Then I assume (the OP needs to clarify) that the "synonyms" property must be a single array, aggregating all the synonyms for that organization. In your solution, the same orgname will appear multiple times under the same "operation".
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"
      ]
    }
  ]
}
Improve this answer

2 Comments

First of all thank you so much @mathguy for such an amazing answer. situation is there can be more than one synony and if synony is null we can remove synoym tab,
Hi @Mathguy I have extended my requirement little bit. your solution is perfect for me. Could you please help me to do changes please? stackoverflow.com/questions/63541003/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.