Question 116220 in Mongodb Question posted in
The official documentation can be found here.

Write a query in MongoDB to find the number of time entry comes in the collection

I have the mongo collection like this

[
    /* 1 */
    {
        "_id" : 1,
        "data_id" : 1,
        "data_test" : "sample1",
        "data_list" : [ 
            "sun"
        ],
    }

    /* 2 */
    {
        "_id" : 2,
        "data_id" : 1,
        "data_test" : "sample2",
        "data_list" : [ 
            "sun",
            "mon"
        ],
    }

    /* 3 */
    {
        "_id" : 3,
        "data_id" : 2,
        "data_test" : "sample3",
        "data_list" : [ 
            "tue"
        ],
    }
    /* 4 */
    {
        "_id" : 4,
        "data_id" : 2,
        "data_test" : "sample4",
        "data_list" : [ 
            "tue",
            "wed"
        ],
    }

]

I would like to query this to get the count of how many time an element of data_list appear on a single collection where there data_id is the same.
Something like this can be achieved using SQL query like this if the data_list is single element and not a list.

Select data_list, count(*) from analytics_customer where data_id = 1 group by data_list

The output should be like this if we query for only "data_id" : 1

{
    "sun": 2,
    "mon": 1
}

The output for "data_id" : 2

{
    "tue": 2,
    "wed": 1
}
Tags:

2

Answers


  1. 0

    You could use aggregation

    or if it’s a fixed amount of options in the list (7 days of week), you can run .count() 7 times and search for each day using $exists

    Login or Signup to reply.
  2. 0

    Working mongo playground example: https://mongoplayground.net/p/tJjmjcPQWer

    Process:

    • $match to select the data_ids that you want
    • $unwind to get all days as individual records so that we can group them
    • $group unique days and data_ids together. This also enables us to get a $count of the days
    • $group again by just data_id (or null) to get a single record per data id, but also $push the days so that they are in an array
    • $project the final format of the document to object using $arrayToObject.

    It is worth noting that you can change the root of the object, but it is simpler to keep it within the days field for manipulation.

    An aggregation always results an array rather than a single object.

    I’m sure there are better approaches.

    Query

    db.collection.aggregate([
  { $match: { "data_id": 1 } }, // If you want to aggregate for a single data_id
  {
    $unwind: "$data_list"
  },
  {
    $group: {
      _id: {
        data_id: "$data_id", // If you want to aggregate for all data_ids
        data_list: "$data_list",
        
      },
      count: {
        $count: {}
      }
    }
  },
  {
    $group: {
      _id: "$_id.data_id", // If you want to aggregate for all data_ids
      //_id: null,
      days: {
        $push: {
          k: "$_id.data_list",
          v: "$count"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      data_id: "$_id", // If you want to aggregate for all data_ids
      days: {
        $arrayToObject: "$days"
      }
    }
  }
])

    Result (for single id):

    [
  {
    "data_id": 1,
    "days": {
      "mon": 1,
      "sun": 2
    }
  }
]

    Result (for all ids):

    [
  {
    "data_id": 2,
    "days": {
      "tue": 2,
      "wed": 1
    }
  },
  {
    "data_id": 1,
    "days": {
      "mon": 1,
      "sun": 2
    }
  }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search