I would like to select only the array aCtxtList and from it get only those items for which the property aCtxtList.process.id matches.
I have played with some queries but havent been able to crack this yet.
For instance:
SELECT e.aCtxList[*].process as proc
FROM db AS e
WHERE ANY v IN e.aCtxList SATISFIES v.id = "b99099fc-fb50-408d-a506-388493fd4d46" END
SELECT ARRAY c.process FOR c IN e.aCtxList WHEN c.process.id = "b99099fc-fb50-408d-a506-388493fd4d46" END AS proc
FROM db AS e
WHERE ANY v IN e.aCtxList SATISFIES v.process.id = "b99099fc-fb50-408d-a506-388493fd4d46" END
OR
SELECT c.process
FROM db AS e
UNNEST e.aCtxList AS c
WHERE c.process.id = "b99099fc-fb50-408d-a506-388493fd4d46" ;
First one you get the output per document i.e. if document has 4 matches those 4 as proc array. Then 10 documents matched 10 documents.
second one each match as separate document. (UNNEST SELF JOIN main document with each array element)
@vsr1
How about I want to update a field in any array element that matches the property instead of selecting it?
Let’s say there is a property, “firstName”, and I would like to update the property for those objects inside the nested array whose ids are equal to ??
UPDATE db AS e
SET c.prcoess.firstName = "xyz" FOR c IN e.aCtxList WHEN c.process.id = "b99099fc-fb50-408d-a506-388493fd4d46" END
WHERE ANY v IN e.aCtxList SATISFIES v.process.id = "b99099fc-fb50-408d-a506-388493fd4d46" END;
Check syntax diagram of UPDATE (update-for FOR ARRAYS ) posted below link