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•13mo 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•13mo 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

Gaming

Programming

update a nested jsonb field