Home » SQL & PL/SQL » SQL & PL/SQL » Nested JSON_ARRAYAGG --> ORA-00937 - how to create json structure (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
icon3.gif  Nested JSON_ARRAYAGG --> ORA-00937 - how to create json structure [message #682635] Wed, 04 November 2020 04:47 Go to next message
autenrip
Messages: 2
Registered: November 2020
Junior Member
Hello everyone
currently i have a challenge creating a json file in special structure and feel free to ask you directly. maybe some one has an idea to easily create it.

i simplified my example. what i have is a table with orders and items. the items itself have several records (in reality it is about incoming goods) in this example as sales date.

what i need is a json structure with 2 level. attached the requested outformat as picure (requested-format.png)

for every order i will see every item/article as array element, and inside article element there must be another array with holds the sales date.

unfortunately i didn't get it working, alsways get ORA-00937 once i included 2 JSON_ARRAYAGG .

can someone help?

following script to reproduce:


drop table json_test purge;

create table json_test
(order_id number,
article_id number,
sales_date date,
sales_qty number);

insert into json_test values (123,106,sysdate,5);
insert into json_test values (123,204,sysdate,3);
insert into json_test values (123,311,sysdate,4);

insert into json_test values (123,106,sysdate-1,5);
insert into json_test values (123,204,sysdate-1,3);
insert into json_test values (123,311,sysdate-1,4);

insert into json_test values (123,106,sysdate-2,5);
insert into json_test values (123,204,sysdate-2,3);
insert into json_test values (123,311,sysdate-2,4);

insert into json_test values (789,106,sysdate,5);
insert into json_test values (789,204,sysdate-1,3);
insert into json_test values (789,311,sysdate-2,4);

insert into json_test values (789,106,sysdate,5);
insert into json_test values (789,204,sysdate,3);
insert into json_test values (789,311,sysdate,4);

insert into json_test values (789,106,sysdate-1,5);
insert into json_test values (789,204,sysdate-1,3);
insert into json_test values (789,311,sysdate-1,4);

insert into json_test values (789,106,sysdate-2,5);
insert into json_test values (789,204,sysdate-2,3);
insert into json_test values (789,311,sysdate-2,4);

commit;

--------

following is not working:


SELECT JSON_OBJECT (
'ORDER' VALUE ORDER_ID
, 'ITEM' VALUE
JSON_ARRAYAGG (
JSON_OBJECT (
'ARTICLE' VALUE ARTICLE_ID
, 'HISTORY' VALUE JSON_ARRAYAGG (JSON_OBJECT ('SALES_DATE' VALUE SALES_DATE) FORMAT JSON ABSENT ON NULL RETURNING CLOB)-- ,'HISTORY' VALUE JSON_OBJECT ('SALES_DATE' VALUE SALES_DATE) FORMAT JSON
)
FORMAT JSON
ABSENT ON NULL
RETURNING CLOB) --item
)
AS RECORD
FROM DWH.JSON_TEST
GROUP BY ORDER_ID;

Re: Nested JSON_ARRAYAGG --> ORA-00937 - how to create json structure [message #682638 is a reply to message #682635] Wed, 04 November 2020 07:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3055
Registered: January 2010
Location: Connecticut, USA
Senior Member
You need to group by ORDER_ID,ARTICLE_ID first and only then by ORDER_ID:

WITH T AS (
           SELECT  ORDER_ID,
                   JSON_OBJECT(
                               'ARTICLE' VALUE ARTICLE_ID,
                               'HISTORY' VALUE JSON_ARRAYAGG(
                                                             JSON_OBJECT(
                                                                         'SALES_DATE' VALUE SALES_DATE
                                                                        )
                                                             FORMAT JSON ABSENT ON NULL RETURNING CLOB
                                                            )
                             ) ARTICLE
             FROM  JSON_TEST
             GROUP BY ORDER_ID,
                      ARTICLE_ID
          )
SELECT  JSON_OBJECT(
                    'ORDER' VALUE ORDER_ID,
                    'ITEM' VALUE JSON_ARRAYAGG(
                                               ARTICLE
                                              )
                   ) RECORD
  FROM  T
  GROUP BY ORDER_ID
/

RECORD
--------------------------------------------------------------------------------
{"ORDER":123,"ITEM":[{"ARTICLE":106,"HISTORY":"[{\"SALES_DATE\":\"2020-11-04T08:
04:54\"},{\"SALES_DATE\":\"2020-11-02T08:04:54\"},{\"SALES_DATE\":\"2020-11-03T0
8:04:54\"}]"},{"ARTICLE":311,"HISTORY":"[{\"SALES_DATE\":\"2020-11-04T08:04:54\"
},{\"SALES_DATE\":\"2020-11-02T08:04:54\"},{\"SALES_DATE\":\"2020-11-03T08:04:54
\"}]"},{"ARTICLE":204,"HISTORY":"[{\"SALES_DATE\":\"2020-11-04T08:04:54\"},{\"SA
LES_DATE\":\"2020-11-02T08:04:54\"},{\"SALES_DATE\":\"2020-11-03T08:04:54\"}]"}]
}

{"ORDER":789,"ITEM":[{"ARTICLE":106,"HISTORY":"[{\"SALES_DATE\":\"2020-11-04T08:
04:54\"},{\"SALES_DATE\":\"2020-11-02T08:04:54\"},{\"SALES_DATE\":\"2020-11-03T0
8:04:54\"},{\"SALES_DATE\":\"2020-11-04T08:04:54\"}]"},{"ARTICLE":311,"HISTORY":

RECORD
--------------------------------------------------------------------------------
"[{\"SALES_DATE\":\"2020-11-02T08:04:54\"},{\"SALES_DATE\":\"2020-11-02T08:04:54
\"},{\"SALES_DATE\":\"2020-11-03T08:04:54\"},{\"SALES_DATE\":\"2020-11-04T08:04:
54\"}]"},{"ARTICLE":204,"HISTORY":"[{\"SALES_DATE\":\"2020-11-03T08:04:54\"},{\"
SALES_DATE\":\"2020-11-02T08:04:54\"},{\"SALES_DATE\":\"2020-11-03T08:04:54\"},{
\"SALES_DATE\":\"2020-11-04T08:04:54\"}]"}]}


SQL>
SY.
Re: Nested JSON_ARRAYAGG --> ORA-00937 - how to create json structure [message #682640 is a reply to message #682638] Wed, 04 November 2020 10:17 Go to previous message
autenrip
Messages: 2
Registered: November 2020
Junior Member
PERFECT!!!! exactly what i wanted... Thanks a lot!
Previous Topic: How to subtract dates between different rows
Next Topic: A regular user cannot create Varchar2(32767)
Goto Forum:
  


Current Time: Sun May 16 13:31:00 CDT 2021