d4mr Posts - Answer Overflow

d4mr

•Created by d4mr on 10/19/2023 in #help

Querying PG table by JSONB field

hi! I can't find any reference on how to query a Postgress table by a JSONB field. Eg: I have a table

export const authMethods = pgTable(
  'auth_methods',
  {
    tenantId: varchar('tenant_id', { length: 128 }).notNull(),

    userId: varchar('user_id', { length: 128 })
      .notNull()
      .references(() => users.id),

    type: authMethodTypeEnum('type').notNull(),

    identifier: text('identifier').notNull(),
    secret: text('secret').notNull(),

    authVendorData: jsonb('auth_vendor_data'),

    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
  }
)

export const authMethods = pgTable(
  'auth_methods',
  {
    tenantId: varchar('tenant_id', { length: 128 }).notNull(),

    userId: varchar('user_id', { length: 128 })
      .notNull()
      .references(() => users.id),

    type: authMethodTypeEnum('type').notNull(),

    identifier: text('identifier').notNull(),
    secret: text('secret').notNull(),

    authVendorData: jsonb('auth_vendor_data'),

    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
  }
)

authVendorData sometimes has an email field. I want to check if any authMethod exists that has an authVendorData.email with a specific value. How can I do that with drizzle? If this currently requires me to drop down to sql , it would be very helpful to know that as well. Thanks!

4 replies

Gaming

Programming