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')

// 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([]),
  // ...

// 
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');

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"}]'`

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!

1 Reply

francis•10mo ago

I'd suggest making your condition work in sql first (in a sql client), then figuring out how to translate that to drizzle. drizzle won't give you anything natively that sql doesn't already have

Gaming

Programming

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