Mongo db - how to join and sort two collection with pagination

Question

I have 2 collections:

1. Office -

{
  _id: ObjectId(someOfficeId),
  name: "some name",
  ..other fields
}

2. Documents -

{
  _id: ObjectId(SomeId),
  name: "Some document name",
  officeId: ObjectId(someOfficeId),
  ...etc
}

I need to get list of offices sorted by count of documetns that refer to office. Also should be realized pagination.

I tryied to do this by aggregation and using $lookup

const aggregation = [
        {
            $lookup: {
                from: 'documents',
                let: {
                    id: '$id'
                },
                pipeline: [
                    {
                        $match: {
                            $expr: {
                                $eq: ['$officeId', '$id']
                            },
                            // sent_at: {
                            //     $gte: start,
                            //     $lt: end,
                            // },
                        }
                    }
                ],
                as: 'documents'
            },
        },
        { $sortByCount:  "$documents" },
        { $skip: (page - 1) * limit },
        { $limit: limit },
    ];

But this doesn't work for me

Any Ideas how to realize this?

p.s. I need to show offices with 0 documents, so get offices by documets - doesn't work for me

Answer 1

There are two errors in your lookup

While passing the variable in with $let. You forgot the _ of the $_id local field

let: {
    id: '$id'
},

In the $exp, since you are using a variable id and not a field of the Documents collection, you should use $$ to make reference to the variable.

$expr: {
    $eq: ['$officeId', '$$id']
},