Search code examples
arrayspostgresqljsonb

Comparing PostgreSQL JSON arrays for any matches

I'm trying to compare two JSON arrays in PostgreSQL to see if there are any matches. For instance I would expect true when comparing '["foo", "bar", "baz"]' and '["qux", "bar"]' and false when comparing '["foo", "bar", "baz"]' and '["qux", "quz"]'. Is it possible to do that in PostgreSQL without creating a function?

Solution

  • I ended up using the ?| operator and casting to text then translating to create an array

    SELECT interests ?| TRANSLATE(tags::TEXT, '[]','{}')::TEXT[]

    interests and tags being jsonb containing arrays

    SELECT '["foo", "bar", "baz"]'::JSONB ?| TRANSLATE('["qux", "bar"]'::JSONB::TEXT, '[]','{}')::TEXT[]