Join query on Mongo

Question

I have the following collections:

Patterns: {idPattern, name, description}
SelectedPatterns: {user, idPatterns}

I am trying to do a query that retrieves all the pattern info (idPattern, name, description) for a given set of users. For instance, imagine the database info is as follows:

Patterns:

{"idPattern":1, "name":"name1","description":"desc1"}
{"idPattern":2, "name":"name2","description":"desc2"}
{"idPattern":3, "name":"name3","description":"desc3"}
{"idPattern":4, "name":"name4","description":"desc4"}

SelectedPatterns:

{"user":"user1", "idPatterns":[1,2]}
{"user":"user2", "idPatterns":[2,3]}

The result of the query for user1 should look something like this this:

[{"idPattern":1, "name":"name1","description":"desc1"},
{"idPattern":2, "name":"name2","description":"desc2"}]

I know how to do this with some Python logic and two Mongo queries but this is quite inefficient, so my question is, could this information be retrieved with a single Mongo query?

ngShravil.py · Accepted Answer · 2020-06-11T05:16:59.440

1

You should use the $lookup operator to achieve this.

Below query will be helpful:

db.SelectedPatterns.aggregate([
  {
    $match: {
      user: "user1"
    }
  },
  {
    $lookup: {
      from: "Patterns",
      localField: "idPatterns",
      foreignField: "idPattern",
      as: "pattern_details"
    }
  },
  {
    $unwind: "$pattern_details"
  },
  {
    $replaceRoot: {
      newRoot: "$pattern_details"
    }
  },
  {
    $project: {
      _id: 0
    }
  }
])

MongoPlayGroundLink

edited Jun 11 '20 at 05:16

answered Jun 09 '20 at 18:24

ngShravil.py

4,742
3
18
30

couple of major changes : you don't need to `$unwind` array (which is actually exploding collection) ! You need to use `$match` as first stage in order to get performance gains - you're doing `$lookup` on entire collection which is not needed ! No need to do `$project` just make that obj as root !! Actually no need of `$expr` in `$match`.. Also make a difference on which users actual doc is being removed ! cause `$unwind` will remove docs where `pattern_details : []`.. – whoami - fakeFaceTrueSoul Jun 09 '20 at 18:31
@whoami, thanks for your inputs, you are right about using `$match` as the first stage to get performance gains. I have updated my answer. But, when I was using `$replaceRoot` with out the `$unwind` stage, I was getting an error: `'newRoot' expression must evaluate to an object, but resulting value was: [...] (an array)`. So, I used it. let me know, if there is other work arounds. – ngShravil.py Jun 10 '20 at 05:08
I said about `$unwind` used as first stage not about last one, yes `newRoot` needs an object not an array. Also not sure why you've changes your `$lookup` to include conditions which is not needed though you can use same original `$lookup` with out the add-on need from `$expr` operator.. – whoami - fakeFaceTrueSoul Jun 10 '20 at 19:28
Since, `idPatterns` is an array `SelectedPatterns` collection, I had to use `$in` operator. – ngShravil.py Jun 11 '20 at 05:08
In `$lookup` it doesn't matter whether it's an array or a single value ! Check this :: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/index.html#use-lookup-with-an-array – whoami - fakeFaceTrueSoul Jun 11 '20 at 05:10
1

Wow! That's amazing feature of `$lookup`. I was not aware of it. Thanks for letting me know. I thought only equality condition is checked. I have updated my answer. – ngShravil.py Jun 11 '20 at 05:18

Join query on Mongo

1 Answers1