Is there a way to create a clean JSON file using Oracle SQL?

Question

I am using MS Report Builder, whilst being on Oracle 19c, to convert an SQL query to JSON.

Here is my SQL code:

SELECT json_object(
'id'        VALUE   REFVAL,
'recdate'   VALUE   DATEAPRECV,
'apptype'   VALUE   APPTYP,
'status'    VALUE   STAT,
'person'    VALUE   NAME
)

FROM APPLICATIONS

WHERE APPTYP = 'S10'

And the outcome:

Report
{"id" : "1", "recdate" : "01-01-01", "apptype" : "S10", "status" : "COMP", "person" : "John"}
{"id" : "2", "recdate" : "02-02-02", "apptype" : "S10", "status" : "REG", "person" : "Mary"}

It produces this tabular format which is not ideal.

Ideally, it would be like this.

{"id" : "1", "recdate" : "01-01-01", "apptype" : "S10", "status" : "COMP", "person" : "John"},{"id" : "2", "recdate" : "02-02-02", "apptype" : "S10", "status" : "REG", "person" : "Mary"}

Or

[
   {
    "id" : "1",
    "recdate" : "01-01-01",
    "apptype" : "S10",
    "status" : "COMP",
    "person" : "John"
   },
   {
    "id" : "2",
    "recdate" : "02-02-02",
    "apptype" : "S10",
    "status" : "REG",
    "person" : "Mary"}
]

Is there any way to reproduce this?

Answer 1

You can use JSON_ARRAYAGG() to aggregate all your JSON objects into one array:

select json_arrayagg(
    json_object(
        'id'        value   refval,
        'recdate'   value   dateaprecv,
        'apptype'   value   apptyp,
        'status'    value   stat,
        'person'    value   name
        )
    format json order by refval
) as agg
from applications
where apptyp = 'INVOICE'