Oracle 12c JSON

Question

I have two tables. Employee and Loans. The latter has a foreign reference to Employee. One employee can have multiple loans. In my simplified example we only have one type of loan "creditLoan".

I wish to structure the loans per employee into a JSON format like this:

{
    "employeeID": "10001",
    "instID": "123456789",
    "loans": [
       {
          "creditLoan": 
             {
               "id":"123",
               "amount":"-20000"
             }
       },
       {
          "creditLoan": 
             {
               "id":"234",
               "amount":"-30000"
             }
       }       
    ]
}

So far I have only gotten to this:

SELECT JSON_OBJECT(
          'employeeID' VALUE E.ID, 
          'instID' VALUE '123456789',
          'loans' VALUE (
               SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                         'creditLoan' VALUE  (
                              SELECT JSON_ARRAYAGG (
                                   JSON_OBJECT(
                                        KEY 'id' VALUE L.ID,  
                                        KEY 'amount' VALUE L.AMOUNT)
                                   )

                               FROM LOANS L WHERE L.EMP_ID=E.ID)))
          FROM DUAL))
FROM EMPLOYEE E ;

Generates the following JSON:

{  
   "employeeID":"1001",
   "instID":"123456789",
   "loans":[  
      {  
         "creditLoan":[  
            {  
               "id":"123",
               "amount":"-20000"
            },
            {  
               "id":"234",
               "amount":"-30000"
            }           
         ]
      }
   ]
}

I wish to avoid having an inner array (creditLoan) inside outer array (loans). Each loan object shall have a "creditLoan" key.

Also, what is an efficient way to combine multiple employee JSON structures into a single clob? Now I get one row for each employee.

Answer 1

You don't need the inner selects, and you have one more layer of aggregation that you want:

-- CTEs for sample data
with employee (id) as (
  select 1001 from dual
),
loans (id, emp_id, amount) as (
  select 123, 1001, -20000 from dual
  union all
  select 456, 1001, -30000 from dual
)
-- actual query
SELECT JSON_OBJECT (
  'employeeID' VALUE E.ID, 
  'instID' VALUE '123456789',
  'loans' VALUE (
    JSON_ARRAYAGG (
      JSON_OBJECT (
        'creditLoan' VALUE (
          JSON_OBJECT (
            KEY 'id' VALUE L.ID,  
            KEY 'amount' VALUE L.AMOUNT
          )
        )
      )
    )
  )
)
FROM EMPLOYEE E
JOIN LOANS L ON L.EMP_ID=E.ID
GROUP BY E.ID;

which gets

{  
  "employeeID":1001,
  "instID":"123456789",
  "loans":[  
    {  
      "creditLoan":{  
        "id":123,
        "amount":-20000
      }
    },
    {  
      "creditLoan":{  
        "id":456,
        "amount":-30000
      }
    }
  ]
}

If you want multiple employees in a single JSON result then you'll need to add another aggregation layer above this; something like:

-- CTEs for sample data
with employee (id) as (
  select 1001 from dual
  union all
  select 1002 from dual
),
loans (id, emp_id, amount) as (
  select 123, 1001, -20000 from dual
  union all
  select 456, 1001, -30000 from dual
  union all
  select 789, 1002, -10000 from dual
)
-- actual query
SELECT JSON_OBJECT (
  'employees' VALUE (
    JSON_ARRAYAGG (
      JSON_OBJECT (
        'employeeID' VALUE E.ID, 
        'instID' VALUE '123456789',
        'loans' VALUE (
          JSON_ARRAYAGG (
            JSON_OBJECT (
              'creditLoan' VALUE (
                JSON_OBJECT (
                  KEY 'id' VALUE L.ID,  
                  KEY 'amount' VALUE L.AMOUNT
                )
              )
            )
          )
        )
      )
    )
  )
)
FROM EMPLOYEE E
JOIN LOANS L ON L.EMP_ID=E.ID
GROUP BY E.ID;

which gets

{  
  "employees":[  
    {  
      "employeeID":1001,
      "instID":"123456789",
      "loans":[  
        {  
          "creditLoan":{  
            "id":123,
            "amount":-20000
          }
        },
        {  
          "creditLoan":{  
            "id":456,
            "amount":-30000
          }
        }
      ]
    },
    {  
      "employeeID":1002,
      "instID":"123456789",
      "loans":[  
        {  
          "creditLoan":{  
            "id":789,
            "amount":-10000
          }
        }
      ]
    }
  ]
}