Oracle查询中json数组的分组

Question

我有Oracle表：

CREATE TABLE jsonarray_test 
(id VARCHAR2(50) PRIMARY KEY, 
json_array_str VARCHAR2(256), 
group_name VARCHAR2(50));

数据

INSERT ALL 
INTO jsonarray_test (id, group_name, json_array_str) values ('af1470c6-4883-454a-9cec-10cdd99c4446', 'sales', '["foo", "bar"]')
INTO jsonarray_test (id, group_name, json_array_str) values ('f310a5e0-b881-42d0-a4af-b6d1e9064676', 'sales', '["foo1", "bar"]')
INTO jsonarray_test (id, group_name, json_array_str) values ('688effbb-b665-4c58-b42a-be073823ec27', 'engineering', '["foo", "bar1"]')
SELECT 1 FROM DUAL;

Oracle是否支持产生以下输出的查询？--删除重复并将其分组到单个JSON数组中

sales       ["foo", "foo1", "bar"]
engineering ["foo", "bar1"]

版本： Oracle数据库12c企业版发布12.2.0.1.0-64位生产

Answer 1

从Oracle 12c开始，您可以使用json_table（）来重新嵌套json数组：从那以后，您可以删除重复的元素，最后使用集合函数json_arrayagg（）来重建数组：

select 
    group_name, 
    json_arrayagg(val order by val) new_json_array_str
from (
    select group_name, val
    from 
        jsonarray_test j,
        json_table(j.json_array_str, '$[*]' columns(val varchar2(10) path '$')) t
    group by group_name, val
) x
group by group_name

请注意，由于某种原因，在我看来似乎是一个bug，您确实需要group by来正确消除重复项：select distinct似乎不起作用（DUP仍然显示在结果数组中-至少在我的db FIDLE中）。

DB Fiddle上的演示：

GROUP_NAME  | NEW_JSON_ARRAY_STR  
:---------- | :-------------------
engineering | ["bar1","foo"]      
sales       | ["bar","foo","foo1"]