JSON: How to build master detail response for a rest service

juliojgs

Hi, this question is probably very basic, but I'm new to all this .... I just want to build a rest service that has to return a json with three level master detail query.

How would you do this?

EJ-Egyed

Can you provide an example of JSON that you are attempting to generate? Is this what you are attempting to create?

https://stackoverflow.com/questions/71695048/oracle-rest-api-multiple-rows/71695402#71695402

Hpaiss

Hi,

there are at least two ways I'm using:

1. Cursors in the select - as already suggested - I use it only with very small resultsets.
2. Query with json functions - follow very simplified example without arrays

select json_object(

        'Cust'     VALUE c.cust

        ,'First Name'  VALUE c.firstname

        ,'Last Name'  VALUE c.lastname

        ) 

        , 'Address'   VALUE    json_object('City'     VALUE c.cityname                    , 'Street'   VALUE c.address     )

        ) Response

    from customers c

    where c.custname = '1234567'

EJ-Egyed

The query that you provided is not valid SQL. Can you share an example JSON that you are attempting to generate?

Hpaiss

Just from docs :

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

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;

EJ-Egyed

This query is valid, but does not describe the problem you are having. Please share some data as it exists in your database and the format of the JSON you are hoping to retrieve that data in.

juliojgs

thanks for your help, I think I can explore that options. Probably will try the json agg functions.

I try to write an example: A three level master detail information: department, employee, task

{ 
  "department": "Accounting", 
  "employees": [ { 
    "name": "Shelley,Higgins", 
    "job": "Accounting Manager", 
    "tasks": [ {
       "name": "Proyect x",
       "hours": 80}, {
       "name": "Proyect zzzz",
       "hours": 200}
             ]
  }, { 
    "name": "William,Gietz", 
    "job": "Public Accountant" , 
    "tasks": [ {
       "name": "campaign ccc",
       "hours": 30}, {
       "name": "Proyect zzzz",
       "hours": 200}
             ]
  } ] 
}

I'd like a sql query to generate a json like this, but returning all the departments, not just Accounting.

Hpaiss

Hi,

Look at the following examles at oracle-base: https://oracle-base.com/articles/12c/sql-json-functions-12cr2

It has two level nested json but in a very clear way that you can implement easily to three levels.

Just remember that by default ORDS will escape the json, so you have to add a columns for the media type as 'application/json' or set the column aliasto '{}json' so, it will not be escaped.

Regards,

Hadar

thatJeffSmith-Oracle

Just remember that by default ORDS will escape the json

Until 21c that is, then once we have a native JSON Data Type, no magic is required for clean output.