如何在JSON_ARRAYAGG中返回不同的值

Question

因此，我尝试将不同的单列查询的结果放入JSON数组中，以便可以在我的web服务器上使用它。我把它设置成这样：

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

但是，当该查询返回结果时，它在JSON中生成的数组包含我的列中的所有值，并以某种方式忽略了子查询中的DISTINCT子句。每当我去掉JSON_ARRAYAGG子句并直接输出结果时，结果是唯一的，但不知何故，当我重新添加该命令时，它会被忽略。我还尝试将DISTINCT子句放在JSON_ARRAYAGG中，如下所示：

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

无济于事。有没有人知道我的代码出了什么问题，导致数组输出所有值而不是不同的值？

Answer 1

有趣的是。在我看来像是个虫子。优化器似乎太急切地向下推。

作为解决方法，您可以对子查询使用NO_MERGE提示。

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

CTE和MATERIALIZE提示似乎也有效。

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

这是一个错误，我们修复了它。您可以在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]}

错误是错误27757725 - JSON生成聚合函数忽略DISTINCT您可以从Oracle支持服务请求一个后端口

Answer 3

我发现了一个有效的方法：

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

另请参阅：Oracle bug produces duplicate aggregate values in JSON_ARRAYAGG