How to make mongodb work with countDocuments() and $addFields

user_4467204 0
Viewed 637

I have a document with the following structure:

{
    '_id': '',
    'a': '',
    'b': '',
    'c': [
        {
            '_id': '',
            'd': '',
            'f': [
                {
                    'orderDate': 12345,
                    'orderProfit': 12,
                },
                {
                    'orderDate': 67891,
                    'orderProfit': 12341,
                },
                {
                    'orderDate': 23456,
                    'orderProfit': 474,
                },
            ],
        },
        {
            '_id': '',
            'd': '',
            'f': [
                {
                    'orderDate': 14232,
                    'orderProfit': 12222,
                },
                {
                    'orderDate': 643532,
                    'orderProfit': 4343,
                },
                {
                    'orderDate': 33423,
                    'orderProfit': 5555,
                },
            ],
        },
    ],
}

orderDate is an int64 that represents the date that an order was made

orderProfit is an int64 that represents the profit of an order

I needed to return the document that had the biggest "orderDate" and check if the "orderProfit" was the one i was looking for. For that matter I used a query like this (in an aggregate query):

[
    {
        '$addFields': {
            'orders': {
                '$map': {
                    'input': '$c',
                    'as': 'c',
                    'in': {
                        'profit': {
                            '$filter': {
                                'input': '$$c.f',
                                'cond': {
                                    '$eq': [
                                        {
                                            '$max': '$$c.f.orderDate',
                                        },
                                        '$$this.orderDate',
                                    ],
                                },
                            },
                        },
                    },
                },
            },
        },
    },
    {
        '$match': {
            '$or': [
                { 'orders.profit.orderProfit': 500 },
            ],
        },
    },
];

It is working properly.

The issue comes when trying to add this query to a countDocuments() query in order to fetch the total number of documents.

It is a requirement to use the countDocuments().

I just can't seem to make it work...

$addFields throws as an unknown top level operator. if I remove the $addFields then I can't add to the countDocuments() the query that finds the max date. If I totally remove it $match is an unknown operator.

db.getCollection('orders').countDocuments(
    {
"orders.profit.orderProfit" : {"Query that was shown previously"}

})
aggregation-framework mongodb-query mongodb
2 Answers

You can't.

countDocuments receives a find query, you can't attach an entire pipeline to it.

However countDocuments is just a wrapper.

Returns the count of documents that match the query for a collection or view. The method wraps the $group aggregation stage with a $sum expression to perform the count and is available for use in Transactions.

Basically this method just executes the following aggregation:

db.collection.aggregate([
    {
        $match: yourQuery
    },
    {
        $group: {
            _id: null,
            sum: {$sum: 1}
        }
    }
])

And then returns results[0].sum or 0 depending on result.

So you can just use your own pipeline and add this stage at the end, it would literally be the same complexity wise.

db.collection.aggregate([
     ...
     your entire pipeline
     ...
    {
        $group: {
            _id: null,
            sum: {$sum: 1}
        }
    }
])

If there's any other specific reason you want to not use the aggregation framework let me know, maybe there's a workaround.

user_107703700

The countDocuments() can't allow aggregation pipeline query,

You can use aggregation operators in match query but it cause the performance issues, you can use this when you don't have any way.

  • $let Binds variables for use in the specified expression, and returns the result of the expression,
  • vars, create variable for orders for your $addFields operation,
  • in, $map to iterate loop of $$orders.profit.orderProfit nested array and check $in condition if your profit amount found then it will return true otherwise false
  • $anyElementTrue will check returned is true then condition will true otherwise fales
db.getCollection('orders').countDocuments({
  $expr: {
    $let: {
      vars: {
        orders: {
          "$map": {
            "input": "$c",
            "as": "c",
            "in": {
              "profit": {
                "$filter": {
                  "input": "$$c.f",
                  "cond": {
                    "$eq": [{ "$max": "$$c.f.orderDate" }, "$$this.orderDate"]
                  }
                }
              }
            }
          }
        }
      },
      in: {
        $anyElementTrue: {
          $map: {
            input: "$$orders.profit.orderProfit",
            in: { $in: [500, "$$this"] }
          }
        }
      }
    }
  }
})

Playground

Second option, other way to handle this condition with less operators,

  • $filter to match your both condition with orderProfit
  • $size to get total result from above $filter result
  • now $map will return array of size that returned by filter
  • $sum to sum that return result number, if its greater than 0 then condition will be true otherwise false
db.getCollection('orders').countDocuments({
  $expr: {
    $sum: {
      "$map": {
        "input": "$c",
        "as": "c",
        "in": {
          $size: {
            "$filter": {
              "input": "$$c.f",
              "cond": {
                $and: [
                  { "$eq": [{ "$max": "$$c.f.orderDate" }, "$$this.orderDate"] },
                  { "$eq": ["$$this.orderProfit", 500] }
                ]
              }
            }
          }
        }
      }
    }
  }
})

Playground

user_89871280
Related