从Oracle数据库表创建json [英] Creating json from Oracle database table

问题描述

我必须从Oracle表创建json文件.我的数据格式如下.

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

以下代码正常运行.

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.

因此列tablename包含"ORG"和"ITEM"价值观.所以我必须创建2个文件，一个是item.json，另一个是ORG.json，依此类推. 我需要将具有ITEM的数据放在item.json中，并将具有ORG的数据放在ORG.json中. 我需要在上面的查询中做哪些更改. 即使是PL/SQL也可以.您可以建议对上述查询进行更改吗?

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

推荐答案

这是一种方法.您不需要事先知道TABLENAME列中的值.相反，查询输出将在TABLENAME中每个唯一值一行一行，并在两列中显示:TABLENAME和该TABLENAME的对应JSON字符串.

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

这篇关于从Oracle数据库表创建json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！