Replace object inside a nested array

SQL++
,
1

Hey I have these kind documents:

[
	{
		"type": "computer",
		"defaultProperties": 
		{
			"characteristics": [
				"propertyId": "25e2ed9d-8894-9lc2-55b5-83f36f212p64",
				"colors": [
					{
						"id": "24e2ed9d-8894-9lc2-55b5-83f36f212f64",
						"name": [
							{
								"display": "green"
							}
						]
					},
					{
						"id": "5f124799-001d-2203-aa3e-f3eadff5c8eb",
						"name": [
							{
								"display": "blue"
							}
						]
					},
					{
						"id": "fe97b188-1d80-6428-10af-f7e763523cdc",
						"name": [
							{
								"display": "red"
							}
						]
					}
				]
			]			
		},
		"deleted": false
	}
]

I’m executing a query in order to replace the objects inside the colors array:

UPDATE
  `Bucket_XXX` AS c  
SET
  co = { "display": co.display}
  FOR co IN ch.colors
    FOR ch IN c.defaultProperties.characteristics END
WHERE    
  (c.type = 'computer' AND c.deleted = false)
  AND
  ARRAY_LENGTH(c.defaultProperties.characteristics) > 0;

The result for colors should be:

"colors": [
  {
    "name": [
      {
        "display": "green"
      }
    ]
  },
  {
    "name": [
      {
        "display": "blue"
      }
    ]
  },
  {
    "name": [
      {
        "display": "red"
      }
    ]
  }
]

After run the query the message said that x row was mutated but no changes are reflected

2

@IRobot ,

  1. Is typo characteristics vs specifications
  2. co is binding variable. You can’t modify itself (i.e root) (co = …) i.e. will not reflect in the document. But you can modify its children. To do that set in parent via pos like below
UPDATE `Bucket_XXX` AS c
SET ch.colors[pos] = { co.name }
       FOR pos:co IN ch.colors
           FOR ch IN c.defaultProperties.specifications END
WHERE (c.type = 'computer' AND c.deleted = false)
  AND ARRAY_LENGTH(c.defaultProperties.specifications) > 0;

If u need to unset single field id u can use

UPDATE `Bucket_XXX` AS c
UNSET co.id
       FOR co IN ch.colors
           FOR ch IN c.defaultProperties.specifications END
WHERE (c.type = 'computer' AND c.deleted = false)
  AND ARRAY_LENGTH(c.defaultProperties.specifications) > 0;