1

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

enter image description here

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.

enter image description here

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

Improve this question
5
  • 1
    wouldn't you just add a WHERE condition to your select "WHERE TABLENAME='ITEM', Or perhaps WHERE TABLENAME=<some parameter, either ITEM or ORG'. Also using the same parametr in your logic to decide which file you are creating. Commented Aug 22, 2020 at 22:03
  • Actually Ed there can be n number of entries like ITEM, ORG,FEATURES so i was looking for perfect answer. I can create code which give output but may not be proper.so i am creating code for a product. so trying my best to write proper code. I am actually a java developer . Commented Aug 22, 2020 at 22:08
  • I added parameter in logic so it started giving error that it is not matching with select query and with. and later it gave some weired output. Tell me one thing can it be done without using cursor? Commented Aug 22, 2020 at 22:10
  • 1
    You probably added the where clause in the wrong place. Find the subquery named prep in my code (in the with clause). Find the from clause: from t. Right after it, add the where clause: ... from t WHERE tablename = :tablename ... Here :tablename (with the leading colon) is a bind variable; you can give it values like 'ITEM', 'ORG', etc. Commented Aug 22, 2020 at 22:50
  • 1
    "any number of values" ... well, I just used the two you showed as an example. If you take my suggestion to write your query so that you feed a parameter from the caller, then it will work for any value you feed it. Commented Aug 23, 2020 at 12:52

1 Answer 1

Reset to default
3

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"}]}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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