I am trying to aggregate A into a list of dictionaries. Oracle's LISTAGG() works but does run into the 4k max char limit. I tried with XMLAGG but now I'm getting "and quot;" instead of "". Please suggest the best way of resolving this. XMLCAST ? Will the final outputs of listagg() and the work around be identical ?

A	N
{"1":"09","2":"11","3":"2010","4":"XYZ","5":""}	1
{"1":"09","2":"11","3":"2010","4":"XYZ","6":""}	2
{"1":"09","2":"11","3":"2010","4":"XYZ","7":""}	3

select  
  --        '[' ||
  --        LISTAGG(cte.A, ' , ') WITHIN GROUP(
  --                ORDER BY
  --                    cte.N
  --            )
  --            || ']'
  '[' ||
  rtrim(xmlagg(xmlelement(e,cte.A,', ').extract('//text()') order by cte.N).getclobval(),', ')
  || ']' aggr_lsts
from cte;

Bad Output:

[{"1":"09","2":"11","3":"2010","4":"XYZ","5":""}, {"1":"09","2":"11","3":"2010","4":"XYZ","6":""}, {"1":"09","2":"11","3":"2010","4":"XYZ","7":""}]

Good Output:

[{"1":"09","2":"11","3":"2010","4":"XYZ","5":""} , {"1":"09","2":"11","3":"2010","4":"XYZ","6":""} , {"1":"09","2":"11","3":"2010","4":"XYZ","7":""}]

Thank you.

CodePudding user response：

You may use xmlcast function to correctly deserialize XML data as a clob.

Please note, that XMLType methods are deprecated since Oracle 11.2, so use XMLQUERY and XMLCAST instead.

with sample(col, rn) as (
  select column_value, rownum
  from sys.odcivarchar2list(
    '&',
    '>',
    '<',
    '"',
    'correctly serializable text'
  )
)
select
  rtrim(xmlcast(xmlquery(
    '//text()'
    passing xmlagg(
      xmlelement(e,col,', ')
      order by rn
    )
  returning content
  ) as clob), ', ') as res
from sample

RES
&, >, <, ", correctly serializable text

But for 12.2 and above you may use JSON functions directly to produce correct JSON array: JSON_ARRAYAGG (with a restriction on 32k JSON keys per object, if I remember correctly).

with sample(col, rn) as (
  select column_value, rownum
  from sys.odcivarchar2list(
    '{"a": 1, "b": "2"}',
    '{"a": 2, "b": "qwe"}',
    '{"a": 3, "c": "test"}'
  )
)
select
  json_arrayagg(
    col order by rn
  ) as res
from sample

RES
["{"a": 1, "b": "2"}","{"a": 2, "b": "qwe"}","{"a": 3, "c": "test"}"]

fiddle

CodePudding user response：

Don't use XML functions with JSON data. From Oracle 12.2, use JSONAGG to aggregate the rows:

SELECT JSON_ARRAYAGG(a FORMAT JSON ORDER BY n RETURNING CLOB) As output
FROM   table_name

Note: you need FORMAT JSON to treat your input as valid JSON, otherwise it will be treated as a string and quoted. You also need RETURNING CLOB otherwise the default return type is a VARCHAR2 which is limited to 4000 characters and will give you the same issues as LISTAGG.

Which, for the sample data:

CREATE TABLE table_name (A, N) AS
SELECT '{"1":"09","2":"11","3":"2010","4":"XYZ","5":""}', 1 FROM DUAL UNION ALL
SELECT '{"1":"09","2":"11","3":"2010","4":"XYZ","6":""}', 2 FROM DUAL UNION ALL
SELECT '{"1":"09","2":"11","3":"2010","4":"XYZ","7":""}', 3 FROM DUAL;

Outputs:

OUTPUT
[{"1":"09","2":"11","3":"2010","4":"XYZ","5":""},{"1":"09","2":"11","3":"2010","4":"XYZ","6":""},{"1":"09","2":"11","3":"2010","4":"XYZ","7":""}]

fiddle