Drizzle Team•8mo ago

filter json field supabase drizzle

I have a case where I need to filter a json filed which contains an array of tags in the supabase database using drizzle but my syntax is wrong can anyone help me with this pls

7 Replies

drcolonelOP•8mo ago

any help on this?

Mykhailo•8mo ago

Hey @drcolonel! You can't use set-returning function in where clause that's why you face an error. I came up with this solution:

  const sq = sql`(select 1 from json_array_elements_text(posts.categories) as elem where elem.value in ${categories})`;

const result = await db
  .select()
  .from(posts)
  .where(categories.length > 0 ? exists(sq) : undefined); // add other conditions

  const sq = sql`(select 1 from json_array_elements_text(posts.categories) as elem where elem.value in ${categories})`;

const result = await db
  .select()
  .from(posts)
  .where(categories.length > 0 ? exists(sq) : undefined); // add other conditions

drcolonelOP•8mo ago

Thank you so much @Mykhailo this did work perfectly.

Mykhailo•8mo ago

@drcolonel Super! Happy to hear it. By the way, could you please tell what are you building with Drizzle?

drcolonelOP•8mo ago

@Mykhailo I'm building a multi vendor Ecommerce website

MithushanJ•7mo ago

Nice! Is this opensource project ?

Mykhailo•7mo ago

This is cool!

Gaming

Programming

filter json field supabase drizzle