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
}
1 Answer
Reset to default 0Without 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