[SERVER-29735] add support for $elemMatch-like semantics in restrictSearchWithMatch of $graphLookup Created: 20/Jun/17  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Jame Yang Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: usability
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

restrictSearchWithMatch of $graphLookup with array not work when search into array element.



 Comments   
Comment by Asya Kamsky [ 01/Jul/17 ]

Absolutely agreed, as creating a new view is not always feasible, but I'm glad the workaround is helping you until a full solution can be implemented.

Comment by Jame Yang [ 30/Jun/17 ]

@asya, your solution of createView is very good, now running in the project with no problem. Thank you! However, it's just a workaround, using restrictSearchWithMatchCondition is the best choice.

Comment by Asya Kamsky [ 27/Jun/17 ]

In current version, there is a workaround possible using "views" - a view is defined via a pipeline and you can create a view that only contains valid relation entries.

Create view with only valid:true entries:

db.createView("oooview", "ooo", 
  [ 
       {$match:{"to.valid":true}},
       {$addFields:{to:{$filter:{input:"$to",cond:{$eq:["$$this.valid",true]}}}}}
  ]
)
{ "ok" : 1 }
db.oooview.find({},{_id:0})
{ "id" : 1, "to" : [ { "id" : 4, "valid" : true } ] }
{ "id" : 3, "to" : [ { "valid" : true } ] }

Now you would omit the restrictWithMatch condition and just do:

db.ooo.aggregate([
   {$graphLookup:{
       startWith:'$id',
       from:'oooview',
       connectFromField:'id',
       connectToField:'to.id',
       as:'relation'
   } }, 
   {$project:{_id:0,'relation._id':0,'relation.to':0,to:0}}
])
{ "id" : 1, "relation" : [ ] }
{ "id" : 2, "relation" : [ ] }
{ "id" : 3, "relation" : [ ] }

Comment by David Storch [ 26/Jun/17 ]

Thanks for the additional info, futurist! I don't think the system currently supports this behavior. I am going to convert this report into a feature request and send it to the Query Team for triage.

Comment by Jame Yang [ 24/Jun/17 ]

Hi, David,

Thanks for the answer.

an id of 2 and value: true must appear in the same array element

Is just what I'm thinking of the $graphLookup working, since the reasonable steps of above example:

when the document {id: 2} passes through the $graphLookup stage

1. $graphLookup will perform find({'to.id': 2}), which will find below document:

{ id:1, to:[{id:2}, {id:4, valid: true}]}

2. then after that, restrictSearchWithMatchCondition should take into account of the graph connection, since array element {id:4, valid: true} is not connected in connectToField, it should not appear in any further condition match. In other words, restrictSearchWithMatchCondition should only be restrict match from the already connected array elements only, not a simple find again query: find({"to.id": 2, "to.valid": true}), which will extend the connection, not a restrict.

Below

{$elemMatch: {id: 2, valid: true}}

is the right behavior, I believe.

Comment by David Storch [ 23/Jun/17 ]

Hi futurist,

Thanks for this issue report. From what I can tell, this query is working as designed. Let's just take the example of what happens when the document {id: 2} passes through the $graphLookup stage.

First, $graphLookup will evaluate the startsWith value. In this case, startsWith evaluates to 2. This value is used to seed the breadth-first search. The system will look for documents in the from collection for documents whose connectToField has a value of 2, and will filter out documents based on the restrictSearchWithMatchCondition. That is, the system will internally issue the following query against the ooo collection:

> db.ooo.find({"to.id": 2, "to.valid": true});
{ "_id" : ObjectId("594d7b5df3b4dbe4b93e3045"), "id" : 1, "to" : [ { "id" : 2 }, { "id" : 4, "valid" : true } ] }

Since this document matches, it appears in the as field ("relation"). The behavior does not change when you remove the restrictSearchWithMatch condition because the internally issued query still matches the same document:

> db.ooo.find({"to.id": 2});
{ "_id" : ObjectId("594d7b5df3b4dbe4b93e3045"), "id" : 1, "to" : [ { "id" : 2 }, { "id" : 4, "valid" : true } ]

If I understand correctly, you wish the internally issued query to apply $elemMatch-like semantics:

> db.ooo.find({to: {$elemMatch: {id: 2, valid: true}}});
// No results.

In other words, an id of 2 and value: true must appear in the same array element in order for the document to match. This functionality is currently not supported by $graphLookup. I am going to move this ticket into "waiting for user info" state; please confirm whether or not this is the functionality you need so that I can convert this ticket into the appropriate feature request. Also, let me know if you have any questions about my explanation.

Best,
Dave

Generated at Thu Feb 08 04:21:41 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.