Why doesn't $lookup work when connected to online archive?

Zarif_Alimov · 2024-01-20T17:53:26.345Z

I am trying to use the $lookup stage in my aggregation pipeline on an online archive database.

Let’s say I have 2 collections: person and person_logs. Docs in person_logs are archived at 6 months, but person is not archived. When connected to my online archive DB, both collections are visible and querable. However, if I try to aggregate person_logs such that I’m also looking up the person collection, the query just hangs. No errors are thrown, it just doesn’t ever return a result.

Same aggregation returns a normal result if connected to the live (not archive) DB.

Just FYI, here is the aggregation I’m using on the person_logs collection:

[
    {
        $match: {date_created: {$gt: start_date, $lt: end_date}}
    },
    {
        '$lookup': {
            'from': 'persons',
            'localField': 'person_id',
            'foreignField': '_id',
            'as': 'person'
        }
    },
    {
        $limit: 10
    }
]

What could be the issue? Are the indexes inactivated somehow in the archive DB?

Leandro_Domingues · 2024-01-22T12:58:52.900Z

Hi @Zarif_Alimov and welcome to the Community Forums.

With $lookup you can merge different collections in the same database, the same cluster or even different clusters (if they are “linked”). However, this is not allowed when it comes to online archive. To perform this operation you need to restore the collection to a database in your “hot cluster”.

Hope this helps!

Best.

Ashish_Negi · 2024-04-22T19:05:23.168Z

As per Atlas Data Federation and Online Archive Can Now Be Deployed in Azure | MongoDB Blog, advanced aggregation like $lookup is now supported.