Update array of object if object exist else insert new object

SQL++
,
1

I have document with key collection:31 as following

{
 "userId": 31,
 "collection": [{
 	"id":"1b3e4567-e89b-12d3-a456-4nD66u5T40f0p",
 	"impact": 5,
 	"level": 3,
 	"title": "Souvenir",
 	"mate": "RHIO",
 	"beacon": null
 },
 {
 	"id":"48093399-c258-4ea0-a56a-8e0e8631b6ab",
 	"impact": 1,
 	"level": 3,
 	"title": "Memento",
 	"mate": "RHIO",
 	"beacon": "US"
 },
 {
 	"id":"2ab82eb1-c67f-4858-96ee-1684c1b0e8b6",
 	"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.

2

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.

3

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

4

Did you take a look at N1QL.

5

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

6

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

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