N1QL upserts of Array Objects

SQL++
1

I am trying to figure out what the best way performance wise is to achive the folowing. I have a Doc per Property that holds Tax info related to that property. That Data is stored in an Array of Docs which look something like this.

"assessmentTaxes": [
{
  "assessmentYear": "2018",
  "assessedValueTotal": 475563,
  "assessedValueLand": 193921,
  "assessedValueImproved": 281642,
  "yoyAsessedChange_$": 9324,
  "yoyAsessedChange_p": 2,
  "taxYear": "2018",
  "totalTax": 5765.9,
  "change_$": 155,
  "change_p": 3
},
{
  "assessmentYear": "2017",
  "assessedValueTotal": 466239,
  "assessedValueLand": 190119,
  "assessedValueImproved": 276120,
  "yoyAsessedChange_$": 9141,
  "yoyAsessedChange_p": 2,
  "taxYear": "2017",
  "totalTax": 5611.28,
  "change_$": 144,
  "change_p": 3
},
{
  "assessmentYear": "2016",
  "assessedValueTotal": 457098,
  "assessedValueLand": 186392,
  "assessedValueImproved": 270706,
  "taxYear": "2016",
  "totalTax": 5466.9
}

],

in my case i check if the assesmentYear and taxYear have a record in my array which is quite fast as i have the dockey.

 select t from mls l
 USE KEYS "tax::67248427"
 UNNEST l.assessmentTaxes t
 where t.assessmentYear = "2018" and t.taxYear = "2018"

so my question are can i upsert a doc in the array via N1QL , i assume as long as i provide enough conditions to make it unique it should work. If i want to upsert, will it delete all keys in the doc which i did not send in the upsert statement ?
If you look at 3 Doc in my array it does not have all the keys as the 1 and 2nd doc. In this case the reason is the property was build that year so it does not have keys that reflect a value for change from previous year. Is there a way to handle this in a N1QL statment in a sub doc if my json value is undefined for the key it will not be created so i am looking for something similar in N1QL

2

UPSERT is document level not array level. You decided what you want to update. If WHEN condition matches all that array elements will be updated. Others will not be touched…

UPDATE mls AS l 
USE KEYS  "tax::67248427"
SET t1. xxxx = "ABC"  FOR t1 IN l.assessmentTaxes WHEN t1.assessmentYear = "2018" and t1.taxYear = "2018" END
WHERE ANY t IN l.assessmentTaxes SATISFIES t.assessmentYear = "2018" and t.taxYear = "2018" END;
3

As it is quite hard in N1QL to create a dynamic query to only update a field in array if we have a value i was wondering if there is a way to update the Doc in Array by just passing the new doc as Value like i do to create a new one.

update mls USE KEYS "tax::67248427"
SET assessmentTaxes = ARRAY_APPEND(assessmentTaxes, {
      "assessedValueImproved": 111111,
      "assessedValueLand": 222222,
      "assessedValueTotal": 333333,
      "assessmentYear": "2019",
      "change_$": 125,
      "change_p": 4,
      "taxYear": "2019",
      "totalTax": 7777.28,
      "yoyAsessedChange_$": 88888,
      "yoyAsessedChange_p": 9
    })

Also is there a way to an upsert that can either create or replace a doc in array ?

4

Update can be done through condition or value (If you know old value you can use ARRAY_REPLACE() https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/arrayfun.html#fn-array-replace)

If you new which position you want replace

update mls USE KEYS “tax::67248427”
SET assessmentTaxes[1] = “new doc”

5

i assume i would need the complete old doc in Array if i want to use ARRAY_REPLACE ?

6

Yes. If you want set Missing field in all the array objects.

UPDATE mls AS l 
USE KEYS  "tax::67248427"
SET t1.xxxx = "ABC"  FOR t1 IN l.assessmentTaxes WHEN t1.xxxx IS MISSING END