Conditional append in a array from java sdk or N1QL

We have the following structure in mind:
{ "values": [{ "ID": "item_1", "data": [{ "name": "data_1", "value": "A" }, { "name": "data_2", "value": "XYZ" } ] }, { "ID": "item_2", "data": [{ "name": "data_1", "value": "123" }, { "name": "data_2", "value": "A23" } ] } ] }

We want to add/append elements to “data” array of a particular element in “values” array with a particular ID. ID field contains a dynamic value. Each append request for “data” array will know the value of ID field beforehand.

Is there a way to do this in couchbase via N1QL or java sdk without fetching the document to application layer first and then pushing it back again after making relevant changes(there might be concurrent appends to the same document and we want to avoid managing CAS on application layer and if possible, avoid locking altogether by using sub-doc API if possible)

N1QL always fetches. With out Fetching conditional update is not possible through subdoc.

Hey @anshul

Is it possible to change your document model? If so, you could change “values” to a JSON object, mapping your “ID” to the data. E.g.

"values": {
   "item_1": { ... },
   "item_2": { ... }, 

Now you can do a Sub-Document operation where you try to insert say “item_3” into this “values” object. If item_3 already exists, the operation will fail, and you have lock-free concurrent safety. If that makes sense?

Hey @graham.pople,

Thank you for the suggestion, this is actually what we are doing as of now. Our requirement is to create indexes on data field and the dynamic key(eg- item_1, item_2, etc) for which we planned to first create an array index via object_pairs on values field then having a nested array index on data field, but our database team advised us that having object_pairs in index can lead to issues down the line. So we’re looking at alternatives.

If we stay with the model you suggested above, our index & query would look something like this:
(will this type of index cause any issues down the line, also we verified that these kind of indexes were covering so we think object_pairs transformation would be evaluated at the time of index creation/update, not at the time of querying. Could you confirm this?)
Index: CREATE INDEX idx01 ON ent_comms_tracking(ARRAY {,} FOR value IN object_pairs(values) END)

Query: SELECT ARRAY {,} FOR value IN object_pairs(values) END as values_array FROM bucket

N1QL index selection is based on predicate. If query doesn’t have predicate only option is primary index.
In above query will never choose idx01. It is not an ARRAY index (AS you don’t have DISTINCT or ALL) it just index that transforms OBJECT into ARRAY.

CREATE INDEX idx01 ON ent_comms_tracking(ARRAY {,} FOR v IN object_pairs(`values`) END);
SELECT ARRAY {,} FOR v IN object_pairs(`values`) END
FROM ent_comms_tracking
WHERE ARRAY {,} FOR v IN object_pairs(`values`) END IS NOT NULL;

This above query and index will cover but that type of index doesn’t perform well and no use it almost whole values is part of the index, index will ballon quickly.

Same thing can be achieved by following little better way.

CREATE INDEX idx01 ON ent_comms_tracking(`values`);
SELECT ARRAY {,} FOR v IN object_pairs(`values`) END
FROM ent_comms_tracking

Array Index will be useful one want to search based on some field. In following case we don’t have array so transforming dynamic object into ARRAY using OBJECT functions and doing Array index.

Example: Want to query if the certain field (dynamically changes each query, “item_1” , “item_10”) in the values is present and get the value of that

CREATE INDEX ix1 ON ent_comms_tracking( DISTINCT ARRAY FOR v IN OBJECT_PAIRS(`values`) END);
SELECT ARRAY FOR v IN  OBJECT_PAIRS(`values`)  WHEN = "item_1" END  AS xyz
FROM ent_comms_tracking

Would recommend check documentation.

When ever you use OBJECT functions to transform ARRAY then iterate has overhead.
One has to decide data model OBJECT vs ARRAY based on there needs.

Example: OBJECT guarantees uniqueness , doesn’t handle duplicates, easy if know field, search on dynamic field makes difficult,…
ARRAY: handles duplicates, user has to take care of uniqueness, must store name, val as object, known field must iterate through array,…