Drizzle Team•12mo ago

Best way to query jsonb field

I can't find any relevent thread other than this one (https://discord.com/channels/1043890932593987624/1164542422328553502) if i have a column like:

myData: jsonb('myData').$type<{a: {b: string} }>().notNull()

myData: jsonb('myData').$type<{a: {b: string} }>().notNull()

what is the best way to find a value where b === 'test'? does this query work/make sense?

await db.query.someTable.findFirst({ where: eq(someTable.a.b, 'test'});

await db.query.someTable.findFirst({ where: eq(someTable.a.b, 'test'});

14 Replies

jakeleventhalOP•12mo ago

this doesnt seem to work. not sure how to get a nested field value like b in an eq operator

Angelelz•12mo ago

There is an open issue for jsonb in postgres, drizzle currently is not handling correctly. there's an open PR but it hasn't been merged yet

Angelelz•12mo ago

This is the issue to follow: you'll find workarounds there https://github.com/drizzle-team/drizzle-orm/issues/724

GitHub

[BUG]: jsonb always inserted as a json string when using postgres-j...

What version of drizzle-orm are you using? 0.26.5 What version of drizzle-kit are you using? 0.18.1 Describe the Bug Inserting an object into a postgres jsonb field with db.insert only inserts a st...

jakeleventhalOP•12mo ago

this seems to only reference inserts, not queries

Angelelz•12mo ago

BTW, you should use the ->> operator and others to access fields in a jsonb column

jakeleventhalOP•12mo ago

so my example, i would just update the query clause to be like this right?

where: sql`a->>'a'->>'b' = ${value}`

where: sql`a->>'a'->>'b' = ${value}`

is that correct?

Angelelz•12mo ago

I don't really use jsonb columns too much. This page should be helpful https://www.postgresql.org/docs/9.5/functions-json.html

PostgreSQL Documentation

JSON Functions and Operators

jakeleventhalOP•12mo ago

i would expect the ORM to have better support for querying a json field type since the ORM already knows the type if i supply $type

Angelelz•12mo ago

You can currently make it work manually like this, but you are more than welcome to submit a pr for better ergonomics

jakeleventhalOP•12mo ago

https://github.com/drizzle-team/drizzle-orm/issues/1690 i submitted this issue. please lmk if anything i wrote is inaccurate

GitHub

[FEATURE]: Native PG jsonb query support · Issue #1690 · drizzle-te...

Describe what you want If I have a column with a jsonb type, and I supply $type, there should be a native way for me to query this field without having to use the sql operator and rawdog the SQL he...

jakeleventhalOP•11mo ago

if the issue looks good as written, i will clsoe this post @Angelelz this doesnt seem to work properly. it returns []

      await db
        .select({ id: notifications.id })
        .from(notifications)
        .where(sql`meta->>'invitationId' = 'ffc617d5-c0cf-4a98-940b-af2926816bed'`)
        .execute()

      await db
        .select({ id: notifications.id })
        .from(notifications)
        .where(sql`meta->>'invitationId' = 'ffc617d5-c0cf-4a98-940b-af2926816bed'`)
        .execute()

for this row in the DB

  {
    action: 'ManagementInvitation',
    createdAt: 2024-01-05T02:34:00.120Z,
    description: 'You have been invited by Demo Company to have a managed account',
    dismissable: false,
    id: 'e9709f16-d7dd-4a7d-8cb2-b31fe60b1699',
    managerId: null,
    meta: {
      company: 'Demo Company',
      invitationId: 'ffc617d5-c0cf-4a98-940b-af2926816bed'
    },
    processed: false,
    title: 'Management Invitation',
    type: 'ManagerInvitation',
    userId: '0015bae9-6f4a-460f-801d-aaced0391583'
  }

  {
    action: 'ManagementInvitation',
    createdAt: 2024-01-05T02:34:00.120Z,
    description: 'You have been invited by Demo Company to have a managed account',
    dismissable: false,
    id: 'e9709f16-d7dd-4a7d-8cb2-b31fe60b1699',
    managerId: null,
    meta: {
      company: 'Demo Company',
      invitationId: 'ffc617d5-c0cf-4a98-940b-af2926816bed'
    },
    processed: false,
    title: 'Management Invitation',
    type: 'ManagerInvitation',
    userId: '0015bae9-6f4a-460f-801d-aaced0391583'
  }

Angelelz•11mo ago

My experience with jsonb is kinda limited. I don't like that data type to be honest with you. I prefer the good old relations to put my data in If you added that data with drizzle, might be why you have problems accessing it

DiamondDragon•7mo ago

yeah i just spent way too long with this issue as well. if you didnt insert the data as sql${data}::jsonb then its a stringified object in the db and your json function wont work. for example see here. The last record i inserted correctly casting it to jsonb manually @Andrew Sherman hate to tag you but this would be massively appreciated to get merged from @Angelelz https://github.com/drizzle-team/drizzle-orm/pull/1785

GitHub

Fix: json and jsonb parsing in postgres-js by Angelelz · Pull Reque...

Second (and better) attempt to close #724 and close #1511 This PR depends on #1659 which has already been merged to beta. After figuring out how to bypass parsers and serializers in postgres.js dri...

Andrii Sherman•7mo ago

yeah, we will add it, but would need to change a logic a bit

Gaming

Programming

Best way to query jsonb field