Update multiple attributes within Array and Nested Array

.NET SDK
,
1

Hi,

I am using Couchbase v6.0. I am looking for best optimum solution/approach from performance perspective for updating multiple attributes within array and nested array via N1QL. Though, I have come up with an approach but I see there are multiple loops required with this approach( no of attributes to update== no of loops to be performed in array and the level of loop increments as you go in nested arrays).

Below is the document structure for example:

{
  "docType": "std",
  "version": 1,
  "class": "10th",
  "course": "IT",
  "school": "HCHSS",
  "student": [
    {
      "email": "abc@gm.com",
      "name": "Rahul",
      "phoneNr": "123456",
      "surname": "Sharma",
      "subject": [
        {
          "maths": 75,
          "english": 85,
          "examType": "Quaterly"
        },
        {
          "maths": 95,
          "english": 65,
          "examType": "halfyearly"
        }
      ]
    },
    {
      "email": "def@gm.com",
      "name": "Rohit",
      "phoneNr": "657453",
      "surname": "Verma",
      "subject": [
        {
          "maths": 55,
          "english": 75,
          "examType": "Quaterly"
        },
        {
          "maths": 90,
          "english": 70,
          "examType": "halfyearly"
        }
      ]
    }
  ]
}

Below is the N1QL Query:

UPDATE Student_Db
SET class =‘11th’,
course=‘ITT’,
std.email=‘upabc@123.com’ for std in student WHEN std.name = ‘Rahul’ and std.surname = ‘Sharma’ END,
std.phoneNr=‘7657822’ for std in student WHEN std.name = ‘Rahul’ and std.surname = ‘Sharma’ END,
sub.maths = 50 for sub IN std.subject for std in student WHEN std.name = ‘Rahul’ and std.surname = ‘Sharma’ and sub.examType=‘Quaterly’ END
Where meta().id = ‘student’

  1. Is thee any better solution/approach available to perform multiple attributes update inside array and nested array using N1QL.

  2. I am using .NET Couchbase SDK and Sub-document API in application. To achieve the same via SDK, I can use Upsert/Replace method but that will replace the entire document which might be like passing huge amount of data over network everytime even if just 6-7 attributes are to be updated in whole document. So not sure, how much document size is ideal to pass over network (my document can grow in MB, probably upto 3-4 MB).

  3. If I opt for Sub-Document API, i.e. mutate in methods which only mutates part of the document we want to. But here the problem I see is, to update array elements, we need to pass path by position( something like students[0] , student[0].subjects[0] ). Here we have to rely on indexing and I am not finding way to use expressions(find by attribute like = Student==“Rahul”).

Please suggest which approach should be ideal.

2

It looks like you have document id. Best option you have is SDKs directly.
N1QL UPDATE will always use whole document fetch/modify/update.

3

Hi @vsr1 , Thank you for your valuable feedback :slight_smile:

Yes, I have the document Id . So, as you recommend SDK over N1QL , hence , in SDK which one would you suggest: SubDocument API(MutateIn, ArrayAppend etc features available) or normal API (Couchbase SDK- upsert/Replace). In normal API if i opt for Upsert/Replace method, as I mentioned in Point 2 above, the document size would be high and so if I transmit that much volume of data(~3-4mb appx) over the network; do you feel it might cause performance issues. Can you please suggest what should be the ideal document size to be used for Upsert/Replace methods.

Using SubDocument API, its actually seems to fit the situation as it only mutates part of the document we want to mutate. Hence, here If I have to update array elements or nested array elements , the only possible way I found is to specify array Index Position as shown below. Is it appropriate to use index position ? On a Side note, I fetch the CAS value beforehand and pass it into mutate operation to ensure no concurrent update has been done to that particular document.

bucket.MutateIn( documentKey).Upsert(students[1].subject[0].maths, 50).WithCas(cas).Execute();

4

Subdoc IS BEST if you know path . If you don’t know or complex cases use full doc.

5

Thanks @vsr1 !

Considering path in SubDoc, below are the steps I am performing to execute:

  1. I fetch the Document and CAS( for Optimistic lock) by Doc Id in code base (.NET side)
  2. Loop through the array to match the required element and get its index (index position of element inside the array).
  3. Make updates to the element locally in code base and invoke SubDoc API to mutate the document by passing array element path using Index( this is the only way I found to specify array path) and CAS(to ensure the document is not updated in between read and update operation) as below. I am passing Index position of the object which I want to update in STUDENTS array and SUBJECT object which is nested within STUDENTS array.

bucket.MutateIn( documentKey).Upsert(students[1].subject[0].maths, 50).WithCas(cas).Execute();

Do you see any challenge using Index Positions in the path ?
Is there any other way to add expressions to filter the array object path ? Like Students.Where(i=>i.examType==“Quaterly”).

6

There should not be any issue because if some one modified in between CAS will change and update will fail. You should try whole logic again. I think subdoc allows you 16 paths at one time. If more than 16 you can use full doc upsert. https://docs.couchbase.com/dotnet-sdk/3.0/howtos/subdocument-operations.html

7

Thanks @vsr1 for your valuable feedback :slight_smile:

1 Like