[FIXED] how to update JSONB column using knexjs, bookshelfjs

Issue

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.

Solution

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

Answered By - Mikael Lepistö

Answer Checked By - Mary Flores (PHPFixing Volunteer)