Continuing the question how to update JSONB column using knexjs, bookshelfjs, I would like to update jsob-property not as just a string but as [string + current row id column value] (the second answer example).
return knex("tablename").update({
jsonbkey: knex.raw(`
jsonb_set(jsonbkey, '{city}','"Ayodhya ${ROW_ID_COLUMN_HERE}"')
`)
}).where({"id" :2020})
Is it possible to do this operation during one query?
The second part of my request - how can I perform this query only for row that has those 'city' not as empty string.
return knex("tablename").update({
jsonbkey: knex.raw(`
jsonb_set(jsonbkey, '{city}','"Ayodhya ${ROW_ID_COLUMN_HERE}"')
`)
}).where({"id" :2020}).and.whereNot(jsonbkey.city, '')
My rows look like:
id | jsonbkey
1 | {"city": "", code: "EU"}
2 | {"city": "Paris", code: "FR"}
I have been thinking about two queries like array_agg for only ids and then loop throw this array - is it rational?
