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
}

CodePudding user response：

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

CodePudding user response：

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