CODEWITHSUNDEEP
postgresqljsonbknex.jsbookshelf.js
zerosand1s
zerosand1s

Reputation: 750

how to update JSONB column using knexjs, bookshelfjs

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.

Upvotes: 2

Views: 8817

Answers (3)

Abdul Samea
Abdul Samea

Reputation: 41

 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

Upvotes: 1

Nayan Patel
Nayan Patel

Reputation: 1761

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}'

Upvotes: 1

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

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

Upvotes: 5

Related Questions