Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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)
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[]
operator does not exist: json <@ character varying[]. Either two arrays or two JSONB values.
{t1,t3} is the default string representation for an array. If that was a JSON column it would be ["t1","t3"]
where tags <@ ... - But why not test3 then? {t3} is also full contained in {t1,t3,t10}
SELECT * FROM test WHERE tags <@ '["t1","t3","t10"]'::JSONB;
-- or
SELECT * FROM test WHERE tags <@ TO_JSONB(ARRAY['t1','t3','t10'])
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=55ff28e62e884047187c9e9f275934f4
tags must be the column of JSONB datatype. If not then cast it to JSONB.
CREATE TABLEstatement) and Postgres version. Most importantly: data type oftags.