What is the analogous function to DBMS_XMLGEN.getxml(query) for json-CodePudding

I know that I can use json_arrayagg. For instance:

WITH
    ta
    AS
        (SELECT 1 a, 2 b FROM DUAL
         UNION ALL
         SELECT 11, 22 FROM DUAL)
SELECT JSON_ARRAYagg(      json_object(ta.a,ta.b)  )
  FROM     ta;

[{"a":1,"b":2},{"a":11,"b":22}]

But I have to name every column.

 SELECT XMLTYPE.createXML (DBMS_XMLGEN.getxml ('select  2 as a from dual')) FROM DUAL;

is more convenient. You don't you give the name of the column.

Is there a similare way to do that.

I've tried that too.

  select   JSON_ARRAY(DBMS_XMLGEN.getxml ('select  2 as a from dual')) from dual

But as expected

>["<?xml version=\"1.0\"?>\n<ROWSET>\n <ROW>\n  <A>2</A>\n </ROW>\n</ROWSET>\n"]

code

CodePudding user response：

From 19c, you can do json_object (*) and this will infer the attribute names from the column names:

with ta as (
  select 1 a, 2 b from dual
  union all
  select 11, 22 from dual
)
select json_arrayagg ( 
          json_object( * )  
       )
from   ta;

JSON_ARRAYAGG(JSON_OBJECT(*))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
-------------------------------
[{"A":1,"B":2},{"A":11,"B":22}]