node.js - mongoose aggregate count by different criterias and group them

Question

I have about 1000 documents in the following format in a collection in mongoDB:

{
 isOrder:true,
 item:
      {
       location:'A',
       type:'AA'
      }
 }

Where isOrder is Boolean, location can be A or B, and type can be AA,AB,BB,BA for example, or more. Location and type are strings. I would like to group by type, and count the items by the other two property. Like so:

[
{
 type:'AA',
 orderA: <count of orders in location A>,
 orderB: <count of orders in location B>,
 stockA: <count of non-orders in location A>,
 stockB: <count of non-orders in location B>,
},
 type:'AB',
 orderA: <count of orders in location A>,
 orderB: <count of orders in location B>,
 stockA: <count of non-orders in location A>,
 stockB: <count of non-orders in location B>,
}
]

I tried aggreagate, match, facet... for all day long... I was the closest with facet, i was able to get the last 4 property, but not grouped by type... Please help

标签： node.jsmongodbmongoose

Answer 1

You can try this query:

The basic idea is:

• Group by type
• Then use $sum with $cond to add 1 or 0 if condition is match. For each key value there is a different condition

db.collection.aggregate([
  {
    "$group": {
      "_id": "$item.type",
      "orderA": {
        "$sum": {
          "$cond": [
            {
              "$and": [
                {
                  "$eq": ["$isOrder",true]
                },
                {
                  "$eq": ["$item.location","A"]
                }
              ]
            },
            1,
            0
          ]
        }
      },
      "orderB": {
        "$sum": {
          "$cond": [
            {
              "$and": [
                {
                  "$eq": ["$isOrder",true]
                },
                {
                  "$eq": ["$item.location","B"]
                }
              ]
            },
            1,
            0
          ]
        }
      },
      "stockA": {
        "$sum": {
          "$cond": [
            {
              "$and": [
                {
                  "$eq": ["$isOrder",false]
                },
                {
                  "$eq": ["$item.location","A"]
                }
              ]
            },
            1,
            0
          ]
        }
      },
      "stockB": {
        "$sum": {
          "$cond": [
            {
              "$and": [
                {
                  "$eq": ["$isOrder",false]
                },
                {
                  "$eq": ["$item.location","B"]
                }
              ]
            },
            1,
            0
          ]
        }
      }
    }
  }
])

Example here