Mongodb aggregation to pass both a matched array and an unmatched array

Question

I've got a MongoDB / Nodes aggregation that looks a little like this (there are other values in there, but this is the basic idea).

            [
            {
            '$unwind': {
                'path': '$Vehicles'
                }
            },
            {
            '$match': {
                'Vehicles.Manufacturer': 'FORD'
                }
            },
            {
            '$facet': {
                'makes': [
                        {
                    '$group': {
                    '_id': '$Vehicles.Manufacturer', 
                    'count': {
                        '$sum': 1
                                }
                            }
                        }
                    ]
                }
            },
            {
            '$project': {
                'makes': {
                '$sortArray': {
                    'input': '$makes', 
                    'sortBy': 1
                        }
                    }
                }
            }
        ]

This works fine. But I would also like to pass an unmatched list through. IE an an array of vehicles whose Manufacturer = FORD and an other list of all Manufacturer.

Can't get it to work. Any ideas please?

Thanks in advance.

Edit:-

The current output looks like this:

      [{
    "makes": [
      {
        "_id": "FORD",
        "count": 285
      }
    ]
  }]

and ideally it would look something like this:

      [{
    "makes": [
      {
        "_id": "FORD",
        "count": 285
      }
    ],
    "unfiltered_makes": [
      {
        "_id": "ABARTH",
        "count": 1
      },
      {
        "_id": "AUDI",
        "count": 7
      },
      {
        "_id": "BMW",
        "count": 2
      },
      {
        "_id": "CITROEN",
        "count": 4
      },
      {
        "_id": "DS",
        "count": 1
      },
      {
        "_id": "FIAT",
        "count": 1
      }.... etc
    ]
  }]

The data looks a bit like this:

"Vehicles": [
{
  "Id": 1404908,
  "Manufacturer": "MG",
  "Model": "3",
  "Price": 11995 .... etc
},{
  "Id": 1404909,
  "Manufacturer": "FORD",
  "ManufacturerId": 34,
  "Model": "Focus",
  "Price": 12000 .... etc
} ... etc
]

user_18482310 · Answer

In this case you can do something like:

db.collection.aggregate([
  {$unwind: "$Vehicles"},
  {$group: {
      _id: "$Vehicles.Manufacturer",
      count: {$sum: 1}}
  },
  {$facet: {
      makes: [{$match: {_id: "FORD"}}],
      unfiltered_makes: [{$group: {_id: 0, data: {$push: "$$ROOT"}}}]
    }
  },
  {$project: {makes: 1, unfiltered_makes: "$unfiltered_makes.data"}}
])

See how it works on the playground example

Another option is:

db.collection.aggregate([
  {$unwind: "$Vehicles"},
  {$group: {
      _id: "$Vehicles.Manufacturer",
      count: {$sum: 1}}
  },
  {$group: {
      _id: 0,
      unfiltered_makes: {$push: "$$ROOT"},
      makes: {$push: {$cond: [{$eq: ["$_id", "FORD"]}, "$$ROOT", "$$REMOVE"]}}
    }
  }
])

See how it works on the playground example

user_1409374 · Answer

Here's another way to do it using "$function" to generate a histogram of "Manufacturer" and format the returned array. The javascript function only traverses the "Vehicles" array once, so this may be fairly efficient, although I did not do algorithm timing comparisons on a large collection.

N.B.: I'm a javascript noob and there may be a better way to do this.

db.collection.aggregate([
  {
    "$set": {
      "unfiltered_makes": {
        "$function": {
          // generate histogram of manufacturers and format output
          "body": "function(makes) {const m = new Object();makes.forEach((elem) => {m[elem.Manufacturer] = m[elem.Manufacturer] + 1 || 1});return Object.entries(m).map(([make, count]) => {return {'_id':make, 'count':count}})}",
          "args": ["$Vehicles"],
          "lang": "js"
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "unfiltered_makes": 1,
      "makes": {
        "$filter": {
          "input": "$unfiltered_makes",
          "as": "make",
          "cond": {
            "$eq": [
              "$$make._id",
              // your search "Manufacturer" goes here
              "FORD"
            ]
          }
        }
      }
    }
  }
])

Try it on mongoplayground.net.

Mongodb aggregation to pass both a matched array and an unmatched array

2 Answers