Why it works different with ~~ any()?

Let me first show you my table:

INSERT INTO my_table(name, brand, source)
VALUES ('Abc', 'Abc', 'Orig'), 
('Auchan', 'Auchan', 'Orig'), 
('Auchan', 'Auchan', 'Added'),
('dj-auchan-djd', 'Auchan', 'Added'),
('Auchan', 'Other', 'Added'),
('Other', 'oj_auchan', 'Added'),
('Other', 'Other', 'Added');

What I want to do is to delete data where source is 'Added' and brand or name is like brand where source is 'Orig'. For example here we will delete next rows:

('Auchan', 'Auchan', 'Added'), - simply name and brand is 'Auchan'
('dj-auchan-djd', 'Auchan', 'Added'), - brand ad name has 'Auchan'(but name lowercase)
('Auchan', 'Other', 'Added'), - name is 'Auchan'
('Other', 'oj_auchan', 'Added') - brand has 'Auchan' but lowercase.

So what it would be when we manually create this brands comparing:

delete
    from my_table
    where lower(name) ~~ any
           ('{%auchan%,%abc%}') IS TRUE
       or lower(brand) ~~ any
          ('{%auchan%,%abc%}') IS TRUE
       and source = 'Added';

It works good, we deleting all rows with this 'Auchan'. But when I am trying to aggregate array of this brands:

delete
from my_table
where lower(name) ~~ any
      (select '{'||array_to_string(ARRAY_AGG(DISTINCT '%' || lower(brand) || '%'), ',')||'}'
      from my_table
      where source = 'Orig') IS TRUE
   or lower(brand) ~~ any
      (select '{' || array_to_string(ARRAY_AGG(DISTINCT '%' || lower(brand) || '%'), ',') || '}'
      from my_table
      where source = 'Orig') IS TRUE
   and source = 'Added';

No data will be deleted.

I even checked if they are similar, and yes these two arrays will be similar...

Can someone plz help(or maybe give advice how to delete this occurrences)?

Solution

The problem is that the result of your subquery is interpreted as a string, not an array. This is because these two forms are semantically different:

~~ ANY ('...')  -- will be interpreted as an array literal

and

~~ ANY (SELECT ...)  -- will compare with all query results in turn

So you can simply write:

WHERE lower(name) ~~ ANY
      (SELECT DISTINCT '%' || lower(brand) || '%'
       FROM my_table
       WHERE source = 'Orig')