从关系数据库表构建自定义json

rqcrx0a6  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(369)

我有一个表,这个表有几列。我想从这些数据构建一个json。这是表格结构。

JSON_KEY                COLUMNNAME

Acreage12               accreage
Farmer_projected        sellingprice
Projected_Expense       attr1
Pattern1                crop


预期json:-

{
  "Agriculture_Expenses": [
    {
      "Acreage12": "4.0",
      "Farmer_projected": "40000.00",
      "Projected_Expense": "76230.00",
      "Pattern1": "Khariff"
    },
    {
      "Acreage12": "4.0",
      "Farmer_projected": "40000.00",
      "Projected_Expense": "50820.00",
      "Pattern1": "Rabi"
    },
    {
      "Acreage12": "4.0",
      "Farmer_projected": "40000.00",
      "Projected_Expense": "63000.00",
      "Pattern1": "Zaid"
    }
  ]
}
nhn9ugyo

nhn9ugyo1#

你可以用 JSON_OBJECT() 连续与 JSON_ARRAYAGG() db版本的第二步中嵌入的函数 12.2+ :

SELECT JSON_OBJECT( 'Agriculture_Expenses' value
                    JSON_ARRAYAGG(JSON_OBJECT ('Acreage12'         value accreage,
                                               'Farmer_projected'  value sellingprice,
                                               'Projected_Expense' value attr1,
                                               'Pattern1'          value crop
                                               )
                                  )
                    ) AS "Result JSON"
  FROM t

演示

jbose2ul

jbose2ul2#

如果你在12摄氏度及以上,使用 JSON_OBJECT 接线员。例如:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> with emp (empno, ename, job, sal) as
  2    (select 1, 'Little', 'Salesman', 1250 from dual union all
  3     select 2, 'Foot'  , 'Clerk'   ,  980 from dual union all
  4     select 3, 'Scott' , 'Manager' , 1580 from dual
  5    )
  6  select json_object ('id'       value empno,
  7                      'name'     value ename,
  8                      'position' value job,
  9                      'salary'   value sal
 10                     ) json
 11  from emp;

JSON
--------------------------------------------------------------------------------
{"id":1,"name":"Little","position":"Salesman","salary":1250}
{"id":2,"name":"Foot","position":"Clerk","salary":980}
{"id":3,"name":"Scott","position":"Manager","salary":1580}

SQL>

如果您使用的是较低的数据库版本,则必须手动执行。

相关问题