CODEWITHSUNDEEP
oracle-database
dang
dang

Reputation: 2412

Convert Oracle tables into JSON

I'm using Oracle 12.1 Standard Edition Two and I have the following three tables:

**UserDetailsTable** 

User_ID User_Name User_Age  User_Gender User_City
1       Jon Snow  23        M           Boston

**UserBankDetailsTable**

User_ID User_Bank_Name User_Bank_No User_Account_No User_Current_Balance
1       Bank of Boston 1094         8464541941      33624

**UserTxnTable**

User_ID User_Bank_No Amount Txn_Type Txn_Date 
1       1094         20     Debit    01/12/17
1       1095         50     Credit   02/12/17
1       1096         12     Debit    03/12/17
1       1097         44     Credit   04/12/17
1       1098         55     Debit    05/12/17
1       1099         78     Debit    06/12/17

I am reading these table individually from my API to create the following response by combining the individual responses:

{
"UserDetails":{"User_Name":"Jon Snow", "User_ID":"1", "User_Age":"23","User_Gender":"M", "User_City":"Boston"},
"UserBankDetails":{"User_ID":"1", "User_Bank_Name":"Bank of Boston", "User_Bank_No":"1094", "User_Account_No":"8464541941", "User_Current_Balance":"33624"},
"UserTxnDetails":{
["User_ID":"1", "User_Bank_No":"1094", "Amount":"20", "Txn_Type":"Debit", "Txn_Date":"01/12/17"],
["User_ID":"1", "User_Bank_No":"1094", "Amount":"50", "Txn_Type":"Credit", "Txn_Date":"02/12/17"],
["User_ID":"1", "User_Bank_No":"1094", "Amount":"12", "Txn_Type":"Debit", "Txn_Date":"03/12/17"],
["User_ID":"1", "User_Bank_No":"1094", "Amount":"44", "Txn_Type":"Credit", "Txn_Date":"04/12/17"],
["User_ID":"1", "User_Bank_No":"1094", "Amount":"55", "Txn_Type":"Debit", "Txn_Date":"05/12/17"],
["User_ID":"1", "User_Bank_No":"1094", "Amount":"78", "Txn_Type":"Debit", "Txn_Date":"06/12/17"]
}
}

I want to create this response in one go, without reading the 3 tables individually. I know we can Join the tables and read the data from a single table but as we can see there would be an unnecessary duplication of records as per the transactions of users, which makes it inefficient.

Is there a way in Oracle 12.1 to join and output it as JSON?

Upvotes: 2

Views: 2313

Answers (1)

mcvkr
mcvkr

Reputation: 3912

You can check https://github.com/pljson/pljson and this page Oracle JSON main page https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246

Starting with Oracle 12c (which looks to be your version) your DB installation has support for JSON.

If you can switch to 12.2 you can use this example

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-B0FA4582-762D-4C32-8A0C-265142BD347B which 

SELECT json_object('id'         VALUE mgr.employee_id, 
               'manager'    VALUE (mgr.first_name || ' '|| mgr.last_name),
               'numReports' VALUE count(rpt.employee_id),
               'reports'    VALUE json_arrayagg(rpt.employee_id
                                                ORDER BY rpt.employee_id))
  FROM  employees mgr, employees rpt
  WHERE mgr.employee_id = rpt.manager_id
  GROUP BY mgr.employee_id, mgr.last_name, mgr.first_name
  HAVING count(rpt.employee_id) > 6;

And the result is

    JSON_OBJECT('ID'ISMGR.EMPLOYEE_ID,'MANAGER'VALUE(MGR.FIRST_NAME||''||MGR.LAST_NAME)
--------------------------------------------------------------------------------
{"id":         100,
 "manager":    "Steven King",
 "numReports": 14,
 "reports":    [101,102,114,120,121,122,123,124,145,146,147,148,149,201]}

Upvotes: 1

Related Questions