Improving MongoDB queries having $facet stage

Question

In my query I have the first stage with $match to filter common conditions. And I want to perform a different set of conditions on the result set, hence using $facet. In the documents I saw $facet stage doesn't use indexes. Probably because of this reason my query is very slow.

Here's my data look like:

PROPERTY_ID OWNER_NAME  ISFORSALE   ISFORECLOSURE   ISFORRENT   CITY
    100         AA        true          false          true       abc
    101         AA        true          false          false      xyz
    102         BB        true          true           true       abc
    103         BB        true          false          false      abc
    104         BB        false         false          true       abc
    105         BB        true          false          true       abc
    106         CC        true          false          false      xyz

Initially I had the data loaded onto a postgres, which had the intended query as:

SELECT 
    COUNT(*) FILTER (WHERE ISFORSALE = true) AS SALE_CNT,
    COUNT(*) FILTER (WHERE ISFORECLOSURE = true) AS FORECLOSURE_CNT,
    COUNT(*) FILTER (WHERE ISFORRENT = true) AS RENT_CNT,
    COUNT(*) FILTER (WHERE ISFORRENT = true AND ISFORSALE = true) AS RENT_SALE_CNT
FROM property_table
WHERE CITY = 'abc'

With the mongodb implementation, the query looks like this:

[{
  $match: {CITY: 'abc'}
}, 
{
  $facet: {
    SALE: [{$match: {ISFORSALE: true}},{$count: 'count'}],
    FORECLOSURE: [{$match: {ISFORECLOSURE: true}},{$count: 'count'}],
    RENT: [{$match: {ISFORRENT: true}},{$count: 'count'}],
    RENT_SALE: [{$match: {ISFORRENT: true, ISFORSALE:true}},{$count: 'count'}],
    MULTIOWER: [{$match: {ISFORSALE: true } }, {$group: {_id: '$OWNER_NAME', count: {$sum: 1 } } }, {$match: {count: {$gte: 2, $lte: 4 } } }, {$count: 'count'} ]
  }
}, 
{
  $project: {
    SALE_CNT: {
      $cond: {
        'if': {$gt: [{$size: '$SALE'},0]},then: {$first: '$SALE.count'},
        'else': 0
      }
    },
    FORECLOSURE_CNT: {
      $cond: {
        'if': {$gt: [{$size: '$FORECLOSURE'},0]},then: {$first: '$FORECLOSURE.count'},
        'else': 0
      }
    },
    RENT_CNT: {
      $cond: {
        'if': {$gt: [{$size: '$RENT'},0]},then: {$first: '$RENT.count'},
        'else': 0
      }
    },
    RENT_SALE_CNT: {
      $cond: {
        'if': {$gt: [{$size: '$RENT_SALE'},0]},then: {$first: '$RENT_SALE.count'},
        'else': 0
      }
    },
    MULTIOWER_CNT: {
      $cond: {
        'if': {$gt: [{$size: '$MULTIOWER'},0]},then: {$first: '$MULTIOWER.count'},
        'else': 0
      }
    }
  }
}]

I've added index for the city, hence the match stages outputs the results very quickly.

But after reviewing the execution stats and some tests, I saw the $facet stage is taking too much time, and also it doesn't use indexes. My DB has nearly 150 million documents.

Total # of docs returned by the match stage would be roughly 800,000.
And not all docs satisfy the conditions I've mentioned as shown in the example dataset.

Is there a way in Mongo to either skip the facet but a way to do the above query efficiently (using the indexes) or any other different way?

user_3027266 · Answer

Not clear what you like to get as result but could be simple:

db.property_table.aggregate([
   { $match: {CITY: 'abc'} },
   { 
      $group: {
        _id: null,
        FORECLOSURE_CNT: {$sum: "$ISFORECLOSURE"},
        SALE_CNT: {$sum: "$ISFORSALE"},
        RENT_CNT: {$sum: "$ISFORRENT"}
      }
   }
])

For boolean values you can use $toInt

Input Type Behavior

Boolean Returns 0 for false

Returns 1 for true

db.collection.aggregate([
   { $match: { CITY: 'abc' } },
   {
      $group: {
         _id: null,
         FORECLOSURE_CNT: { $sum: { $toInt: "$ISFORECLOSURE" } },
         SALE_CNT: { $sum: { $toInt: "$ISFORSALE" } },
         RENT_CNT: { $sum: { $toInt: "$ISFORRENT" } }
      }
   }
])

Improving MongoDB queries having $facet stage

1 Answers