1

I have 2 collections:

users collection:

{
    "_id": "1234141341"
    "name": "David",
    "is_active": True,
    "age": 20
}

locations collection:

{
    "owner": "1234141341"
    "type": "Point"
    "coordinates": [ 105.843111, 21.045752 ]
}

I want to join users collection with locations collection. My query:

db.collection.aggregrate(
    {
        "$match": {
            "is_active": True,
        }
    },
    {
        "$lookup": {
            "from": "locations",
            "as": "location",
            "let": {"user_id": "$_id", "list_user_id": "the expression to get list of user id after $match stage"},
            "pipeline": [
                { 
                    "$geoNear": {
                        "near": {"type": "Point", "coordinates": [ 105.843111, 21.045752 ]},
                        "distanceField": "distance",
                        "query": {
                            // "owner": {"$in": "$$list_user_id"}      // <---- filter location that belong filtered user here
                        },
                    }
                },
                {
                    "$redact": {
                        "$cond": {
                            "if": {"$lte": ["$distance", 10000000000]},
                            "then": "$$KEEP",
                            "else": "$$PRUNE",
                        }
                    }
                },
                {
                    "$match": {
                        "$expr": {"$eq": ["$owner", "$$user_id"]},
                    },
                },
            ],
        }
    }
)

For optimize this aggregrate query, I want to get list of user id after stage $match, then pass this list of user id to stage $geoNear of $lookup for filter location that belong to filtered users. So this $geoNear stage will not calculate all locations. I have read mongo document, also read questions in this site, but I can not find out the answer. Any idea about this is appreciated, thanks

Alexander Phung
  • 75
  • 1
  • 9
  • what is that `$$list_user_id`? Can you just do `query: { owner: $$user_id}` – chakwok May 22 '20 at 05:35
  • @chakwok, `$$list_user_id` is what I mentioned above, but I don't know how to get this `list_user_id`. I have commented this line, it is just describe my idea. If using `query: { owner: $$user_id}` the `$geoNear` only calculate distance for only one user, but I want calculate distance for all filtered user after `$match` stage – Alexander Phung May 22 '20 at 05:51
  • Why do you need `$$list_user_id` if you are going to match with `$$user_id` anyways? – thammada.ts May 22 '20 at 17:15
  • Although this does not answer you question, but I guess `"$geoNear": { ... "query": { "$expr": { $eq: ['$owner', '$$user_id'] } } }` would get what you are trying to do – thammada.ts May 22 '20 at 17:21

1 Answers1

0

Normally when you want to do a filter in the collection being looked up you do:

{"$match": {
 "$expr": { "$in": [ "$owner", "$$user_id" ] }
}}

You can either add a $match stage at the beginning or I believe you can put the whole $expr expression into the query.

chakwok
  • 980
  • 8
  • 21
  • As mongodb document, `$geoNear` must be the first stage of pipeline, so I can not use `$match` before `$geoNear` in `pipeline` of `$lookup`. – Alexander Phung May 22 '20 at 06:37
  • How about putting the `$expr` in the `$geoNear.query`? – chakwok May 22 '20 at 06:38
  • I have edited my query a litle bit for make clearly my idea. I can put `$expr` into `geoNear.query`. I also intend to do that. But what make me stuck here is that how can i get list of user id after `$match` stage ? – Alexander Phung May 22 '20 at 06:45
  • I think `$$userId` is already the array you are referring to? The answer has an explanation. https://stackoverflow.com/questions/55033804/aggregate-lookup-does-not-return-elements-original-array-order – chakwok May 22 '20 at 07:02
  • The explanation can not applied for me. Let's disscuss again. After `$match: { is_active: True}` I will get a list of user, right ? I will use this list filtered user to do `$lookup` with `locations` collection. In `$lookup.let`, I defined a variable named `user_id=_id` is user id of current input document. But now I also want to define a variable 'list_user_id` that contain a list of user'id of all input document. How can I do that? – Alexander Phung May 22 '20 at 07:37
  • @PhùngXuânAnh I understand what you meant. What I am saying is that `$$userId` you defined in `$lookup.let` is not "the userId of current input document". Instead, it is the "list of userId of all input document". Take a look at mongodb document, `$in` takes two arguments, `{ $in: [ , ] }`, so the `$$user_id` is an array, but not a userId – chakwok May 22 '20 at 07:51
  • Ah, I know that, I also checked, `$$user_id` is just an id of an user. So I need to get list of all user id and assigne to variable `list_user_id`. Then I can do ` {"$match": { "$expr": { "$in": [ "$owner", "$$list_user_id" ] } }}` as you said above – Alexander Phung May 22 '20 at 08:02
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/214404/discussion-between-phung-xuan-anh-and-chakwok). – Alexander Phung May 22 '20 at 08:26
  • @PhùngXuânAnh Sorry, I totally misread your question. Not sure if it's the most straight forward solution, but it does the optimization you wanted. However, if you need the information from `users` you many need to lookup again... https://mongoplayground.net/p/WZYNDVhmdaO – chakwok May 22 '20 at 09:12
  • Sr for answer late, as your suggest, I am trying to use variable `$$userIds` inside `geoNear.query` instead of `$match`, like this: `$geoNear.query("owner": {$in: $$userIds})` but not successfully. It raise error: `$in needs an array` – Alexander Phung Jun 01 '20 at 06:38
  • @PhùngXuânAnh did you add the `$group` stage before the lookup as I did in the mongoplayground? I tested the query and it works fine – chakwok Jun 01 '20 at 06:46
  • Please review, My code here: https://mongoplayground.net/p/OXN5zayRzqe – Alexander Phung Jun 01 '20 at 08:55