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.
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