Почему функция JSON_ARRAYAGG возвращает не уникальные значения в JSON массив?

Question

Пытаюсь поместить результат одноколоночного запроса в массив JSON, чтобы его использовать на веб-сервере.

Пробую как-то так:

SELECT col FROM tbl
/       
COL
---
A
C
A
B

SELECT JSON_OBJECT(
  'ArrayKey' VALUE JSON_ARRAYAGG( col ) 
) AS jsonResult
FROM (SELECT DISTINCT col as col
       FROM tbl);

JSONRESULT
--------------------------------
{"ArrayKey":["A","C","A","B"]}

Этот запрос возвращает в результате массив, который содержит все значения из столбца, т.е. игнорирует DISTINCT в подзапросе. Без выражения JSON_ARRAYAGG результат уникален:

SELECT DISTINCT col as col FROM tbl
/
COL
---
B
C
A

Пытался также поместить DISTINCT внутрь JSON_ARRAYAGG, но результат тот же:

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

Что не так в моем коде? Из-за чего массив выводит все значения, а не отдельные?

На db<>fiddle.

_{Свободный перевод вопроса How to return distinct values in a JSON_ARRAYAGG от участника @jwas}

Answer 1

Похоже на баг в выпуске 18c. В версии 19.3.0 больше не воспроизводится:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT JSON_OBJECT (
  2    'ArrayKey' VALUE JSON_ARRAYAGG(col)
  3  ) AS jsonResult
  4  FROM (SELECT DISTINCT col as col FROM tbl);

JSONRESULT
--------------------------------------------------------------------------------
{"ArrayKey":["B","C","A"]}

Для тех, у кого 18c, в качестве обходного пути можно воспользоваться GROUP BY вместо DISTINCT. Участник @sticky bit в принятом ответе предложил использовать подсказку NO_MERGE на подзапрос, или CTE с подсказкой MATERIALIZE.

Все варианты рабочих запросов на db<>fiddle:

select json_object ('ArrayKey' value json_arrayagg (col order by col)) jsonResult
from (select col from tbl group by col)
/
SELECT /*+NO_MERGE(x)*/ json_object(
     'ArrayKey' VALUE json_arrayagg(col)) jsonResult
FROM (SELECT DISTINCT col FROM tbl) x
/
WITH cte AS (
    SELECT /*+MATERIALIZE*/ DISTINCT col
    FROM tbl
)
SELECT json_object(
    'ArrayKey' VALUE json_arrayagg(col)) jsonResult
FROM cte
/

Участник @Beda Hammerschmidt в ответе упомянул баг:

Bug 27757725 - JSON GENERATION AGGREGATION FUNCTIONS IGNORE DISTINCT

Но бльше информации по нему не предоставил.

БЛОГ НА HUSL

Почему функция JSON_ARRAYAGG возвращает не уникальные значения в JSON массив?

Ответы (1 шт):