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.
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
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
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
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)
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?