CODEWITHSUNDEEP
node.jspostgresql
Grogu
Grogu

Reputation: 2495

Add new array element to JSON object in Node-Postgres

I have a json field in a postgres database that looks something like this :

{

"first_name": "John",
"last_name": "Doe",
"email": "[email protected]",

"reviews" :[
{
"comment": "John gave us great service! Love this guy!",
"last_name": "Mariana",
"first_name": "Brown"
}
]

}

Using node, I'm trying to add a new array element (a new review) to the reviews. I want to be able to do this dynamically. The query I'm using ads in a array at index 2. That is not dynamic. I did find helpful material here but all the answers don't tell you how to update(add) to second level keys like the one in my situation. How can I do this?

Query

var text ="UPDATE users SET info = JSONB_SET(info, '{reviews,2}', '"+review+"') WHERE id=$1 RETURNING*";//works but not dynamic
var values = [userid];

pool.query(text, values, (err, res) => {
if (err) {

//log errors
console.log(err.stack);

//return error
io.to(socketid).emit('review-sent',{valid:'false'});

} else {

//success
console.log(res.rows );

var json = res.rows;

//success
io.to(socketid).emit('review-sent',{valid:'true'});

}
});

Upvotes: 0

Views: 557

Answers (1)

user330315
user330315

Reputation:

You can use jsonb_insert() if that is given a negative index it counts from the end. Passing true as the third parameter will then insert the new value after that, so jsonb_insert(..., '{reviews, -1}', true) will append the new value at the end:

update users
  set info = jsonb_insert(info, 
                          '{reviews,-1}', 
                          '{"comment": "...", "last_name": "...", "first_name": "..."}', 
                          true)
where id = 1;

Upvotes: 1

Related Questions