admin 管理员组

文章数量: 1086019

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 edited 2 days ago Matt Kantor 2,0651 gold badge21 silver badges40 bronze badges asked Mar 28 at 14:51 Anupa ReddyAnupa Reddy 1
Add a comment  | 

1 Answer 1

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

本文标签： jsonUse JSONARRAYAGG to create list of values from two output rowsStack Overflow

版权声明：本文标题：json - Use JSON_ARRAYAGG to create list of values from two output rows - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/p/1744030392a2521390.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。