$lookup: subarrays

[Lasse_Johansen]

i have two collections i want to join

houses

[
    {
        "name": "house1",
        "events": [
            {
                "name": "Event1"
            },
            {
                "name": "Event2",
                "accountId": 123
            }
        ]
    }
]

accounts

[
    {
        "_id": 123,
        "balance": 22
    }
]

I want to join the 2 collections and get a result like this:

[
    {
        "name": "house1"
        "events": [
            {
                "name": "Event1"
            },
            {
                "name": "Event2",
                "accountId": 123,
                "account": {
                    "_id": 123,
                    "balance": 22
                }
            }
        ],
    }
]

Here is my incomplete aggregate solution so far:

{
    "$lookup": {
        "as": "eventAccounts",
        "foreignField": "_id",
        "from": "accounts",
        "localField": "events.accountId"
    }
}

Please help

[steevej (Steeve Juneau)]

I prefer to do this final assembly within the application (ans sometimes in the front-end) server rather than do it on the data server. It is easier to scale and use less bandwidth (in case of duplicate).

But to do it with the aggregation you will need

• a $set stage that uses $map
• the $map will use events as the input
• for each event being $map-ed, the entry with an account _id, you will need to $mergeObjects with the document from eventAccounts, that matches

Look at Aggregation not getting more than 1 of the document even though I have duplicated entries on purpose - #5 by steevej.

[Lasse_Johansen]

Thanks for the tip of why implementing it on app level is normally better. I have done so, just to move on.