Filter nested array in mongodb?

Question

I have a document that looks like so:

{
    "_id": {
        "$oid": "5b1586ccf0c56353e89d330b"
    },
    "address": {
        "street": "123 Street",
        "address2": "Address 2",
        "city": "Some City",
        "state": "MI",
        "zip": "12345"
    },
    "subs": [
        {
            "invoices": [
                {
                    "address": {
                        "street": "3061 Pine Ave SW",
                        "city": "Grandville",
                        "state": "AK",
                        "zip": "49418"
                    },
                    "lineItem": [
                        {
                            "images": [
                                {
                                    "_id": {
                                        "$oid": "5b1fca54e6ee1d80c463612d"
                                    },
                                    "name": "1528810066348_RSA Logo.jpeg",
                                    "url": "https....",
                                    "uploadDate": {
                                        "$date": "2018-06-12T13:27:46.931Z"
                                    },
                                    "size": 91819
                                }
                            ],
                            "_id": {
                                "$oid": "5b1fca54e6ee1d80c463612c"
                            },
                            "desc": "2",
                            "amt": 2
                        }
                    ],
                    "_id": {
                        "$oid": "5b1fca54e6ee1d80c463612b"
                    }
                }
            ],
            "_id": {
                "$oid": "5b1fc7f23b595481d4599f58"
            },
            "email": "a@a.com",
            "scope": "Roof",
        },
        {
            "invoices": [
                {
                    "address": {
                        "street": "3061 Pine Ave SW",
                        "city": "Grandville",
                        "state": "AL",
                        "zip": "49418"
                    },
                    "lineItem": [
                        {
                            "images": [
                                {
                                    "_id": {
                                        "$oid": "5b1fca2fe6ee1d80c463612a"
                                    },
                                    "name": "1528810029700_RSA Stamp.png",
                                    "url": "https....",
                                    "uploadDate": {
                                        "$date": "2018-06-12T13:27:10.403Z"
                                    },
                                    "size": 238113
                                }
                            ],
                            "_id": {
                                "$oid": "5b1fca2fe6ee1d80c4636129"
                            },
                            "desc": "1",
                            "amt": 1
                        }
                    ],
                    "_id": {
                        "$oid": "5b1fca2fe6ee1d80c4636128"
                    }
                },
                {
                    "address": {
                        "street": "3061 Pine Ave SW",
                        "city": "Grandville",
                        "state": "AL",
                        "zip": "49418"
                    },
                    "lineItem": [
                        {
                            "images": [
                                {
                                    "_id": {
                                        "$oid": "5b1fd05b0d1f7185e02e9c40"
                                    },
                                    "name": "1528811607099_error page.PNG",
                                    "url": "https....",
                                    "uploadDate": {
                                        "$date": "2018-06-12T13:53:28.080Z"
                                    },
                                    "size": 224772
                                }
                            ],
                            "_id": {
                                "$oid": "5b1fd05b0d1f7185e02e9c3f"
                            },
                            "desc": "3",
                            "amt": 3
                        }
                    ],
                    "_id": {
                        "$oid": "5b1fd05b0d1f7185e02e9c3e"
                    }
                }
            ],
            "_id": {
                "$oid": "5b1fc7f23b595481d4599f55"
            },
            "email": "b@b.com",
            "scope": "Siding",
        }
    ],
    "firstName": "",
    "lastName": "",
}

My issue is that I want to be able to access a specific invoices of a specific subs.

I am new to Mongo/Mongoose so it is possible I am doing something completely wrong and I would be more than happy with any answer/criticism on how I am approaching this.

-- tweaked answer --

Job.aggregate([
    {
        $match: {
          "_id": mongoose.Types.ObjectId(req.body.jobID)
        }
    },
    {
        $unwind: "$subs"
    },
    {
        $match: {
            "subs._id": mongoose.Types.ObjectId(req.body.subID)
        }
    },
    {
        $unwind: "$subs.invoices"
    },
    {
        $match: {
            "subs.invoices._id": mongoose.Types.ObjectId(req.body.invID)
        }
    },
    {
        $project: {
            "_id": 1,
            "subs.invoices": 1
        }
    }
], function(err, job) {
    if (err) throw err;
    res.send(job);
});

Ashh · Accepted Answer · 2018-06-12T20:06:07.360

You can try below aggregation...

Here this is a long process of deconstructing an array using $unwind and rebuild the array using $group

db.collection.aggregate([
  { "$match": { "_id": "1111" } },
  { "$unwind": "$subs" },
  { "$match": { "subs._id": "2222" } },
  { "$unwind": "$subs.invoices" },
  { "$match": { "subs.invoices._id": "3333" } },
  { "$group": {
    "_id": {
      "_id": "$_id",
      "subs": "$subs._id"
    },
    "firstName": { "$first": "$firstName" },
    "lastName": { "$first": "$lastName" },
    "address": { "$first": "$address" },
    "subs": {
      "$first": {
        "_id": "$subs._id",
        "email": "$subs.email",
        "venue": "$subs.venue",
        "scope": "$subs.scope"
      }
    },
    "invoices": { "$push": "$subs.invoices" }
  }},
  { "$group": {
    "_id": "$_id._id",
    "firstName": { "$first": "$firstName" },
    "lastName": { "$first": "$lastName" },
    "address": { "$first": "$address" },
    "subs": {
      "$push": {
        "_id": "$subs._id",
        "email": "$subs.email",
        "venue": "$subs.venue",
        "scope": "$subs.scope",
        "invoices": "$invoices"
      }
    }
  }}
])

Or you can do this with $filter aggregation as well

db.collection.aggregate([
  { "$match": { "_id": "5b1586ccf0c56353e89d330b" }},
  { "$unwind": "$subs" },
  { "$match": { "subs._id": "5b1fc7f23b595481d4599f58" }},
  { "$project": {
    "address": 1, "firstName": 1, "lastName": 1,
    "subs.type": "$subs._id",
    "subs.status": "$subs.email",
    "subs.code": "$subs.scope",
    "subs.invoices": {
      "$filter": {
        "input": "$subs.invoices",
        "as": "invoice",
        "cond": {
          "$eq": [
            "$$invoice._id",
            "5b1fca54e6ee1d80c463612b"
          ]
        }
      }
    }
  }},
  { "$group": {
    "_id": "$_id",
    "address": { "$first": "$address" },
    "firstName": { "$first": "$firstName" },
    "lastName": { "$first": "$lastName" },
    "subs": { "$push": "$subs" }
  }}
])

Thanks for the quick response! This is still returning all of the invoices and not just the one that matches the id — Craig Howell, Jun 12 '18 at 17:29
Still working with your answer but initially this is returning a blank array. I am confident all ids are being passed correctly. — Craig Howell, Jun 12 '18 at 18:01
@CraigHowell you can check it here https://mongoplayground.net/p/YdXfxgCnC5- — Ashh, Jun 12 '18 at 18:01
@CraigHowell your mongoose `_id` should be wrapped like `mongoose.Types.ObjectId(id)` — Ashh, Jun 12 '18 at 18:03
That is definitely getting the desired result, I will check my end and see what I am missing. — Craig Howell, Jun 12 '18 at 18:03
This is perfect. I tweaked it a little for my needs and have provided that in my question for anyone that may be interested. Thanks! — Craig Howell, Jun 12 '18 at 18:24

Filter nested array in mongodb?

1 Answers1