How to check if any field of array not contains substring in Postgres?-CodePudding

How to check if any field of array not contains substring in Postgres?

$ select * from blogs;
 id  |   comments
------- ---------------
 1 | {str1,str2,str3}
 2 | {_substr_,str2,str3}

What I expected is like this:

> select * from mytable where ANY(comments)  not like '%substr%';
     id  |   comments
    ------- ---------------
     1 | {str1,str2,str3}

If I use unnest, I will get unpacked array joined with every record(Not expected) like this：https://www.db-fiddle.com/f/9997TuKMMzFUUuyr5VJX7a/0

 > select * from (select id,unnest(comments) as cmts from t1) tmp where cmts not like '%substr%'
  id  | cmts
------- ------
 1 | str1
 1 | str2
 1 | str3
 2 | str2
 2 | str3

If I use array_to_string(array, delimiter) with not like, I could get what I wanted as following

  > select * from (select id,array_to_string(comments, ',') as cmts from blogs) tmp where cmts not like '%substr%';
 id  |      cmts
------- ----------------
 1 | str1,str2,str3

But there is a limit: *substr* cann't contains delimiter:

# select * from (select id,array_to_string(comments, ',') as cmts from blogs) tmp where cmts not like '%str1,str2%';
 id  |        cmts
------- --------------------
 2 | _substr_,str2,str3

Is there any better way to filter the whole row if any field of comments not contains specified substring? How to check if any field of array not contains substring in Postgres?

$ select * from blogs;
 id  |   comments
------- ---------------
 1 | {str1,str2,str3}
 2 | {_substr_,str2,str3}

What I expected is like this:

> select * from mytable where ANY(comments)  not like '%substr%';
     id  |   comments
    ------- ---------------
     1 | {str1,str2,str3}

If I use unnest, I will get unpacked array joined with every record(Not expected)

 > select * from (select id,unnest(comments) as cmts from t1) tmp where cmts not like '%substr%'
  id  | cmts
------- ------
 1 | str1
 1 | str2
 1 | str3
 2 | str2
 2 | str3

If I use array_to_string(array, delimiter) with not like, I could get what I wanted as following

  > select * from (select id,array_to_string(comments, ',') as cmts from blogs) tmp where cmts not like '%substr%';
 id  |      cmts
------- ----------------
 1 | str1,str2,str3

But there is a limit: *substr* cann't contains delimiter:

# select * from (select id,array_to_string(comments, ',') as cmts from blogs) tmp where cmts not like '%str1,str2%';
 id  |        cmts
------- --------------------
 2 | _substr_,str2,str3

Is there any better way to filter the whole row if any field of comments not contains specified substring?

CodePudding user response：

If you have a unique id in your table, you can do it like this (result here)

with x as (select *,unnest(arrays) as to_text from t1)
select t1.*
from t1,x 
where x.to_text ilike '%sutstr%'
and x.id = t1.id

CodePudding user response：

You can try to use unnest function.

select *
from (
  select *,unnest(arrays) as val 
  from mytable
) tt 
WHERE pub_types like '%sutstr%'

If you don't want to unpack arrays, another way you can try to use ARRAY_TO_STRING function with LIKE.

SELECT *
FROM mytable
where ARRAY_TO_STRING(pub_types, ',') LIKE '%sutstr%'