Couchbase : How to maintain arrays without duplicate elements?

Question

We have a Couchbase store which has the Customer data.

• Each customer has exactly one document in this bucket.
• Daily transactions will result in making updates to this customer data.

Sample document. Let's focus on the purchased_product_ids array.

{
  "customer_id" : 1000
  "purchased_product_ids" : [1, 2, 3, 4, 5 ] 
      # in reality this is a big array - hundreds of elements
  ... 
  ... many other elements ...
  ...
} 

Existing purchased_product_ids : 
    [1, 2, 3, 4, 5]

products purchased today : 
    [1, 2, 3, 6]  // 6 is a new entry, others existing already

Expected result after the update: 
    [1, 2, 3, 4, 5, 6]

I am using Subdocument API to avoid large data transfer between server and clients.

Option1 "arrayAppend" :

customerBucket.mutateIn(customerKey)
    .arrayAppend("purchased_product_ids", JsonObject for [1,2,3,6] )
    .execute();

It results in duplicate elements. 
"purchased_product_ids" : [1, 2, 3, 4, 5, 1, 2, 3, 6]

Option2 "arrayAddUnique" :

customerBucket.mutateIn(customerKey)
    .arrayAddUnqiue("purchased_product_ids", 1 )
    .arrayAddUnqiue("purchased_product_ids", 2 )
    .arrayAddUnqiue("purchased_product_ids", 3 )
    .arrayAddUnqiue("purchased_product_ids", 6 )
    .execute();

It throws exception for most of the times, 
because those elements already existing.

Is there any better way to do this update ?

Answer 1

You could use N1QL, and the ARRAY_APPEND() and ARRAY_DISTINCT() functions.

UPDATE customer USE KEYS "foo" 
SET purchased_product_ids = ARRAY_DISTINCT(ARRAY_APPEND(purchased_product_ids, 9))

Presumably this would be a prepared statement and the key itself and the new value would be supplied as parameters.

Also, if you want to add multiple elements to the array at once, ARRAY_CONCAT() would be a better choice. More here:

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/arrayfun.html

Answer 2

Do you need purchased_product_ids to be ordered? If not you can convert it to a map, e.g.

{
  "customer_id" : 1000
  "purchased_product_ids" : {1: {}, 3: {}, 5: {}, 2: {}, 4: {}}
}

and then write to that map with subdoc, knowing you won't be conflicting (assuming product IDs are unique):

customerBucket.mutateIn(customerKey)
   .upsert("purchased_product_ids.1", JsonObject.create()) // already exists
   .upsert("purchased_product_ids.6", JsonObject.create()) // new product
   .execute();

which will result in:

{
  "customer_id" : 1000
  "purchased_product_ids" : {1: {}, 3: {}, 6: {}, 5: {}, 2: {}, 4: {}}
}

(I've used JsonObject.create() as a placeholder here in case you need to associate additional information for each customer-order paid, but you could equally just write null. If you do need purchased_product_ids to be ordered, you can write the timestamp of the order, e.g. 1: {date: <TIMESTAMP>}, and then order it in code when you fetch.)