Delete few items from nested document based on a condition

SQL++
1

Hi,

I have multiple documents as per below structure. One document is really large and has 2k parts in it. I need to split them to multiple documents with different name and 500 parts each. Is there anyway to do this using sql or any other method.

Please let know how to form a delete query that can selectively delete certain parts that satisfies some condition.

{
“category”: “ATTUNE S+ FB TRAYS SZ 3-8”,
“currentLocation”: {
“displayName”: “JZ MEDICAL INC-704001001”,
“href”: “location_d3210e7a1dec4c6584e90e1079ab8392”
},
“homeLocation”: {
“displayName”: “JZ MEDICAL INC-704001001”,
“href”: “location_d3210e7a1dec4c6584e90e1079ab8392”
},
“name”: “ATTUNE S+ FB TIB TRAYS 3-8 AKAST-1”,
“parts”: [
{
“expirationDate”: {
“ticks”: 1824940800000,
“tz”: “Etc/UTC”
},
“lotCode”: “8656603”,
“name”: “ATTUNE S+ FB TIB BASE SZ 5 CEM”,
“partNumber”: “150670005”
},
{
“expirationDate”: {
“ticks”: 1824940800000,
“tz”: “Etc/UTC”
},
“lotCode”: “8669803”,
“name”: “ATTUNE S+ FB TIB BASE SZ 7 CEM”,
“partNumber”: “150670007”
}
],
“reservable”: true,
“serialNumber”: “K-201AKAST-1”
}

2

Tried below query - not sure what am i missing here. getting syntax error at the end of input

Update defaultbucket cont
SET cont.parts =
OBJECT ov.name:ov.val FOR ov IN
(ARRAY v FOR v in OBJECT_PAIRS(cont.parts)
WHEN v.name <>‘DoNotUse Part3’ END)
END
where meta(cont).id like ‘container%’ and cont.name =‘Test Bin 500’ and
ANY v in OBJECT_PAIRS(cont.parts) SATISFIES v.name <>‘DoNotUse Part3’ End

3

Your Input document is not JSON particularly parts array. Can you fix that.The syntax error is due to missing END at the end of statement.

4

Thanks for the quick reply. I have pasted the actual json. After addding End keyword, the query didnt not throw error, but it didnt removed expected data also :frowning:

5 
UPDATE  defaultbucket cont
SET cont.parts = ARRAY v FOR v IN cont.parts WHEN v.name != "DoNotUse Part3" END
WHERE  META(cont).id  LIKE "container%" AND cont.name ="Test Bin 500" AND
ANY v IN cont.parts SATISFIES v.name  = "DoNotUse Part3" END;
6

Thank you. Is there anyway to use limit & offset clause in this query? I want to keep the rows 500 to 1000 and delete rest - instead of using the condition v.name =‘DoNotUse Part3’

7 
UPDATE  defaultbucket cont
SET cont.parts = (SELECT RAW p FROM cont.parts AS p WHERE p.name != "DoNotUse Part3"  ORDER BY ..... OFFSET 10 LIMIT 100)
WHERE  META(cont).id  LIKE "container%" AND cont.name ="Test Bin 500" AND
ANY v IN cont.parts SATISFIES v.name  = "DoNotUse Part3" END;

OR

You can use array subscript, you need to aware of boundaries.

UPDATE  defaultbucket cont
SET cont.parts = (ARRAY v FOR v IN cont.parts WHEN v.name != "DoNotUse Part3" END)[10:20]
WHERE  META(cont).id  LIKE "container%" AND cont.name ="Test Bin 500" AND
ANY v IN cont.parts SATISFIES v.name  = "DoNotUse Part3" END;
8

I am using below one, will increment offset as required. Hope I will not run into any issues.

UPDATE defaultbucket cont
SET cont.parts = (SELECT RAW p FROM cont.parts AS p ORDER BY p.partNumber, p.lotCode OFFSET 100 LIMIT 100)
WHERE META(cont).id LIKE “container%” AND cont.name =“Test Bin 500” AND
ANY v IN cont.parts SATISFIES 1=1 END

Only problem is my order by columns doesn’t have unique values

9

Hope objects in array are Unique. Once you update you loose old data.
I would recommend insert into select.

UPDATE  defaultbucket cont
SET cont.parts = (SELECT RAW p FROM cont.parts AS p  ORDER BY p OFFSET 100 LIMIT 100)
WHERE  META(cont).id  LIKE "container%" AND cont.name ="Test Bin 500" AND
 ARRAY_LENGTH(cont.parts) > 0;
10

No. Objects in array are not unique. We need to keep duplicates.

11

See if this works. 100 offset and 200 is (offset+limit)

   UPDATE  defaultbucket cont
    SET cont.parts = (ARRAY ov FOR pos:ov IN cont.parts WHEN pos BETWEEN 100 AND 200 END)
    WHERE  META(cont).id  LIKE "container%" AND cont.name ="Test Bin 500" AND
    ARRAY_LENGTH(cont.parts)  >= 100  ;
12

Thank you :slightly_smiling_face: . I will get back later on this.

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;