Grouping json array In Oracle Query

Question

I have Oracle table:

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

Data

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;

Does Oracle support queries that yield following output? -- deduplicate and group all into single JSON array

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

Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Answer 1

From Oracle 12c onwards, you can use json_table() to unnest the json arrays: from there on, you can then deduplicate the elements, and finally use aggregate function json_arrayagg() to rebuild the arrays:

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

Note that, for some reason that looks like a bug to me, you do need group by to properly eliminate the duplicates: select distinct does not seem to work (dups are still showing in the resulting arrays - at least in my db fiddle).

Demo on DB Fiddle:

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