Considering collection clothing of documents with this strucure

{
  "brand": <string>,
  "color": <string>,
  "size": <number>
}

In a single aggregation grouing by brand I would like to find two values:

1. min size for all documents in a group where color="blue"
2. max size for all documents in a group where color="red"

In SQL it would be something like this:

SELECT MIN(CASE color WHEN "blue" THEN size ELSE NULL END),
       MAX(CASE color WHEN "red" THEN size ELSE NULL END),
       brand
  FROM clothing
 GROUP BY brand

How to express this in MongoDB query?

CodePudding user response：

db.collection.aggregate([
  {
    "$match": {
      "color": { "$in": [ "red", "blue" ] }
    }
  },
  {
    "$group": {
      "_id": "$color",
      "max": { "$max": "$size" },
      "min": { "$min": "$size" }
    }
  }
])

mongoplayground

CodePudding user response：

1. $group - Group by brand and $push size into red or blue array by $cond.
2. $project - Decorate output document with $min (blue) and $max (red).

db.collection.aggregate([
  {
    "$group": {
      "_id": "$brand",
      "red": {
        $push: {
          $cond: [
            {
              $eq: [
                "$color",
                "red"
              ]
            },
            "$$ROOT.size",
            "$$REMOVE"
          ]
        }
      },
      "blue": {
        $push: {
          $cond: [
            {
              $eq: [
                "$color",
                "blue"
              ]
            },
            "$$ROOT.size",
            "$$REMOVE"
          ]
        }
      }
    }
  },
  {
    $project: {
      brand: "$brand",
      min: {
        $min: "$blue"
      },
      max: {
        $max: "$red"
      }
    }
  }
])

Sample Mongo Playground