Delete few items from nested document based on a condition

SQL++
13

This query insert parts > 500 into new documents and updates original document to first 500 (please note k is new key which post fixed with “::1”, “::2”,…).

INSERT INTO default (KEY k, VALUE val)
SELECT (META(cont).id || "::" || TOSTRING(uv.id)) AS k, OBJECT_PUT(cont,"parts",cont.parts[uv.s:uv.e]) AS val
FROM default cont
UNNEST (ARRAY {"s":v, "e": LEAST( v+500, ARRAY_LENGTH(cont.parts)),"id":pos} FOR pos:v IN ARRAY_RANGE(0,ARRAY_LENGTH(cont.parts),500) END) AS uv
WHERE  META(cont).id LIKE "container%" AND cont.name ="Test Bin 500" AND ARRAY_LENGTH(cont.parts)  > 500  uv.id > 0 ;

UPDATE default cont
SET cont.parts = cont.parts[0:500]
WHERE  META(cont).id LIKE "container%" AND cont.name ="Test Bin 500" AND ARRAY_LENGTH(cont.parts) > 500  ;

OR

Use MERGE to insert items > 500 into as new documents and update the the parts.

MERGE INTO default m USING
(SELECT (META(cont).id || uv.id) AS id, OBJECT_PUT(cont,"parts",cont.parts[uv.s:uv.e]) AS val
FROM default cont
UNNEST (ARRAY {"s":v, "e": LEAST( v+500, ARRAY_LENGTH(cont.parts)),"id": CASE WHEN pos > 0 THEN "::" || TOSTRING(pos) ELSE "" END} FOR pos:v IN ARRAY_RANGE(0,ARRAY_LENGTH(cont.parts),500) END) AS uv
WHERE  META(cont).id LIKE "container%" AND cont.name ="Test Bin 500" AND ARRAY_LENGTH(cont.parts)  > 500 ) AS d ON KEY d.id
WHEN MATCHED THEN UPDATE SET m.parts = d.val.parts
WHEN NOT MATCHED THEN INSERT d.val;