Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
3 rows affected

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


GROUP_NAME
NEW_JSON_ARRAY_STR


engineering
["bar1","foo"]

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