Finding max array count with hour interval in MongoDb-CodePudding

I have a collection in MongoDb and the data in it is as follows.

[
{
    "_id" : ObjectId(""),
    "at" : ISODate("2022-03-27T11:56:00.000Z"),
    "personIds" : 13355,
    "productIds" : [ 
        "c3cc8b62-f9f7-4845-8585-effb1efd790d", 
        "af59b890-61b6-45a6-b2a1-32e16ba1a136"
    ]
},
{
    "_id" : ObjectId(""),
    "at" : ISODate("2022-03-28T11:56:00.000Z"),
    "personIds" : 13355,
    "productIds" : [ 
        "c3cc8b62-f9f7-4845-8585-effb1efd790d", 
        "af59b890-61b6-45a6-b2a1-32e16ba1a136",
        "dg59b890-gdb6-4ere-asdd-23e16ba123a4"
    ]
},
{
    "_id" : ObjectId(""),
    "at" : ISODate("2022-03-29T11:57:00.000Z"),
    "personIds" : 10347,
    "productIds" : [ 
        "920ba4e4-8d51-4e87-b40a-5d3f7b78d3ba"
    ]
}
]

What I need here is to find the data with the highest productIds size from the data whose personId is 13355 or 10347, date is between 28-03-2022 and 30-03-2022, and hour range is between 10 and 22.

The output should be: {count:3}

CodePudding user response：

Hope this code will help to you

db.collection.aggregate([
  {
    "$project": {
      hours: {
        "$hour": "$at"
      },
      count: {
        $size: "$productIds"
      },
      at: 1,
      personIds: 1
    }
  },
  {
    "$match": {
      personIds: {
        $in: [
          13355,
          10347
        ]
      },
      at: {
        $gte: ISODate("2022-03-28T00:00:00Z"),
        $lt: ISODate("2022-03-30T00:00:00Z")
      },
      hours: {
        $gte: 10,
        $lte: 22
      }
    }
  },
  {
    $group: {
      _id: null,
      count: {
        $max: "$count"
      }
    }
  },
  
])

Code with output

https://mongoplayground.net/p/U9LxyP4NQWN

CodePudding user response：

You can use an aggregation pipeline and try this:

db.collection.aggregate([
  {
    "$match": {
      personIds: {
        "$in": [
          13355,
          10347
        ]
      }
    }
  },
  {
    "$project": {
      at: {
        "$dateToParts": {
          "date": "$at"
        }
      },
      count: {
        "$size": "$productIds"
      },
      
    }
  },
  {
    "$match": {
      "at.day": {
        $gte: 28,
        $lt: 31
      },
      "at.hour": {
        $gte: 10,
        $lte: 22
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "count": {
        "$max": "$count"
      }
    }
  },
  {
    "$project": {
      "_id": 0
    }
  }
])

Playground link.