Indexing for an Update Query

SQL++
5 
CREATE INDEX ix1d_UserAsset ON content(type, DISTINCT ARRAY v FOR v IN categories END)
WHERE (_type = "UserAsset");

UPDATE content
SET categories = ARRAY a FOR a IN categories WHEN a != "veg" END
WHERE _type="UserAsset"
      AND type IN ["IMAGE","AUDIO","VIDEO","SVG","FILE","PPT"]
      AND ANY v IN categories SATISFIES v = "veg" END
RETURNING categories;
  1. UPDATE requires whole document Fetch
  2. mutation of document is controlled by WHERE clause. SET clause only decides what to set. If you have condition in set clause that can result in update same document with out any change. So you need to add that condition to WHERE clause avoid unnecessary mutation.