CODEWITHSUNDEEP
mongodbaggregation-framework
magos
magos

Reputation: 3511

How to use match after join in MongoDb?

I would like to filter nested properties after lookup in mongodb. I cannot find proper example in documentation. I use aggregation and in the last stage I have to pass few conditions. I want to filter by properties tightened in lookup stage.

This is my query:

        db.collection('leagues').aggregate([
                    {
                        $match: {
                            _id: ObjectID(leagueId)
                        }
                    },
                    {
                        $lookup: {
                            from: "league_configs",
                            localField: "_id",
                            foreignField: "leagueId",
                            as: "configs"
                        }
                    },
                    {
                        $lookup: {
                            from: "events",
                            localField: "_id",
                            foreignField: "leagueId",
                            as: "events"
                        }
                    },
                    {
                        $match: {
                            $and: [{
                                "events": {
                                    "isCalculated": {
                                        $eq: false
                                    },
                                    "isDeleted": {
                                        $eq: false
                                    },
                                    "startDate": {
                                        $lte: new Date()
                                    }
                                },
                            }]
                        }
                    }
            ])

Example league object: 

{
"_id" : ObjectId("57c6bf5934db7e18b8af650c"),
"name" : "League1",
"adminId" : ObjectId("57c5a08b8857ad1da099e8de"),
"isStarted" : false,
"isFinished" : false,
"users" : [ 
    ObjectId("57c5a08b8857ad1da099e8de")
],
"isPublic" : true,
"createdDate" : "2016-08-31T13:28:25+02:00",
"updatedDate" : ISODate("2016-08-31T12:28:09.064Z"),
"currentStage" : 1,
"isDeleted" : false
}

Example event object:

{
"_id" : ObjectId("57c6bfca34db7e18b8af650f"),
"home" : "home",
"guest" : "guest",
"description" : null,
"result" : "X",
"resultHome" : 1,
"resultGuest" : 1,
"startDate" : ISODate("2016-08-31T18:30:00.000Z"),
"type" : "1",
"specialPoints" : null,
"leagueStage" : 1,
"isDeleted" : false,
"isCalculated" : false,
"leagueId" : ObjectId("57c6bf5934db7e18b8af650c"),
"updatedDate" : ISODate("2016-09-02T06:52:45.544Z")
}

Any advices?

Upvotes: 1

Views: 2893

Answers (1)

notionquest
notionquest

Reputation: 39226

Here is the query. You can use "$elemMatch" to filter the data from "events" collection. Also, I have renamed the lookup result as "events_docs" rather than "event" for clarity.

Please note that I have removed the first lookup on "league_configs" as I don't have that collection data. You can add that back and try the full query.

The below query work fine with the data you have provided.

Query:-

db.leagues.aggregate([
                    {
                        $match: {
                            _id: ObjectId("57c6bf5934db7e18b8af650c")
                        }
                    },
                       {
                        $lookup: {
                            from: "events",
                            localField: "_id",
                            foreignField: "leagueId",
                            as: "events_docs"
                        }
                    },
                    {
                        $match: { "events_docs": {$elemMatch: {"isDeleted" : false,"isCalculated" : false, "startDate": {
                                        $lte: new Date()
                                    }}  } }

                    }

            ]);

Output:-

{
    "_id" : ObjectId("57c6bf5934db7e18b8af650c"),
    "name" : "League1",
    "adminId" : ObjectId("57c5a08b8857ad1da099e8de"),
    "isStarted" : false,
    "isFinished" : false,
    "users" : [ 
        ObjectId("57c5a08b8857ad1da099e8de")
    ],
    "isPublic" : true,
    "createdDate" : "2016-08-31T13:28:25+02:00",
    "updatedDate" : ISODate("2016-08-31T12:28:09.064Z"),
    "currentStage" : 1,
    "isDeleted" : false,
    "events_docs" : [ 
        {
            "_id" : ObjectId("57c6bfca34db7e18b8af650f"),
            "home" : "home",
            "guest" : "guest",
            "description" : null,
            "result" : "X",
            "resultHome" : 1,
            "resultGuest" : 1,
            "startDate" : ISODate("2016-08-31T18:30:00.000Z"),
            "type" : "1",
            "specialPoints" : null,
            "leagueStage" : 1,
            "isDeleted" : false,
            "isCalculated" : false,
            "leagueId" : ObjectId("57c6bf5934db7e18b8af650c"),
            "updatedDate" : ISODate("2016-09-02T06:52:45.544Z")
        }
    ]
}

Test Case 2:- I ran the same query for league id = ObjectId("57ce74076eae72aafab03013"). The query fetched zero documents.

League Document:-

{
    "_id" : ObjectId("57ce74076eae72aafab03013"),
    "name" : "League1",
    "adminId" : ObjectId("57c5a08b8857ad1da099e8de"),
    "isStarted" : false,
    "isFinished" : false,
    "users" : [ 
        ObjectId("57c5a08b8857ad1da099e8de")
    ],
    "isPublic" : true,
    "createdDate" : "2016-08-31T13:28:25+02:00",
    "updatedDate" : ISODate("2016-08-31T12:28:09.064Z"),
    "currentStage" : 1,
    "isDeleted" : false
}

Events Documents:-

This event doesn't have any league id.

{
    "_id" : ObjectId("57ce74256eae72aafab03014"),
    "home" : "home",
    "guest" : "guest",
    "description" : null,
    "result" : "X",
    "resultHome" : 1,
    "resultGuest" : 1,
    "startDate" : ISODate("2016-08-31T18:30:00.000Z"),
    "type" : "1",
    "specialPoints" : null,
    "leagueStage" : 1,
    "isDeleted" : false,
    "isCalculated" : false,
    "updatedDate" : ISODate("2016-09-02T06:52:45.544Z")
}

This event has league id (ObjectId("57ce74076eae72aafab03013")), however the isDeleted and isCalculated attributes are set to true. So, the query didn't fetch this document.

{
    "_id" : ObjectId("57ce74ea6eae72aafab03015"),
    "home" : "home",
    "guest" : "guest",
    "description" : null,
    "result" : "X",
    "resultHome" : 1,
    "resultGuest" : 1,
    "startDate" : ISODate("2016-08-31T18:30:00.000Z"),
    "type" : "1",
    "specialPoints" : null,
    "leagueStage" : 1,
    "isDeleted" : true,
    "isCalculated" : true,
    "leagueId" : ObjectId("57ce74076eae72aafab03013"),
    "updatedDate" : ISODate("2016-09-02T06:52:45.544Z")
}

Upvotes: 2

Related Questions