1 reply

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

eventGroups

column, which is

json

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

jsonb

text

text

to try and

like

like

->>

->>

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

json_array_elements

or similar, but not sure how that incorporates into a query like this. Thank you!

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

eventGroups

column, which is

json

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

jsonb

text

text

to try and

like

like

->>

->>

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

json_array_elements

or similar, but not sure how that incorporates into a query like this. Thank you!

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

Similar Threads

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

Similar Threads

Similar Threads

Similar Threads