I have something like a table having 2 columns -> component_names and component_types where the task is to remove te duplicates from the component_names for that component_types meaning same named coponent can be there across the types but not within.

CREATE TABLE IF NOT EXISTS test
(
    id integer NOT NULL DEFAULT nextval('threshold_retry.threshold_details_id_seq'::regclass),
    component_type text COLLATE pg_catalog."default" NOT NULL,
    component_names text[] COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT test_pk PRIMARY KEY (id)
)

INSERT INTO test(
    id, component_type, component_names)
    VALUES (1, 'INGESTION', '{ingestiona,atul, ingestiona, ingestionb}'),
    (2, 'INGESTION', '{test_s3_prerit, atul}'),
    (3, 'DQM', '{testmigration}'),
    (4, 'SCRIPT', '{scripta}'),
    (5, 'SCRIPT', '{testimportscript, scripta}'),
    (6, 'SCRIPT', '{Script_Python}'),
    (7, 'BUSINESS_RULES', '{s3_testH_Graph}'),
    (8, 'EXPORT', '{Export2}')
    

;

I am working on the result to be like below:

component_type  component_names
INGESTION       {ingestiona,atul,ingestionb}
INGESTION       {test_s3_prerit}
DQM             {testmigration}
SCRIPT          {scripta}
SCRIPT          {testimportscript}
SCRIPT          {Script_Python}
BUSINESS_RULES  {s3_testH_Graph}
EXPORT          {Export2}

Where atul, ingestiona and scripta is getting removed.

I have written the python script for this but want to explore more in pgsql.
Tried few quries but didn’t work.