I want to select all rows that match at least one of given tags from this table:

If I want to search by tag1 and tag3 it should return 1st, 3rd and 4th row and when I want to search by tag2 and tag4 it should return all of them because they all have this tag and if I want to search by tag4 it should return just 2nd row.

I found how to select ones that have all the tags provided which is:

.select().contains("tags", ["tag1", "tag2"])

but the returned rows meet all two tags at once so it returns just 2nd and 3rd row and I want it to return 4th row as well.

I want to select all rows that match at least one of given tags from this table:

If I want to search by tag1 and tag3 it should return 1st, 3rd and 4th row and when I want to search by tag2 and tag4 it should return all of them because they all have this tag and if I want to search by tag4 it should return just 2nd row.

I found how to select ones that have all the tags provided which is:

.select().contains("tags", ["tag1", "tag2"])

but the returned rows meet all two tags at once so it returns just 2nd and 3rd row and I want it to return 4th row as well.

• javascript
• reactjs
• supabase
• supabase-database

• Array.some() is one such function which return true if there is at least one element matching. Check this documentation – shanmukha vangaru Commented Feb 16, 2023 at 19:12
• I would rather know how to do it straight from supabase but I think it will do just fine if I make the logic behind it good enough – pelak Commented Feb 16, 2023 at 19:19

2 Answers 2

I think you're looking for the overlap filter https://supabase./docs/reference/javascript/overlaps

const { data, error } = await supabase
  .from('your_table')
  .select()
  .overlaps('tags', ['tag1', 'tag2'])

If you want to match any value of that array, then you need to encapsulate it within the OR filter:

const supabase = createClient(SUPABASE_URL, SUPABASE_KEY);
const { data: ret, error } = await supabase
  .from('test_tags')
  .select()
  .or('tags.cs.{tag1}','tags.cs.{tag2}');
  
console.log(ret);

You can also expand and use two or more OR logical operators:

const { data: ret, error } = await supabase
  .from('test_tags')
  .select()
  .or('tags.cs.{tag1},or(tags.cs.{tag4},tags.cs.{tag3})');
  
console.log(JSON.stringify(ret));