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.
Add a comment
|
3 Answers
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
4 Comments
sasha romanov
How to update json column , using JSON_SET can you check stackoverflow.com/questions/57331278/…, i have tried but all i get is this error json_set(unknown, unknown, unknown) does not exist
sasha romanov
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
Mikael Lepistö
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. postgresql.org/docs/9.6/functions-json.html
Mikael Lepistö
Here is an example how nested jsonb_set queries may be built runkit.com/embed/5jw1issuf141
update \"table\" set \"jsonbColumn\" = jsonb_set(jsonb_set(\"jsonbColumn\", '{key1}', ?, true), '{key2}', ?, true)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}'
1 Comment
Venryx
Is the separator for deep paths a dot (
.), or a comma (,)? This answer suggests it is a comma. Maybe both work? 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
Comments
