Update a nested element with multiple conditional

SQL++
,
1

I’m trying to set a property from an object that is inside an array on this case does not display errors, and it seems that it apply the changes to the number of objects that it should be but no changes are persisted.

This would be my collection:

 [
	{
 		"productName": "p00",
 		"characteristics": [
 			{
 				"name": "place x",
 				"value": [ {
 					"language": "",
 					"label": "\"Cool\""}
 				],
 				"characteristicId": "char00"
			},
 			{
 				...
 			}
 		]
 	},
 	{
 		"productName": "p01",
 		"characteristics": [
 			{
 				"name": "place x",
 				"value": [{
 					"language": "",
 					"label": "\"Nice\""}, {...}
 				],
 				"characteristicId": "char00"
 			},
 		]
 	}
 ]

An this is the query:

UPDATE Bucket_XXX AS b
SET valueToUpdate.label = ‘[’ || valueToUpdate || ‘]’
FOR characteristic IN b.characteristics
FOR valueToUpdate IN characteristic.value WHEN valueToUpdate.languageCode = ‘’ AND characteristic.characteristicId IN [‘char00’, ‘char01’] END
WHERE
(p.deleted = false)
AND
(ANY characteristic IN b.characteristics SATISFIES characteristic.characteristicId IN [‘char00’, ‘char01’] END)

I’ve tried to put the conditional to check if the characteristicId is valid on the first FOR to avoid looping inside the second FOR but it complains:

UPDATE Bucket_XXX AS b
SET valueToUpdate.label = ‘[’ || valueToUpdate || ‘]’
FOR characteristic IN b.characteristics WHEN characteristic.characteristicId IN [‘char00’, ‘char01’] END
FOR valueToUpdate IN characteristic.value WHEN valueToUpdate.languageCode = ‘’ END
WHERE
(p.deleted = false)
AND
(ANY characteristic IN b.characteristics SATISFIES characteristic.characteristicId IN [‘char00’, ‘char01’] END)

The desired output should be for the first coincidence from: “label”: “"Cool"” to “label”: “["Cool"]”

2

UPDATE SET/UNSET FOR clause WHEN is allowed only once (combine all it might be little overhead no other way).
FOR statement iteration is inside out (backward)
Checkout p.delete is it needs to be b. I don’t see that in the document.

UPDATE Bucket_XXX AS b
SET v.label = "[" || v.label || "]"
     FOR v IN c.`value`
          FOR c IN b.characteristics
              WHEN c.characteristicId IN ["char00", "char01"] AND v.language = ""  END
WHERE b.deleted = false AND (ANY c IN b.characteristics SATISFIES c.characteristicId IN ["char00", "char01"] END);

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html#update-for