How to get filtered elements of an array field using $filter

Question

I have a train collection in MongoDB which has following rows:

{
        "_id" : ObjectId("594a441fcbd8f815dc42436d"),
        "trainnum" : "011Ф",
        "startplace" : "Tashkent",
        "destination" : "Samarkand",
        "weeks" : [
                "1",
                "2",
                "3"
        ],
        "vagons" : [
                {
                        "_id" : ObjectId("594a6a42231e5912342ff903"),
                        "numvag" : "01K",
                        "seatcount" : "30",
                        "reserves" : [
                                {
                                        "numseats" : "1-10",
                                        "numreserve" : "1",
                                        "_id" : ObjectId("594a6a42231e5912342ff904")
                                }
                        ],
                        "date2" : ISODate("2017-06-30T00:00:00Z"),
                        "date1" : ISODate("2017-06-05T00:00:00Z")
                },
                {
                        "_id" : ObjectId("594a6c0d231e5912342ff905"),
                        "numvag" : "02C",
                        "seatcount" : "01-10",
                        "reserves" : [
                                {
                                        "numseats" : "01-10",
                                        "numreserve" : "05",
                                        "_id" : ObjectId("594a6c0d231e5912342ff906")
                                }
                        ],
                        "date2" : ISODate("2017-06-21T00:00:00Z"),
                        "date1" : ISODate("2017-06-25T00:00:00Z")
                }
        ],
        "date2" : ISODate("2017-07-30T00:00:00Z"),
        "date1" : ISODate("2017-06-01T00:00:00Z"),
        "__v" : 0
}
{
        "_id" : ObjectId("594a69c4231e5912342ff900"),
        "trainnum" : "012A",
        "startplace" : "Tashkent",
        "destination" : "Samarkand",
        "updated_at" : ISODate("2017-06-21T12:42:44.893Z"),
        "weeks" : [
                "1",
                "2",
                "3",
                "4"
        ],
        "vagons" : [
                {
                        "_id" : ObjectId("594b54fe0925d110180abf7b"),
                        "numvag" : "01K",
                        "seatcount" : "30",
                        "reserves" : [ ],
                        "date2" : ISODate("2017-07-23T00:00:00Z"),
                        "date1" : ISODate("2017-06-30T00:00:00Z")
                },
                {
                        "_id" : ObjectId("594b55ede16d1908b8129b42"),
                        "numvag" : "02L",
                        "seatcount" : "40",
                        "reserves" : [ ],
                        "date2" : ISODate("2017-07-11T00:00:00Z"),
                        "date1" : ISODate("2017-06-29T00:00:00Z")
                }
        ],
        "date2" : ISODate("2017-07-30T00:00:00Z"),
        "date1" : ISODate("2017-06-01T00:00:00Z"),
        "__v" : 0
}

How can I retrieve trains with all vagons which have date1 (vagons date1) > current day(22.06.2017) (Vagons.date1>current day). The result should be something like this:

{
"_id" : ObjectId("594a441fcbd8f815dc42436d"),
"trainnum" : "011Ф",
"startplace" : "Tashkent",
"destination" : "Samarkand"        
"weeks" : [
            "1",
            "2",
            "3"
    ],
    "vagons" : [
            {
                    "_id" : ObjectId("594a6c0d231e5912342ff905"),
                    "numvag" : "02C",
                    "seatcount" : "01-10",
                    "reserves" : [
                            {
                                    "numseats" : "01-10",
                                    "numreserve" : "05",
                                    "_id" : ObjectId("594a6c0d231e5912342ff906")
                            }
                    ],
                    "date2" : ISODate("2017-06-21T00:00:00Z"),
                    "date1" : ISODate("2017-06-25T00:00:00Z")
            }
    ],
    "date2" : ISODate("2017-07-30T00:00:00Z"),
    "date1" : ISODate("2017-06-01T00:00:00Z"),
    "__v" : 0
}
`{
    "_id" : ObjectId("594a69c4231e5912342ff900"),
    "trainnum" : "012A",
    "startplace" : "Tashkent",
    "destination" : "Samarkand",
    "updated_at" : ISODate("2017-06-21T12:42:44.893Z"),
    "weeks" : [
            "1",
            "2",
            "3",
            "4"
    ],
    "vagons" : [
            {
                    "_id" : ObjectId("594b54fe0925d110180abf7b"),
                    "numvag" : "01K",
                    "seatcount" : "30",
                    "reserves" : [ ],
                    "date2" : ISODate("2017-07-23T00:00:00Z"),
                    "date1" : ISODate("2017-06-30T00:00:00Z")
            },
            {
                    "_id" : ObjectId("594b55ede16d1908b8129b42"),
                    "numvag" : "02L",
                    "seatcount" : "40",
                    "reserves" : [ ],
                    "date2" : ISODate("2017-07-11T00:00:00Z"),
                    "date1" : ISODate("2017-06-29T00:00:00Z")
            }
    ],
    "date2" : ISODate("2017-07-30T00:00:00Z"),
    "date1" : ISODate("2017-06-01T00:00:00Z"),
    "__v" : 0
}`

Neil Lunn · Accepted Answer · 2017-06-22 07:48:23Z

1

Just apply the $filter condition on the field. Use $gt for the "cond" argument.

If you don't have MongoDB 3.4 or greater, then use $project instead of $addFields and simply include all the other fields in the projection:

Train.aggregate([
  { "$match": {
    "vagons.date1": { "$gte": new Date("2017-06-22") }
  }},
  { "$addFields": {
    "vagons": {
      "$filter": {
        "input": "$vagons",
        "as": "v",
        "cond": { "$gt": [ "$$v.date1", new Date("2017-06-22") ] }
      }
    }
  }}
])

Gives the output:

{
        "_id" : ObjectId("594a441fcbd8f815dc42436d"),
        "trainnum" : "011Ф",
        "startplace" : "Tashkent",
        "destination" : "Samarkand",
        "weeks" : [
                "1",
                "2",
                "3"
        ],
        "vagons" : [
                {
                        "_id" : ObjectId("594a6c0d231e5912342ff905"),
                        "numvag" : "02C",
                        "seatcount" : "01-10",
                        "reserves" : [
                                {
                                        "numseats" : "01-10",
                                        "numreserve" : "05",
                                        "_id" : ObjectId("594a6c0d231e5912342ff906")
                                }
                        ],
                        "date2" : ISODate("2017-06-21T00:00:00Z"),
                        "date1" : ISODate("2017-06-25T00:00:00Z")
                }
        ],
        "date2" : ISODate("2017-07-30T00:00:00Z"),
        "date1" : ISODate("2017-06-01T00:00:00Z"),
        "__v" : 0
}
{
        "_id" : ObjectId("594a69c4231e5912342ff900"),
        "trainnum" : "012A",
        "startplace" : "Tashkent",
        "destination" : "Samarkand",
        "updated_at" : ISODate("2017-06-21T12:42:44.893Z"),
        "weeks" : [
                "1",
                "2",
                "3",
                "4"
        ],
        "vagons" : [
                {
                        "_id" : ObjectId("594b54fe0925d110180abf7b"),
                        "numvag" : "01K",
                        "seatcount" : "30",
                        "reserves" : [ ],
                        "date2" : ISODate("2017-07-23T00:00:00Z"),
                        "date1" : ISODate("2017-06-30T00:00:00Z")
                },
                {
                        "_id" : ObjectId("594b55ede16d1908b8129b42"),
                        "numvag" : "02L",
                        "seatcount" : "40",
                        "reserves" : [ ],
                        "date2" : ISODate("2017-07-11T00:00:00Z"),
                        "date1" : ISODate("2017-06-29T00:00:00Z")
                }
        ],
        "date2" : ISODate("2017-07-30T00:00:00Z"),
        "date1" : ISODate("2017-06-01T00:00:00Z"),
        "__v" : 0
}

answered Jun 22, 2017 at 7:48

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

4 Comments

Frank Over a year ago

Works correctly. But it returns only _id and vagons fields. How can I add rest of the fields (trainnum, startplace, destination, weeks)? And can I add some condition for theese fields (trainnum, startplace ...)?

Neil Lunn Over a year ago

@Frank I'm using $addFields here because all that will do is "overwrite" the "vagons" field and replace it with the filtered content. If your MongoDB version is less than 3.4 and you do not have that pipeline operator, then $project must include "all" the fields. i.e { "$project": { "trainnum": 1, "startplace": 1 ... } etc. But I did say that in the answer.

Frank Over a year ago

got that!. How can I add conditions for weeks field in this aggregation

Neil Lunn Over a year ago

@Frank try and adapt what you have here. If you still cannot work it out then Ask a new Question. It's the clearest way. All questions that differ from your original are new questions anyway.

Collectives™ on Stack Overflow

How to get filtered elements of an array field using $filter

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related