$lookup subquery concept

Working with Data
1

Hi,
I’m having some difficulty trying to understand whether MongoDB can do the following via $lookup:

Using this MongoDB example, is there a way to only return a result if the orders.drink == soda? (i.e. if the subquery evaluates to true?) To clarify, instead of returning "matches" : [ ], I don’t want the result at all:

{
   "_id" : 2, 
   "item" : "cheese pizza",
   "restaurant_name" : "Honest John Pizza",
   "drink" : "lemonade",
   "matches" : [ ]
}
2

You simply add a $match stage that matches only not empty array. Something like:

{
  "$match" : { "matches.0" : { "$exists" : true } }
}
3

Thanks @steevej , this is pretty ingenious. Related to this, is there a way create the query so that there’s a match on restaurants and orders before the $lookup?

4

A simple $match stage like:

{ "$match" :
  { "restaurant_name" : "Honest John Pizza" }
}

before the $lookup will restrict the result set to the given restaurant.

5

Hi @steevej ,
Sorry I wasn’t clear. I can do the $match on restaurants, but how do I do $match on orders before the $lookup? If I do it in the $lookup, that creates the null results. Is there a way to do it before the $lookup to not create the null results.

6

There is no way to query a looked up document, before it is looked up. There is no way to predict if the $lookup will produce an empty set before doing the lookup.

Depending of the use case you might reverse your aggregation and start with orders, applying the $match you want. And the do the $lookup on the restaurant if needed.

7

Thanks @steevej , I appreciate it.

1 Like
8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.