How to aggregate with value matched in an array then sort

Question

I have these collections:

lists
{_id: 1, item: "a", owner: 1}
users
{_id: 1, subs: [{_id: 1, active: "Y"},{_id: 2, active: "N"}]}
subs
{_id: 1, text: "A"}
{_id: 2, text: "B"}

I want to have a result of lists with user info and with subs info that is active.

{_id: 1, item: "a", owner: {_id: 1, subs: [{_id: {_id: 1, text: "A"}, active: "Y"}]}}

I want also to sort it based on "text" field.

I tried aggregation but failed,

db.getCollection("lists").aggregate(
[
    { 
        "$lookup" : { 
            "from" : "users", 
            "localField" : "owner", 
            "foreignField" : "_id", 
            "as" : "owner"
        }
    }, 
    { 
        "$match" : { 
            "owner.0.subs" : { 
                "$elemMatch" : { 
                    "active" : "Y"
                }
            }
        }
    }
], 
{ 
    "allowDiskUse" : false
}
);

I am also using Mongoose and failed using populate. Any way to get my result?

Here, I updated my aggregation pipeline,

[
  {
    $lookup: {
      from: "users",
      as: "owner",
      let: { owner: "$owner" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$owner", "$_id"] } } },
        { $unwind: { path:"$sub", preserveNullAndEmptyArrays: false} },
        { $match: { "subs.active": "Y" } },
        {
          $lookup: {
            from: "plans",
            localField: "subs._id",
            foreignField: "_id",
            as: "subs.plans"
          }
        },
        { $unwind: { path:"$subs.plans", preserveNullAndEmptyArrays: false} },
      ]
    }
  },
  { $unwind: { path: "$owner", preserveNullAndEmptyArrays: true} },
  { '$sort': { item: 1 } },
  { '$skip': 0 },
  { '$limit': 20 } ]

Answer 1

You can use lookup with pipeline and nested lookup,

inside lookup pipelines are:

• $match your owner id in users collection
• $unwind deconstruct subs array because we need to lookup with subs collection
• $match subs is active or not
• $lookup with subs collection
• $unwind deconstruct subs._id that we joined from subs collection
• $group reconstruct subs array
• $unwind deconstruct owner array
• $sort by item and pagination by $skip and $limit

db.getCollection("lists").aggregate([
  {
    $lookup: {
      from: "users",
      as: "owner",
      let: { owner: "$owner" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$owner", "$_id"] } } },
        { $unwind: "$subs" },
        { $match: { "subs.active": "Y" } },
        {
          $lookup: {
            from: "subs",
            localField: "subs._id",
            foreignField: "_id",
            as: "subs._id"
          }
        },
        { $unwind: "$subs._id" },
        {
          $group: {
            _id: "$_id",
            subs: {
              $push: {
                _id: "$subs._id._id",
                text: "$subs._id.text",
                active: "$subs.active"
              }
            }
          }
        }
      ]
    }
  },
  { $unwind: "$owner" },
  { $sort: { item: 1 } },
  { $skip: 0 },
  { $limit: 20 }
], { allowDiskUse: false })

Playground

---

Your Second Edit: there is wrong key name sub in first lookup inside first $unwind, correct this,

{ $unwind: { path:"$sub", preserveNullAndEmptyArrays: false} }

to

{ $unwind: { path:"$subs", preserveNullAndEmptyArrays: false} }

Your Working Query