Drizzle Team•11mo ago

(Solved) Filter where json array contains

I have a json array of strings, an invite list of user ids (clerk is managing my users which is why its not a relation), im trying to get all results from the events table where the current users id is in the invite list or the host, using planetscale (mysql) and i cannot figure out the correct syntax this is what i have so far

const user = await currentUser();
  if (user === null) return [];

  const query = await db.query.events.findMany({
    where: or(eq(events.user_id, user.id), inArray(events.invited_users, [user.id]))
  });

const user = await currentUser();
  if (user === null) return [];

  const query = await db.query.events.findMany({
    where: or(eq(events.user_id, user.id), inArray(events.invited_users, [user.id]))
  });

and this is the schema for the events table

export const events = mysqlTable('event', {
    id: serial('id').primaryKey(),
    user_id: text('user_id').notNull(),
    start: timestamp('start', { mode: 'date' }).defaultNow().notNull(),
    end: timestamp('end', { mode: 'date' }).defaultNow().notNull(),
    title: varchar('title', { length: 50 }),
    description: varchar('description', { length: 500 }).default(""),
    invited_users: json("invited_users").$type<string[]>().default([]),
});

export const events = mysqlTable('event', {
    id: serial('id').primaryKey(),
    user_id: text('user_id').notNull(),
    start: timestamp('start', { mode: 'date' }).defaultNow().notNull(),
    end: timestamp('end', { mode: 'date' }).defaultNow().notNull(),
    title: varchar('title', { length: 50 }),
    description: varchar('description', { length: 500 }).default(""),
    invited_users: json("invited_users").$type<string[]>().default([]),
});

5 Replies

Mykhailo•11mo ago

Hello, @! AlexNotTheLion! You can try this:

const userId = 'userId';
const query = await db.query.events.findMany({
  where: or(
    eq(schema.events.user_id, userId),
    sql`JSON_CONTAINS(${schema.events.invited_users}, ${JSON.stringify(userId)}, '$')`,
  ),
});

const userId = 'userId';
const query = await db.query.events.findMany({
  where: or(
    eq(schema.events.user_id, userId),
    sql`JSON_CONTAINS(${schema.events.invited_users}, ${JSON.stringify(userId)}, '$')`,
  ),
});

! AlexNotTheLionOP•11mo ago

NAILED IT! thank you that was driving me nuts! i tried to use json contains but i wasnt stringifying the user id i passed to it !

Alvee•11mo ago

solo is pro

! AlexNotTheLionOP•11mo ago

Agreed!

Mykhailo•11mo ago

Thank you, guys ❤️ :drizzle:

Gaming

Programming

(Solved) Filter where json array contains