Using $lookup with sort

Working with Data
1

Hi,

I have a fairly simple use case but I am struggling to get it to work as I’d like.

Collections:
Cases:
-_id
-Description
… more fields

Items:
-_id
-Description
-CurrentOfficeName
…more fields

Now, we used to have a array field in Cases called ‘items’ that stores the id’s of the items that belong to that particular case as an unbounded array. That has worked up until now, but we’d like to change the system so a case can have over 100k items. So I created a join table to handle that:

CaseItems:
-_id
-CaseId
-ItemId
-OrganizationId

So now, we’re using $lookup to retrieve all items that belong to a case like so:


db.getCollection('caseItems').aggregate([
   {
      "$match":{
         "CaseId":NumberLong(3067920),
         "OrganizationId":NumberLong(1)
      }
   },
   {
      "$lookup":{
         "from":"items",
         "localField":"ItemId",
         "foreignField":"_id",
         "as":"result"
      }
   },
   {
      "$replaceRoot":{
         "newRoot":{
            "$mergeObjects":[
               "$$ROOT",
               {
                  "$arrayElemAt":[
                     "$result",
                     0
                  ]
               }
            ]
         }
      }
   },
   {
      "$sort":{
         "CurrentOfficeName":-1
      }
   },
   {
      "$skip":0
   },
   {
      "$limit":1000
   }
])

The issue I’m having is that the $sort field makes the query take 15+ seconds (The sort field on the items collection is an index). If I remove the sort field then the query is very fast.

So, my question is: Can Mongo db handle this? If so, then what am I doing incorrectly?

Thanks for all the help!
AJ

2

I would try to $sort after the $match. After the $replaceRoot the index cannot be used because the resulting documents are not in the index.

Also I think the index should be

CaseId:1,OrganizationId:1,CurrentOfficeName:1
2 Likes
3

Thanks for the quick response!

After the match, I’m in the caseItems collection which doesn’t have the CurrentOfficeName field. That field is inside of the items collection. Can this work? I’ll test

4

True. I have not notice that. The index I suggested is useless in this case. Sorry for misleading.

Your $lookup from:items will create the 100k items, so you might reach limits.

I do not understand the separation of items and caseItems, it looks like there is a 1-1 relationship between the 2.

If you want to sort by CurrentOfficeName you might need to move the field into caseItems.

5

In the past, we had an array of items in the cases collection. But we can’t have an array of 100k items in there because it will run too slowly.

I need to be able to sort the items collection by any of the fields in items. If I must move the fields to caseitems then that defeats the purpose.

6

Correction, in the past, we had an array of item ids in the cases collection*