2

I have a JSONB column in PostgreSQL database like {lat: value, lon: value}. I want to change any specific value at a time eg. lat, but I am not sure how I can achieve this using bookshelf.js or knex.js. I tried using jsonb_set() method specified in Postgres documentation but I am not sure if I used that correctly. Can somebody please suggest me how can I do this? or what is the correct syntax to do this? Thanks.

zerosand1s
  • 750
  • 1
  • 8
  • 23

3 Answers3

4

AFAIK only knex based thing that supports writing to and extracting data from postgresql jsonb columns is objection.js ORM.

With plain knex you need to use raw to write references:

knex('table').update({
  jsonbColumn: knex.raw(`jsonb_set(??, '{lat}', ?)`, ['jsonbColumn', newLatValue])
})

You can check generated SQL here https://runkit.com/embed/44ifdhzxejf1

Originally answered in: https://github.com/tgriesser/knex/issues/2264

More examples how to use jsonb_set with knex can be found in following answers

How to update a jsonb column's field in PostgreSQL?

What is the best way to use PostgreSQL JSON types with NodeJS

Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • How to update json column , using JSON_SET can you check https://stackoverflow.com/questions/57331278/how-to-update-a-key-of-object-with-value-in-json-column-with-knexjs, i have tried but all i get is this error json_set(unknown, unknown, unknown) does not exist – sasha romanov Aug 02 '19 at 18:20
  • what if i want to update multiple keys in the same field if i did it one by one in the same query update. only the last one updates, i made a question in the link above – sasha romanov Aug 03 '19 at 05:28
  • As far as I know it is not possible, unless you create multiple nested jsonb_set function calls where you set each key one by one. https://www.postgresql.org/docs/9.6/functions-json.html – Mikael Lepistö Aug 16 '19 at 15:49
  • Here is an example how nested jsonb_set queries may be built https://runkit.com/embed/5jw1issuf141 `update \"table\" set \"jsonbColumn\" = jsonb_set(jsonb_set(\"jsonbColumn\", '{key1}', ?, true), '{key2}', ?, true)` – Mikael Lepistö Aug 16 '19 at 17:53
0

Jsonb field update using knex.js

return knex("tablename").update({
        jsonbkey: knex.raw(`
        jsonb_set(jsonbkey, '{city}','"Ayodhya"')
          `)
      }).where({"id" :2020})

The jsonbkey will be the column name, where the datatype is jsonb.

The tablename is the name of your table.

The city is the object key.

If there is multiple level of object then you can use dot. Like '{city.id}'

Nayan Patel
  • 1,683
  • 25
  • 27
  • Is the separator for deep paths a dot (`.`), or a comma (`,`)? [This answer](https://stackoverflow.com/a/23500670/2441655) suggests it is a comma. Maybe both work? – Venryx Oct 12 '21 at 00:07
0
 let result = await db().raw(`UPDATE widget 
SET name = ?,
jsonCol= jsonCol::jsonb || ?::jsonb
WHERE id = ?`, 
[name, JSON.stringify(newJsonData), id);

this knex query helps to update any json column by overriding specific keys in the value supplied to the right hand side of || operator. DO NOT forget to typecast the values with ::jsonb

Abdul Samea
  • 31
  • 6