How to count specific documents in a row using aggregation?

Question

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.

turivishal · Accepted Answer · 2020-11-28 05:53:57Z

1

You can try,

$group by null and prepare one array call root of all documents
$reduce to iterate loop of root array and set initial value result array and status default 0, check condition if delivery_result_id is 0 and initial value is true, then concat object with initial value result otherwise set status false
$unwind deconstruct result array
$replaceRoot replace to root result object

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" } }
])

Playground

answered Nov 28, 2020 at 5:53

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

codemonkey Over a year ago

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 Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

How to count specific documents in a row using aggregation?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related