CODEWITHSUNDEEP
jsonoracle-database
Balaji Krishnan
Balaji Krishnan

Reputation: 457

Select - Oracle JSON Object - Join

I have a requirement to select column values in Oracle in a JSON structure. Let me explain the requirement in detail We have a table called "dept" that has the following rows

enter image description here

There is another table called "emp" that has the following rows

enter image description here

The output we need is as follows

{"Data": [{
 "dept": "Sports",
 "City": "LA",
 "employees": {
"profile":[
  {"name": "Ben John", "salary": "15000"},
  {"name": "Carlos Moya", "salary": "19000"}]
}},
{"dept": "Sales",
 "City": "Miami",
 "employees": {
"profile":[
  {"name": "George Taylor", "salary": "9000"},
  {"name": "Emma Thompson", "salary": "8500"}]
  }}
]
}

The SQL that I issued is as follows

select  json_object('dept' value b.deptname,
                'city' value b.deptcity,
                'employees' value json_object('employee name' value a.empname,
                                                    'employee salary' value a.salary)
                format json) as JSONRETURN
from emp a, dept b where
a.deptno=b.deptno

However, the result looks like the following and not what we expected.

enter image description here

Please note the parent data is repeated. What is the mistake I am making?

Thanks for the help

Bala

Upvotes: 2

Views: 1212

Answers (1)

user5683823
user5683823

Reputation:

You can do something like this. Note the multiple (nested) calls to json_object and json_arrayagg. Tested in Oracle 12.2; other versions may have other tools that can make the job easier.

select json_object(
         'Data' value
           json_arrayagg(
             json_object (
               'dept'      value deptname,
               'City'      value deptcity,
               'employees' value
                 json_object(
                   'profile' value
                     json_arrayagg(
                       json_object(
                         'name'   value empname,
                         'salary' value salary
                       ) order by empid  -- or as needed
                     )      
                 )
             ) order by deptno  -- or as needed
           )
       ) as jsonreturn
from   dept join emp using (deptno)
group  by deptno, deptname, deptcity
;

Upvotes: 4

Related Questions