'How to write a Mongodb query to count collection rows

I am trying to achieve the following. I have a collection with to-do items based on categories. It has x number of row. There is functionality built to assign these to-do items to an owner. I want to show assignment progress for each category. How can I go about getting this query right. Appreciate the help

Category = [{ id: DB, Title: Database, Desc: Database Fixes, }, { id: NET, Title: Network, Desc: Network Fixes, }, ...]

County =

[{
id: AL
category_id: DB,
Title: Alabama,
},
{
id: AK
category_id: DB,
Title: Alaska
},
{
id: AZ
category_id: DB,
Title: Arizona
},
...]

ToDo =

id: TD1
County_id: AL,
Title: Installation,
assigned: 1,
user: ASED
},
{
id: TD2
County_id: AL,
Title: Documentation,
assigned: 0,
user: ""
},
{
id: TD3
County_id: AL,
Title: Migration,
assigned: 1,
user: ILKQ
},
{
id: TD4
County_id: AK,
Title: Documentation,
assigned: 0,
user: ""
},
{
id: TD5
County_id: AZ,
Title: Testing,
assigned: 0,
user: ""
},
{
id: TD6
County_id: AZ,
Title: Documentation,
assigned: 1,
user: JUSJ
}]

Expected Result=

[ {
    category_id: DB,
    Title: Database,
    Desc: Database Fixes,
    total_rec: 4,
    total_assigned: 2
    },
    {
    category_id: NET,
    Title: Network,
    Desc: Network Fixes,
    total_rec: 2,
    total_assigned:1
    }]
group-bymongodb-query


Solution 1:[1]

I guess you want something like this:

    db.Category.aggregate([
  {
    "$lookup": {
      "from": "county",
      "localField": "id",
      "foreignField": "category_id",
      "as": "county"
    }
  },
  {
    $project: {
      Desc: 1,
      county: "$county.id",
      Title: 1,
      id: 1
    }
  },
  {
    "$lookup": {
      "from": "ToDo",
      "localField": "county",
      "foreignField": "County_id",
      "as": "tasks"
    }
  },
  {
    "$project": {
      "total_assigned": {
        "$sum": {
          "$sum": "$tasks.assigned"
        }
      },
      "Desc": 1,
      total_rec: {
        $size: "$tasks"
      },
      category_id: "$id",
      Title: 1,
      _id: 0
    }
  },
])

starting from Category, using $lookup to bring all countries and then another one to get all ToDos. You can see how it works here on the playground.

BTW, notice I needed to change the category_id for Arizona in your example in order to match your wanted results...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 nimrod serok

Related Questions