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?

sticky bit · Accepted Answer · 2021-10-25 23:14:33Z

8

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

edited Oct 25, 2021 at 23:14

answered Apr 17, 2019 at 22:04

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Beda Hammerschmidt · Accepted Answer · 2020-10-23 20:31:09Z

3

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

answered Oct 23, 2020 at 20:31

Beda Hammerschmidt

4463 silver badges6 bronze badges

Comments

Lukas Eder · Accepted Answer · 2021-06-30 13:26:12Z

2

I've found this hack to work:

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

See also: Oracle bug produces duplicate aggregate values in JSON_ARRAYAGG

answered Jun 30, 2021 at 13:26

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Collectives™ on Stack Overflow

How to return distinct values in a JSON_ARRAYAGG

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related