Oracle bug? Query runs with either of the columns selected, but not both. Throws error "ORA-00937: not a single-group group function"

Question

I'm experiencing behavior which I believe is a bug in Oracle and I'm unsure what to do about it.

It can be reproduced on Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production - Version 21.3.0.0.0 with the following query:

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;

Running this query will throw the following error, where position 133 places the cursor at the start of the word 'bar':

[42000][937] ORA-00937: not a single-group group function Position: 133

EDIT: as to why not simplify to a direct json_object() call, the real query generation produces queries like:

  key 'aggregates' value (
    select json_object(key 'min' value min("TOTAL"))
    from (
      select "INVOICE".*
      from "INVOICE"
      where "INVOICE"."BILLINGCOUNTRY" = 'Canada'
      order by
        "INVOICE"."BILLINGADDRESS" asc nulls last,
        "INVOICE"."INVOICEID" asc nulls last
      offset 0 rows
      fetch next 30 rows only
    ) "INVOICE"
  ) format json

However, if you comment out either of the key/value pairs, the query runs:

It also runs if you unwrap the nested json_object from a select statement:

Answer 1

In case anyone runs across this in the future, a workaround discovered by a coworker is to move the json_arrayagg() into the subquery that produces the json_object():

with INVOICE_DATA(INVOICEID, BILLINGCOUNTRY, TOTAL) as (
    SELECT 1 as INVOICEID, 'Canada' as BILLINGCOUNTRY, 10 as TOTAL FROM DUAL UNION ALL
    SELECT 2 as INVOICEID, 'Canada' as BILLINGCOUNTRY, 20 as TOTAL FROM DUAL UNION ALL
    SELECT 3 as INVOICEID, 'Canada' as BILLINGCOUNTRY, 30 as TOTAL FROM DUAL
)

select json_object(
  -- Removed json_arrayagg("j") here
  key 'rows' value "j" format json,
  key 'aggregates' value (
    select json_object(key 'min' value min("TOTAL"))
    from (
      select "INVOICE_DATA".*
      from "INVOICE_DATA"
    ) "INVOICE"
  ) format json
  returning clob
) "data"
from (
  --- json_arrayagg call moved to wrap "j" directly here
  select json_arrayagg(json_object(
    key 'InvoiceId' value "INVOICEID",
    key 'BillingCountry' value "BILLINGCOUNTRY"
    returning clob
  )) "j"
  from (
    select "INVOICE_DATA".*
    from "INVOICE_DATA"
  ) "INVOICE"
) "alias_70049180"

Output:

data
{"rows":[{"InvoiceId":1,"BillingCountry":"Canada"},{"InvoiceId":2,"BillingCountry":"Canada"},{"InvoiceId":3,"BillingCountry":"Canada"}],"aggregates":{"min":10}}

Answer 2

You can simplify the query to:

select json_object(
         key 'foo' value (select json_arrayagg(dummy) from dual),
         key 'nested' value (select json_object(key 'bar' value 2) from dual)
       ) AS json
from   dual;

Then to:

select json_object(
         key 'foo' value (select json_arrayagg(dummy) from dual),
         key 'nested' value json_object(key 'bar' value 2)
       ) AS json
from   dual;

and then further to:

select json_object(
         key 'foo' value json_arrayagg(dummy),
         key 'nested' value json_object(key 'bar' value 2)
       ) AS json
from   dual;

Which all output:

JSON
{"foo":["X"],"nested":{"bar":2}}

fiddle

---

As for your update, you can move the JSON_OBJECT out of the sub-query and return the minimum:

SELECT JSON_OBJECT(
         key   'aggregates'
         value JSON_OBJECT(
           key   'min'
           value ( select MIN(TOTAL)
                   from   ( select TOTAL
                            from   INVOICE
                            where  BILLINGCOUNTRY = 'Canada'
                            order by
                                   BILLINGADDRESS asc nulls last,
                                   INVOICEID asc nulls last
                            offset 0 rows
                            fetch next 30 rows only
                          )
                 )
         )
       ) AS json
FROM   DUAL;

Which, for the sample data:

CREATE TABLE invoice (total, billingcountry, billingaddress, invoiceid) AS
SELECT DBMS_RANDOM.VALUE(110,120), 'Canada', 'ABC', LEVEL FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT 100, 'Canada', 'ABC', 11 FROM DUAL UNION ALL
SELECT DBMS_RANDOM.VALUE(110,120), 'Canada', 'ABC', LEVEL + 11 FROM DUAL CONNECT BY LEVEL <= 29 UNION ALL
SELECT 90, 'Canada', 'ABC', 41 FROM DUAL;

Outputs:

JSON
{"aggregates":{"min":100}}

fiddle