Group by count for "True" in collection without key in Query-CodePudding

My Data:

{"_id": "0x026EFF", "Stations": {"MP": false, "AS": true, "RW": true, "FT": true}},
{"_id": "0x026F00", "Stations": {"MP": null, "AS": true, "RW": true, "FT": false}},
{"_id": "0x026F01", "Stations": {"MP": null, "AS": true, "RW": false, "FT": null}},
{"_id": "0x026F02", "Stations": {"MP": null, "AS": null, "RW": true, "FT": false}},
{"_id": "0x026F03", "Stations": {"MP": null, "AS": true, "RW": null, "FT": false}}

Here is my query

Collection.aggregate([
    {"$group": {'_id': None,
                'MP': {'$sum': {'$cond': ["$Stations.MP", 1, 0]}},
                'AS': {'$sum': {'$cond': ["$Stations.AS", 1, 0]}},
                'RW': {'$sum': {'$cond': ["$Stations.RW", 1, 0]}},
                'FT': {'$sum': {'$cond': ["$Stations.FT", 1, 0]}}
                }
        },
    {'$project': {'_id': 0}}
])

mongoplayground

I got

[{"AS":4,"FT":1,"MP":0,"RW":3}]

My question is Can I rewrite my query without "MP", "AS", "RW", "FT"?

CodePudding user response：

$set - Set the Stations field with convert Stations from key-value pair to an array with the documents of k and v fields.
$unwind - Deconstruct Stations array to multiple documents.
$group - Group by Stations.k and perform sum by condition.
$group - Group by null to combine all documents into one and push the root document into data array.
$replaceWith - Replace the input document with:

5.1. $arrayToObject - Convert the array from the result 5.1.1 to key-value pair.

5.1.1. $map - Iterate the data array and return a new array with the documents containing k and v fields.

db.collection.aggregate([
  {
    $set: {
      Stations: {
        $objectToArray: "$Stations"
      }
    }
  },
  {
    $unwind: "$Stations"
  },
  {
    $group: {
      _id: "$Stations.k",
      count: {
        $sum: {
          $cond: {
            if: {
              $eq: [
                "$Stations.v",
                true
              ]
            },
            then: 1,
            else: 0
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $replaceWith: {
      $arrayToObject: {
        $map: {
          input: "$data",
          in: {
            k: "$$this._id",
            v: "$$this.count"
          }
        }
      }
    }
  }
])

Demo @ Mongo Playground