N1QL to add new Subdocument to existing document

SQL++
1

Hi ,
I have document like below :

{
  "contact_id": "10",
  "email": "abc@xyz.com",
  "subscription_id": "0",
  "product_name": "RATE",
  "product_id": "1",
      "strategycalendar": [{
      "bid" : 123 ,               
      "showSliderShading": "true",
      "sliderLowerHandle": "-25",
      "sliderUpperHandle": "30",
      "rateChangeViewAs": "C",
      "rateChangeComparedTo": "7"
  }]
}

I want to perform insert into this subdocument as new dictionary with below elements so that final one will look like:

{
  "contact_id": "10",
  "email": "abc@xyz.com",
  "subscription_id": "0",
  "product_name": "RATE",
  "product_id": "1",
                  "strategycalendar": [{
      "bid" : 123 ,               
      "showSliderShading": "true",
      "sliderLowerHandle": "-25",
      "sliderUpperHandle": "30",
      "rateChangeViewAs": "C",
      "rateChangeComparedTo": "7"
  },
   {
      "bid" : 567,                
      "showSliderShading": "true",
      "sliderLowerHandle": "-25",
      "sliderUpperHandle": "30",
      "rateChangeViewAs": "C",
      "rateChangeComparedTo": "7"
  }               
                  ]                                 
                                      }

Can we achieve this new dictionary elements insert using N1QL ?
Also if I want to update one entire sub-document can this be done via N1QL .

We have tried couple of API methods for Python 3.0 and fails on Subdocument Mutate so not sure if API or N1QL is right and FAST method to do this kind of operation . Please help

thanks

2

N1QL Fetches whole document, modifies and writes back as whole document.

Append

UPDATE default AS d
SET d.strategycalendar = ARRAY_APPEND(d.strategycalendar, {.....})
WHERE d.contact_id = 10;

Replace bid 123

UPDATE default AS d
SET d.strategycalendar [pos] = {......}
                          FOR pos:v IN  d.strategycalendar  WHEN v.bid = 123 END;
WHERE d.contact_id = 10;

Modify part of bid

UPDATE default AS d
SET v.showSliderShading = false
                          FOR v IN  d.strategycalendar  WHEN v.bid = 123 END;
WHERE d.contact_id = 10;
1 Like
3

Do you think N1QL will insert / upsert or delete subdocument Faster than Couchbase Python API ?
My scenario is 100 user will concurrently access same bucket and 1 user can potentially try to update values multiple times within span of few secs so I need to make sure the locking will apply implicitly and it will do fast operation .
I am trying to understand if N1QL is best use case here or just 3.0 Python API ?
thanks

4

If you are already have document keys and want to do subdoc operation SDKs are better

2 Likes
5

thank you @vsr1 - appreciate it