json - Select - Oracle JSON Object - Join

Question

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

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

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.

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

Thanks for the help

Bala

标签： jsonoracle

Answer 1

你可以做这样的事情。json_object请注意对和的多次（嵌套）调用json_arrayagg。在 Oracle 12.2 中测试；其他版本可能有其他工具可以使工作更容易。

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
;