DT
Drizzle Team•16mo ago
PGT

update a nested jsonb field

I have a table with a jsonb column that is something like this: 
{
  foo: {
     bar: 'test'
     baz: 'hi'
  },
  foo2: {
    bar: 'test2'
    baz: 'hi2'
  }
}
{
  foo: {
     bar: 'test'
     baz: 'hi'
  },
  foo2: {
    bar: 'test2'
    baz: 'hi2'
  }
}
I want to update columnName.foo.bar and not mutate anything else, how would I do this? In SQL, would be something like: 
UPDATE table
SET
    columnName = jsonb_set(columnName, '{foo,bar}', to_jsonb('changing test to something else'), false)
WHERE id = 'someId';
UPDATE table
SET
    columnName = jsonb_set(columnName, '{foo,bar}', to_jsonb('changing test to something else'), false)
WHERE id = 'someId';
Looked around on GitHub issues, Discord, and the docs and can't find a way to do this the Drizzle way.
2 Replies
PGT
PGTOP•16mo ago
closest I've found is this post: https://github.com/drizzle-team/drizzle-orm/issues/724#issuecomment-1650641266
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...
PGT
PGTOP•16mo ago
but haven't been able to get it working, my update replaces the whole column had to do it like this: 
const path = `foo,bar`
const value = 'new value'

db.update(Table)
.set({
    columnName:
        sql`jsonb_set(columnName, ${path}, ${value}, false)`,
    })
.where(eq(Table.id, id));
const path = `foo,bar`
const value = 'new value'

db.update(Table)
.set({
    columnName:
        sql`jsonb_set(columnName, ${path}, ${value}, false)`,
    })
.where(eq(Table.id, id));
note: had to set the path and value outside of the sql`` template string because of the way drizzle handles the db vars under the hood, was getting some errors would love to know if there's a drizzle way to do this

Did you find this page helpful?