How to return distinct values in a JSON_ARRAYAGG

Question

So I'm attempting to place the results of a distinct, single-column query into a JSON array so it can be used on my web server. I have it set up something like this:

SELECT JSON_OBJECT(
  'ArrayKey' VALUE JSON_ARRAYAGG( col ) 
) AS jsonResult
FROM(SELECT DISTINCT column_name AS col
       FROM tbl_name);

However, when this query returns results, the array it generates in JSON contains all values from my column and ignores the DISTINCT clause in the subquery somehow. Whenever I get rid of the JSON_ARRAYAGG clause and output the results directly, the result are unique, but somehow the command is ignored when I add it back in. I've also attempted to place the DISTINCT clause inside the JSON_ARRAYAGG as well, like so:

SELECT JSON_OBJECT(
  'ArrayKey' VALUE JSON_ARRAYAGG( DISTINCT col ) 
) AS jsonResult
FROM(SELECT DISTINCT column_name AS col
       FROM tbl_name);

to no avail. Does anyone know what's going wrong in my code that's causing the array to output all values instead of distinct ones?

Answer 1

Interesting... Looks like a bug to me. The optimizer seems to push down too eagerly.

As workaround you can use the NO_MERGE hint on the subquery.

SELECT /*+NO_MERGE(x)*/
      json_object('ArrayKey'
                  VALUE json_arrayagg(column_name)) jsonresult
      FROM (SELECT DISTINCT
                   column_name
                   FROM tbl_name) x;

A CTE and a MATERIALIZE hint seem to work too.

WITH cte
AS
(
SELECT /*+MATERIALIZE*/ 
       DISTINCT
       column_name
       FROM tbl_name
)
SELECT json_object('ArrayKey'
                   VALUE json_arrayagg(column_name)) jsonresult
       FROM cte;

db<>fiddle

Answer 2

This was a bug, we fixed it. You can try it out on live SQL

create table tbl_name (column_name number);
insert into tbl_name values(1);
insert into tbl_name values(1);
insert into tbl_name values(2);


SELECT JSON_OBJECT(
'ArrayKey' VALUE JSON_ARRAYAGG( col ) 
) AS jsonResult
FROM(SELECT DISTINCT column_name AS col
     FROM tbl_name);

{"ArrayKey" : [1,2]}

The bug is Bug 27757725 - JSON GENERATION AGGREGATION FUNCTIONS IGNORE DISTINCT you can request a backport from Oracle Support Services