Eli Posts - Answer Overflow

Eli

•Created by Eli on 2/7/2024 in #help

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!

2 replies

Gaming

Programming