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 } ]

You have 3 collections and only 1 lookup list->users. You need at least another lookup to add subs to the equation — Alex Blex
– Alex Blex, Commented Aug 20, 2020 at 15:27

turivishal · Accepted Answer · 2020-08-22 16:07:07Z

2

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

edited Aug 22, 2020 at 16:07

answered Aug 20, 2020 at 15:37

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

JMA Over a year ago

Hi. Kindly check my new update, I am able to get my result but when sorting it, it is not working.

turivishal Over a year ago

what you want exactly you want to sort by item name and pagination right? so your query is invalid check updated answer.

JMA Over a year ago

Do you have a way to remove details and just count those documents without skip and limit?

turivishal Over a year ago

you mean you need total size of array?, use $size or $count

JMA Over a year ago

can you give example of using $count / $size? I want to count total rows return of that aggregate without skip and limit option

|

Collectives™ on Stack Overflow

How to aggregate with value matched in an array then sort

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related