How to count specific documents in a row using aggregation?

发布于 2020-11-28 05:25:53

The collection I'm querying looks like this:

[
  {
    "_id": {"$oid": "5fa8408978d1511e9b6ecee9"},
    "client_id": {"$oid": "5f8d29a2beffbd2e00f81a54"},
    "date_created": {"$date": "2020-11-08T19:01:29.460Z"},
    "delivery_result_id": 0,
    "lead_id": {"$oid": "5fa84082c9ac031d0c4b6c01"}
  },
  {
    "_id": {"$oid": "5fa825495ee4001d2e0c2122"},
    "client_id": {"$oid": "5f8d29a2beffbd2e00f81a54"},
    "date_created": {"$date": "2020-11-08T17:05:13.126Z"},
    "delivery_result_id": 0,
    "lead_id": {"$oid": "5fa82527c9ac031d0c4b6bfe"}
  },
  {
    "_id": {"$oid": "5fa825495ee4001d2e0c2121"},
    "client_id": {"$oid": "5f8d29a2beffbd2e00f81a54"},
    "date_created": {"$date": "2020-11-08T17:05:13.125Z"},
    "delivery_result_id": 0,
    "lead_id": {"$oid": "5fa8251fc9ac031d0c4b6bfb"}
  },
  {
    "_id": {"$oid": "5fa825495ee4001d2e0c2120"},
    "client_id": {"$oid": "5f8d29a2beffbd2e00f81a54"},
    "date_created": {"$date": "2020-11-08T17:05:13.125Z"},
    "delivery_result_id": 100,
    "lead_id": {"$oid": "5fa82516c9ac031d0c4b6bf8"}
  },
  {
    "_id": {"$oid": "5fa823e08b542d1d1c91d30e"},
    "client_id": {"$oid": "5f8d29a2beffbd2e00f81a54"},
    "date_created": {"$date": "2020-11-08T16:59:12.362Z"},
    "delivery_result_id": 0,
    "lead_id": {"$oid": "5fa823d9c9ac031d0c4b6bf5"}
  },
  {
    "_id": {"$oid": "5fa823e08b542d1d1c91d30a"},
    "client_id": {"$oid": "5f8d29a2beffbd2e00f81a54"},
    "date_created": {"$date": "2020-11-08T16:59:12.253Z"},
    "delivery_result_id": 0,
    "lead_id": {"$oid": "5fa823d3c9ac031d0c4b6bf2"}
  },
  {
    "_id": {"$oid": "5fa823e08b542d1d1c91d300"},
    "client_id": {"$oid": "5f8d29a2beffbd2e00f81a54"},
    "date_created": {"$date": "2020-11-08T16:59:12.125Z"},
    "delivery_result_id": 100,
    "lead_id": {"$oid": "5fa823c1c9ac031d0c4b6bef"}
  },
  {
    "_id": {"$oid": "5fa823e08b542d1d1c91d2ff"},
    "client_id": {"$oid": "5f8d29a2beffbd2e00f81a54"},
    "date_created": {"$date": "2020-11-08T16:59:12.125Z"},
    "delivery_result_id": 100,
    "lead_id": {"$oid": "5fa823bac9ac031d0c4b6bec"}
  }
]

I'm trying to figure out how many documents in a row have delivery_result_id of 0 starting from the latest document. My query has to include a cut off date beyond which I don't bother looking. But in the docs that came in after the date I need to could successive delivery_result_id of 0. Here is my aggregation:

db.lead_delivery_logs.aggregate([
        {
            $match: {
                client_id: ObjectId('5f8d29a2beffbd2e00f81a54'),
                date_created: {$gte: new Date("2020-11-07T16:59:12.362Z")}
            }
        },
        {
            $sort : {_id : -1}
        },
        {
            $match: {
                /* This is where I need to figure out that count */
            }
        },
    ])

Since I only need the count, ideally, the aggregation would return 3 from the collection above, because there are 3 docs that match delivery_result_id of 0 and then comes one that's 100 so the count stops.

Questioner

codemonkey

Viewed

Original

db.collection.aggregate([ { $match: { client_id: ObjectId("5f8d29a2beffbd2e00f81a54"), date_created: { $gte: new Date("2020-11-07T16:59:12.362Z") } } }, { $sort: { _id: -1 } }, { $group: { _id: null, root: { $push: "$$ROOT" } } }, { $project: { root: { $reduce: { input: "$root", initialValue: { result: [], status: true }, in: { $cond: [ { $and: [ { $eq: ["$$this.delivery_result_id", 0] }, { $eq: ["$$value.status", true] } ] }, { result: { $concatArrays: ["$$value.result", ["$$this"]] }, status: true }, { result: "$$value.result", status: false } ] } } } } }, { $unwind: "$root.result" }, { $replaceRoot: { newRoot: "$root.result" } } ])

codemonkey 2020-11-28 06:05:28

Very clever indeed. I certainly didn't expect it to be this elaborate. If you had the choice of doing it this way OR returning all the matching documents from the first 2 conditions of the pipeline back into your app and doing this calculation inside your app (using JS or whatever language), would you still do it inside an aggregation? Seems like a lot of work for Mongo.

turivishal 2020-11-28 06:09:06

of course i will do it in my language client side, we can save 4 stages of processing from mongo and client side language would do that in less time.

codemonkey 2020-11-28 06:11:17

Make sense. Little doubt that your post answers my question though. Thank you.

How to count specific documents in a row using aggregation?

热门帖子

热门github