A
A
Alexey Nikolaev2021-08-02 15:21:54
MongoDB
Alexey Nikolaev, 2021-08-02 15:21:54

How to select subdocuments with pagination from a collection?

Good day.
There is a collection, let's call it Reports.

Reports collection document example
{
   _id: '123123123',
   name: 'Report 1',
   userStatuses: [
      {
         userId: '1234567',
         userEmail: '[email protected]`
      },
      {
         userId: '1234567',
         userEmail: '[email protected]`
      },
   ],
   createdAt: '2021-05-14 06:01:25.444Z'
}

There is another collection, UsersMap.
Sample UsersMap Collection Document
{
    "_id" : "YGh2gkct9C4cNce4j",
    "email" : "[email protected]",
    "external_id" : "12345678",
    "userAccessLevel" : "user",
    "createdAt" : "2019-08-13T20:16:07.175Z"
}

It is necessary to select from the Reports collection all subdocuments inside "userStatuses", with a join ($lookup'om) and with the possibility of pagination. That is, for the query to first select all userStatuses that have the "user" role in the UsersMap collection, remove duplicates (distinct) and then, based on the result, make skip & limit.

I did the aggregation through $lookup, however
- the selection is made by the Reports collection, but you need to select subdocuments
- duplicates inside userStatuses
I just can’t figure out how to make a selection from the individual entities of the collection, and even with distinct, and with skip \ limit.

Query with aggregation
db.Reports.rawCollection().aggregate(
  [
    {
      $match: {
        'createdAt': {
          $gte: dateRangeStart.toDate(),
          $lt: dateRangeEnd.toDate(),
        },
      }
    },
    {
      $lookup: {
      from: 'UsersMap',
      as: 'map',
      let: { userId: '$userStatuses.userId' },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ['$_id', '$$userId'] },
                { $eq: ['$userAccessLevel', 'user'] },
              ]
            }
          }
        }
      ]
    },
  ]
);


Tell me, how can I do this in Monge? Where to dig?
Thanks in advance.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question