MongoDB query with 2 counts - slow because of facet-CodePudding

I am new to MongoDB and I try to get a query working with 2 counts in it. I have a movement table where deliveries and returns are stored. Now I want to current stock which substract all counted deliveries minus all counted returns.

A delivery is marked with startID = 0 and toID = 40. A return is marked with startID = 40 and toID = 0.

So basically it is not a problem to get deliveries and returns by its own and do 2 queries. But I want to have the calculation right in the aggregation.

Single aggregation for delivieries < 80ms

[
  {
    '$match': {
      '$and': [
        {
          'startID': 0
        }, {
          'toID': 40
        }
      ]
    }
  }, {
    '$count': 'deliveries'
  }
]

Single aggregation for returns < 80ms

[
  {
    '$match': {
      '$and': [
        {
          'startID': 40
        }, {
          'toID': 0
        }
      ]
    }
  }, {
    '$count': 'returns'
  }
]

Now I want to compare these both counts and substract it in the end to get the stock. I tried it with facet but as I read it is not using indices.

First try with match before facet (compound index on startID and toID) > 1000ms!

[
  {
    '$match': {
      '$or': [
        {
          '$and': [
            {
              'toID': 0
            }, {
              'startID': 40
            }
          ]
        }, {
          '$and': [
            {
              'toID': 40
            }, {
              'startID': 0
            }
          ]
        }
      ]
    }
  }, {
    '$facet': {
      'returns': [
        {
          '$match': {
              'toID': 0
          }
        }, {
          '$count': 'returns'
        }
      ], 
      'deliveries': [
        {
          '$match': {
             'startID': 0
          }
        }, {
          '$count': 'deliveries'
        }
      ]
    }
  }, {
    '$unwind': {
      'path': '$deliveries'
    }
  }, {
    '$unwind': {
      'path': '$returns'
    }
  }, {
    '$addFields': {
      'stock': {
        '$subtract': [
          '$deliveries.deliveries', '$returns.returns'
        ]
      }
    }
  }
]

Second try without match before facet (no index could be used) > 4000ms!

[
  {
    '$facet': {
      'returns': [
        {
          '$match': {
            '$and': [
              {
                'toID': 0
              }, {
                'startID': 40
              }
            ]
          }
        }, {
          '$count': 'returns'
        }
      ], 
      'deliveries': [
        {
          '$match': {
            '$and': [
              {
                'toID': 40
              }, {
                'startID': 0
              }
            ]
          }
        }, {
          '$count': 'deliveries'
        }
      ]
    }
  }, {
    '$unwind': {
      'path': '$deliveries'
    }
  }, {
    '$unwind': {
      'path': '$returns'
    }
  }, {
    '$addFields': {
      'stock': {
        '$subtract': [
          '$deliveries.deliveries', '$returns.returns'
        ]
      }
    }
  }
]

Is there anything I can speed up this query or get rid of facet in this kind of usage?

Thank you!

CodePudding user response：

Given deliveries are the documents with toID=40 you can group all documents and add 1 if toID equals 40 or subtract 1 if not. You can put it right after $match and fully replace facet and etc.

{
  $group: {
    _id: null,
    stock: {
      $sum: {
        $cond: [
          {
            $eq: [
              "$toID",
              40
            ]
          },
          1,
          -1
        ]
      }
    }
  }
}