Add new field to each existing array element of existing documents

SQL++
1

Using CB 6.5. I’d like to use N1QL via the query editor to add a field to documents that match a specific criteria . Those documents have a array of objects. So for each array element I need to add the new field and set its value.

After looking at the topics here I tried a few attempts but I have been unsuccessful in getting it done. While the SQL statement isn’t complaining, it isn’t updating the array elements with the new field either.

So my document looks something like this:

{
  "cancelDate": null,
  "type": "order",
  "client": null,
  "id": "123123123123_fake",
  "created": "2020-01-01T01:01:01-0400",
  "updated": "2020-01-01T01:01:01-0400",
  "currency": "USD",  
  "financialStatus": "paid",
  "isCancelled": false,
  "isRefunded": false,
  "items": 2,
  "lines": [
    {
      "price": "1.00",
      "quantity": 1,
      "sku": "sku123xyx",
      "taxable": true
    },
    {
      "price": "2.00",
      "quantity": 1,
      "sku": "sku456yx",
      "taxable": true
    }
  ]
}

to each element in the above document’s lines array I would like to add a NEW field and set its value: requiresShipping: true

I have tried without success the following statement:

UPDATE `myorders` as orders
SET i = OBJECT_ADD(i, "requiresShipping", 'true')
  FOR i in orders.lines END
WHERE orders.type = 'order' AND orders.id = '123123123123_fake'
RETURNING orders
2 
UPDATE `myorders` as orders
SET i.requiresShipping  = true
  FOR i in orders.lines END
WHERE orders.type = 'order' AND orders.id = '123123123123_fake'
RETURNING orders

Also checkout Update an attribute inside a array in a json docuemnt

3

Thank you! I was sure I tried that before but it didn’t work. Maybe I had another error that led me to think I had to use OBJECT_ADD.

Another question, is there a way of adding multiple objects to the same array?

I tried using this, but I get an error:

UPDATE `myorders` as orders
SET i.requiresShipping  = true
  FOR i in orders.lines END
SET i.discount  = NULL
  FOR i in orders.lines END
SET i.payment  = "0.00"
  FOR i in orders.lines END
WHERE orders.type = 'order' AND orders.id = '123123123123_fake'
RETURNING orders

Also, what is the correct approach if I need to add a multiple path object, i.e.: lines.discountAllocation.amount=0
I tried unsuccessfully using the previous syntax you suggested without success:

UPDATE `myorders` as orders
SET i.discountAllocation.amount = 0
  FOR i in orders.lines END
WHERE orders.type = 'order' AND orders.id = '123123123123_fake'
RETURNING orders
4

SET is allowed once.

UPDATE `myorders` as orders
SET i.requiresShipping  = true
  FOR i in orders.lines END,
  i.discount  = NULL
  FOR i in orders.lines END,
 i.payment  = "0.00"
  FOR i in orders.lines END
WHERE orders.type = 'order' AND orders.id = '123123123123_fake'
RETURNING orders;

OR

 UPDATE `myorders` as orders
  SET orders.lines[pos] = OBJECT_CONCAT(i, {"requiresShipping":true, "discount":NULL, "payment":"0.00"})
  FOR pos:i in orders.lines END
WHERE orders.type = 'order' AND orders.id = '123123123123_fake'
RETURNING orders

One you have works if the discountAllocation is OBJECT and exist. If not you need to create one before updating nested field.

UPDATE `myorders` as orders
SET i.discountAllocation = CASE WHEN IS_OBJECT(i.discountAllocation) THEN OBJECT_ADD(i.discountAllocation,"amount",0) ELSE {"amount":0} END
  FOR i in orders.lines END
WHERE orders.type = 'order' AND orders.id = '123123123123_fake'
RETURNING orders

Also in your example i is variable you can’t directly assign (those will be lost). You can modify sub field of variable and preserved (it is reference) not root (value). If you need to assign get position of array element and set in array position like we did OR case above.

You can use OBJECT_CONCAT() or any OBJECT_ functions in cascaded way (i.e. first argument is object function)

5

Thank you again, this is EXTREMELY helpful!! Thank you for the clarification. The docs did not make it clear that it can only be used once but that you can “chain” in it the way you provided in the first example of:

Since there’s only 1 loop happening in your second example, comparing it to the first with multiple loops, would it be fair to say that the second example is more efficient?

Ahh ok, I see. Great, thanks!!

6

Check rail-road diagram https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html it shows SET, UNSET once.

If you can do single loop use single loop. Some times it is not possible due to WHEN condition at that time you have to use different loops.