Update array of object if object exist else insert new object

I have document with key collection:31 as following

 "userId": 31,
 "collection": [{
 	"impact": 5,
 	"level": 3,
 	"title": "Souvenir",
 	"mate": "RHIO",
 	"beacon": null
 	"impact": 1,
 	"level": 3,
 	"title": "Memento",
 	"mate": "RHIO",
 	"beacon": "US"
 	"impact": 3,
 	"level": 2,
 	"title": "Trophy",
 	"mate": "LEO",
 	"beacon": "AU"

Now I want to add new collection object in the array but if that ID exist then I want to update it otherwise I want to insert the new object into collections array.

Your help will be much appreciated.

If you structured your data as a map instead of an array (which it seems like you logically are using it as a map, with id is your identifier) then you could use the Sub-document API’s DICT_UPSERT command.

See http://developer.couchbase.com/documentation/server/4.5/sdk/subdocument-operations.html, specifically upsert.

using CB4.1 so need to find a way with array

Did you take a look at N1QL.

@geraldss yeah I went through array functions and conditional operators and tried some queries but it didn’t work

Ok, here’s a solution. We should add more direct support for this pattern, @vsr1 @keshav_m @prasad.

SET collection = ARRAY_DISTINCT( ARRAY_APPEND( ARRAY CASE WHEN x.id = $id THEN $new_value ELSE x END, $new_value ) )
WHERE ...;