I stumbled across this earlier today, as it's impacting my work. I believe it's a bug in Oracle.
The gist of it is that the following query will execute when either of the 2 key/value pairs are present, but never both:
select json_object(
key 'foo'
value json_arrayagg((select * from dual)),
key 'nested'
value (select json_object(key 'bar' value 2) from dual)
)
from dual;
Try to comment out either foo or nested and see that running either alone will work, but as written above running it gives:
[42000][937] ORA-00937: not a single-group group function Position: 133
Note that unwrapping the nested json_object() from the subquery select also makes it work, which is strange since they should be semantically equivalent:
value json_object(key ‘bar’ value 2)
Is there any way to work around this?