Mongoose filter by a property of multi related collection

user_1021834 0
Viewed 57

I have 2 collection types, Collection a has a property that holds an array with objects from collection b

  1. collection_a
{
  id:a1,
  collection_b: [b1,b2]
}
{
  id:a1,
  collection_b: [b2]
}
  1. collection_b
{
  id:b1,
  isOpen:true
}
{
  id:b2,
  isOpen:false
}
{
  id:b3,
  isOpen:false
}

I need to query all objects from the collection a that at least one of the connected collection_b items property isOpen is set to true.

Here is the filter I have tried and not working:

{'collection_b.isOpen' : true}
mongoose
1 Answers
  • $lookup with pipeline pass collection_b in let
  • $match to check condition collection_b condition and isOpen condition
  • $group by null and just return id, (its option) purpose of this stage to return the limited result from lookup to avoided memory exceed limit, if you are sure there are limited and countable documents then you can skip this stage
  • $match to check return result from lookup is not empty
  • $project to remove collection_b_result field its no longer needed
db.collection_a.aggregate([
  {
    $lookup: {
      from: "collection_b",
      let: { collection_b: "$collection_b" },
      pipeline: [
        {
          $match: {
            $and: [
              { $expr: { $in: ["$id", "$$collection_b"] } },
              { isOpen: true }
            ]
          }
        },
        {
          $group: {
            _id: null,
            id: { $first: "$id" }
          }
        }
      ],
      as: "collection_b_result"
    }
  },
  { $match: { collection_b_result: { $ne: [] } } },
  { $project: { collection_b_result: 0 } }
])

Playground

user_89871280
Related