Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Regression in Oracle 23c when nesting JSON aggregate functions

Lukas EderApr 4 2023

This query used to work in 21c:

select json_array( 
  ( 
    select coalesce( 
      json_arrayagg(json_array(v0)), 
      json_array() 
    ) 
    from ( 
      select json_array('Hello', 'World') v0 
      from DUAL 
    ) t 
  ) 
) v0 
from DUAL

It used to produce this JSON:

[[[["Hello","World"]]]]

Starting with 23c (I'm using https://hub.docker.com/r/gvenzl/oracle-free),,) I'm now getting:

SQL Error [978] [42000]: ORA-00978: nested group function without GROUP BY

The workaround is to add a dummy GROUP BY clause:

select json_array( 
  ( 
    select coalesce( 
      json_arrayagg(json_array(v0)), 
      json_array() 
    ) 
    from ( 
      select json_array('Hello', 'World') v0 
      from DUAL 
    ) t 
    group by null -- Dummy clause
  ) 
) v0 
from DUAL
This post has been answered by MartinBach-Oracle on Apr 4 2023
Jump to Answer
Comments
Post Details
Added on Apr 4 2023
4 comments
602 views