$lookup foreigner collector from an aggregation?

[Andrea_Agostini]

Hi all,

I have a tricky use case.

I have written two aggregations:

AGGREGATION 1

            var runDataList = _runCollection
                .Aggregate()
                .Match(filter)
                .Lookup(
                    _clxCollection,
                    m => m.RunDefinition,
                    c => c.RunDefinition,
                    (RunDataFull m) => m.ClxParameterList
                    )
                .ToList();

In this aggregation the $lookup stages creates 1 key: ClxParameterList

AGGREGATION 2

            var clxList = _clxCollection
                .Aggregate()
                .Match(filter)
                .Lookup(
                    _clxCollection,
                    m => m.BaseClxName,
                    c => c.Name,
                    (BikeClxParametersFull m) => m.BaseParamaterValues
                    )
                .ToList();

In this aggregation the $lookup stage creates one key: BaseParamaterValues

This is ok, but I would like to mix them: basically I would like that the BaseParamaterValues key appears in the first aggregation; something like ClxParameterList.BaseParamaterValues

It is like if in the first aggregaton I replace _bikeClxParameterCollection with the output of the second aggregation (clxList) - I cannot do this becase the $lookup parameters must be collections, not aggregation results.

Has anyone any suggestion? I am using the C# driver.

[HomAskIe (Mongo Mongo)]

Hello! @Andrea_Agostini
Could you please provide some data from those collections? and what are the results of Aggregation 1 and 2?

If that’s convenience please make sample input and your expected output. That would help a lot .

[Andrea_Agostini]

@Andrea_Agostini

            var clxList = _clxCollection
                .Aggregate()
                .Match(filter)
                .Lookup(
                    _clxCollection,
                    m => m.BaseClxName,
                    c => c.Name,
                    (BikeClxParametersFull m) => m.BaseParamaterValues
                    )
                .ToList();

Hi,
thank you very much for your prompt answer. I ave created a little code that automatically fill up a local database, so you can run it and create your own data. Please have a look to the attached TXT file.
Program.txt (2.5 KB)

About my goal, please have a look to the attached image.

As you can see, the “clxList” result shows object with Id=2 plus there is a field “RefBase” that include the one with Id=3.
In the “runDataList” result, in the field RefClx I can see that the object with Id=2 is shown but I would like to see also a “RefBase” that include the one with Id=3, as in the previous result.
I expect that I have to change the data type from Clx to ClxFull, but I don’t know how to do the rest!

Thank you in advance,
Andrea

[HomAskIe (Mongo Mongo)]

Thank you for the information. I understand the problem and your expectation.
If I understand correctly, the output you’re looking for is something like this:

[
    {
        '_id': ObjectId('611cbd936eda2b9719b8780d'), 
        'id': 1, 
        'idClx': '2', 
        'refClx': {
            '_id': ObjectId('611cbd936eda2b9719b8780b'), 
            'id': '2', 
            'baseClx': 'BaseClx', 
            'name': 'MyClx'
        }, 
        'refBase': {
            '_id': ObjectId('611cbd936eda2b9719b8780c'), 
            'id': '3', 
            'baseClx': None, 
            'name': 'BaseClx'
            }
    }
]

If that’s correct. Here is how you can get that.

Make a new pipeline that from runData lookup for Clx by ID and then you unwind it (since the IdClx is single value so I think it’s safe to unwind). Then with that’s in hand you lookup for baseClx and one more time unwind it
(unwind: https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/)

Here is sample pipeline:
note: I’m a Python guy so this is Python Driver

pipiline = [
    {"$lookup": {
        "from": "clx",
        "localField": "idClx",
        "foreignField": "id",
        "as": "refClx"
     }},
     {"$unwind": "$refClx"},
     {"$lookup": {
         "from": "clx",
         "localField": "refClx.baseClx",
         "foreignField": "name",
         "as": "refBase"
     }},
     {"$unwind": "$refBase"}
]

Try it out and let me know if that helps. Feel free to ask more questions!

[Andrea_Agostini]

Hi @HomAskIe ,
thank you very much for your help! I have fully understand what you have done; thank you again

[HomAskIe (Mongo Mongo)]

glad it helps, please consider mark my answer as the solution