I have a table "article" and for each entry there are some "tags" stored (array column)

name   |   tags

test1  | {t1,t3}
test2  | {t2,t3}
test3  | {t3}
test4  | {t1,t5} 
test5  | {t1,t3}
test6  | {t2,t3} 

I can query for tags which passed as an array:

SELECT name, tags FROM article WHERE tags && array['t1','t3','t10']::_varchar

This is working but I actually want to achieve that just entries will be returned when all tags of the entry are in the passed array. (not just 1)

enter image description here

  • 1
    Please always start with table definition (CREATE TABLE statement) and Postgres version. Most importantly: data type of tags. Feb 25, 2022 at 21:44

2 Answers 2


Use the contains operator <@ to check if the tags array contains all elements of the "other" array

SELECT name, tags 
FROM article 
WHERE tags <@ array['t1','t3','t10']::varchar[]

Online example

  • operator does not exist: json <@ character varying[]. Either two arrays or two JSONB values.
    – Akina
    Feb 25, 2022 at 21:20
  • 1
    @Akina: but tags is an array column, not a JSON column. {t1,t3} is the default string representation for an array. If that was a JSON column it would be ["t1","t3"]
    – user1822
    Feb 25, 2022 at 22:23
  • @a_horse_with_no_name thanks for your answer. in your "online example" it will return only "test6". What I want to achieve is: in your example it should return test1, test5 and test6. sorry for the missunderstanding. the entries don't have to have all tags but all tags of the entry must exist in the array['t1','t3','t10']; Feb 26, 2022 at 9:26
  • @user1117605: sounds like you only want where tags <@ ... - But why not test3 then? {t3} is also full contained in {t1,t3,t10}
    – user1822
    Feb 26, 2022 at 11:37
  • @a_horse_with_no_name works like a charm thanks (missed the test3) Feb 27, 2022 at 10:43
SELECT * FROM test WHERE tags <@ '["t1","t3","t10"]'::JSONB; 
-- or
SELECT * FROM test WHERE tags <@ TO_JSONB(ARRAY['t1','t3','t10'])


tags must be the column of JSONB datatype. If not then cast it to JSONB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.