4 replies

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

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.

4 replies

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

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.