I'm trying to use JSON_ARRAYAGG to create a nested JSON list from two/more output rows. How to re-write my query to get desired output? I tried adding JSON_ARRAYAGG but the output is erroring "ORA-40478: output value too large (maximum: 4000)" then I tried using RETURNING CLOB function, even after that it is throwing error. I also tried TO_CLOB(JSON_OBJECT()) but still not working. Finally I tried DBMS_LOB package but nothing is working.

SQL results without JSON_OBJECT:

col1	col2	col3	col5	col6	col7
12345	1	654321	test	2345	14
12345	1	765432	test	2345	14

I'm trying to use JSON_ARRAYAGG to create a nested JSON list from two/more output rows. How to re-write my query to get desired output? I tried adding JSON_ARRAYAGG but the output is erroring "ORA-40478: output value too large (maximum: 4000)" then I tried using RETURNING CLOB function, even after that it is throwing error. I also tried TO_CLOB(JSON_OBJECT()) but still not working. Finally I tried DBMS_LOB package but nothing is working.

SQL results without JSON_OBJECT:

col1	col2	col3	col5	col6	col7
12345	1	654321	test	2345	14
12345	1	765432	test	2345	14

JSON_OBJECT query:

SELECT DISTINCT JSON_OBJECT(
    'col1' VALUE mytable.col1,
    'col2' VALUE '1',
    'col3' VALUE TO_CHAR(table2.col3),
    'col4' VALUE JSON_OBJECT(
        'col5' VALUE 'test',
        'col6' VALUE TO_CHAR(mytable.col1)
        ABSENT ON NULL
    ),
    'col7' VALUE ( 
        SELECT FLOOR(MAX(days)/30)
        FROM schema1.table1
        WHERE table1.test = mytable.test
    )
ABSENT ON NULL ) AS output
FROM 
    mytable
    join table 2 on mytable.col1=table2.col1

Current JSON output:

{
    "col1": 12345,
    "col2": "1",
    "col3": "654321",
    "col4": {
        "col5": "test",
        "col6": "2345"
    },
    "col7": 14
}
{
    "col1": 12345,
    "col2": "1",
    "col3": "765432",
    "col4": {
        "col5": "test",
        "col6": "2345"
    },
    "col7": 14
}

Desired JSON output:

{
    "col1": 12345,
    "col2": "1",
    "col3": [ "654321", "765432"],
    "col4": {
        "col5": "test",
        "col6": "2345"
    },
    "col7": 14
}

• json
• oracle-database
• json-arrayagg

Share Improve this question Follow edited 2 days ago Matt Kantor 2,06511 gold badge2121 silver badges4040 bronze badges asked Mar 28 at 14:51 Anupa ReddyAnupa Reddy 1

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Without sample data it is difficult to tell but maybe moving the join to table2 to an aggregated sub-query:

SELECT JSON_OBJECT(
         'col1' VALUE col1,
         'col2' VALUE col2,
         'col3' VALUE (
           SELECT JSON_ARRAYAGG(TO_CHAR(table2.col3) RETURNING CLOB)
           FROM   table2
           WHERE  mytable.col1 = table2.col1
         ),
         'col4' VALUE JSON_OBJECT(
           'col5' VALUE col5,
           'col6' VALUE col6
           ABSENT ON NULL
         ),
         'col7' VALUE ( 
            SELECT FLOOR(MAX(days)/30)
            FROM /*schema1.*/table1
            WHERE table1.test = mytable.test
         )
         ABSENT ON NULL
         RETURNING CLOB
       ) AS output
FROM   mytable

If I try to guess your data:

CREATE TABLE mytable (col1, col2, col5, col6, test) AS
  SELECT 12345, '1', 'test', '2345', 'X' FROM DUAL;

CREATE TABLE table1 (test, days) AS
SELECT 'X', 30*14 FROM DUAL;

CREATE TABLE table2 (col1, col3) AS
SELECT 12345, 654321 FROM DUAL UNION ALL
SELECT 12345, 765432 FROM DUAL;

Then the output is:

OUTPUT
{"col1":12345,"col2":"1","col3":["654321","765432"],"col4":{"col5":"test","col6":"2345"},"col7":14}

fiddle