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!

3 Replies

Angelelz•15mo ago

https://stackoverflow.com/questions/50095322/querying-data-in-postgresql-by-applying-filter-on-a-jsonb-column

Stack Overflow

Querying data in postgresql by applying filter on a JSONB column

I have a table with 2 columns; ID (int) and EntityData (JSON). I have created a filter object in form of a Dictionary in C#. I want to fetch all rows which satisfy the key-value pair data in my fil...

d4mrOP•15mo ago

thanks! Just wanted confirmation that there's no native drizzle way and using sql is the way

Angelelz•15mo ago

You would use the sql operator to select that column only, you don't need it for the whole query

Gaming

Programming

Querying PG table by JSONB field