Drizzle TeamDT
Drizzle Team•2y ago
Eli

Can't figure out how to filter by JSON column with array data

I have a query with some joins that I have built up so far:

// not the full query
const distinctConnections = db.selectDistinct().from(schema.connections).as('dc')


Now I am trying to join that list by filtering on the users whose eventGroups column, which is json, an array of objects, has an item with a particular key-value pair:

// 
export const users = pgTable('users', {
  eventGroups: json('eventGroups').$type<EventGroup[]>().notNull().default([]),
  // ...


I've tried multiple combinations of converting to jsonb or text to try and like or ->> the values, but usually the query fails with some sort of error.

const viewerCounts = db
  .select({
    count: count(),
  })
  .from(schema.users)
  .where(
    // what do I put here?
  )
  .innerJoin(
    distinctConnections,
    eq(schema.users.id, distinctConnections.id),
  )
  .prepare('select_viewer_counts_by_id');


The following don't seem to work with the way the query is currently crafted:

like(schema.users.eventGroups, '%"value":"test"%')
sql`${schema.users.eventGroups} like '%"value":"test"%'`
sql`${schema.users.eventGroups}::jsonb @> '{"value":"test"}'`
sql`${schema.users.eventGroups}::jsonb @> '[{"value":"test"}]'`


I've tried a couple other similar query modifications with no luck. Anyone know what I'm doing wrong? I've read that maybe I need to use json_array_elements or similar, but not sure how that incorporates into a query like this. Thank you!
Was this page helpful?