CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-queryaggregation-framework
dlsrb6342
dlsrb6342

Reputation: 368

How to include empty array when filtering an array

Here is my item model.

const itemSchema = new Schema({
  name: String,
  category: String,
  occupied: [Number],
  active: { type: Boolean, default: true },
});

I want to filter 'occupied' array. So I use aggregate and unwind 'occupied' field.

So I apply match query. And group by _id. But if filtered 'occupied' array is empty, the item disappear.

Here is my code

Item.aggregate([
  { $match: {
    active: true
  }},
  { $unwind:
    "$occupied",
  },
  { $match: { $and: [
    { occupied: { $gte: 100 }},
    { occupied: { $lt: 200 }}
  ]}},
  { $group : {
    _id: "$_id",
    name: { $first: "$name"},
    category: { $first: "$category"},
    occupied: { $addToSet : "$occupied" }
  }}
], (err, items) => {
  if (err) throw err;
  return res.json({ data: items });
});

Here is example data set

{ 
    "_id" : ObjectId("59c1bced987fa30b7421a3eb"),
    "name" : "printer1",
    "category" : "printer",
    "occupied" : [ 95, 100, 145, 200 ],
    "active" : true
},
{
  "_id" : ObjectId("59c2dbed992fb91b7421b1ad"),
   "name" : "printer2",
   "category" : "printer",
   "occupied" : [ ],
   "active" : true
}

The result above query

[
  { 
    "_id" : ObjectId("59c1bced987fa30b7421a3eb"),
    "name" : "printer1",
    "category" : "printer",
    "occupied" : [ 100, 145 ],
    "active" : true
  }
]

and the result I want

[
  { 
    "_id" : ObjectId("59c1bced987fa30b7421a3eb"),
    "name" : "printer1",
    "category" : "printer",
    "occupied" : [ 100, 145 ],
    "active" : true
  },
  { 
    "_id" : ObjectId("59c2dbed992fb91b7421b1ad"),
    "name" : "printer2",
    "category" : "printer",
    "occupied" : [ ],
    "active" : true
  }
]

how could I do this??

Thanks in advance.

Upvotes: 2

Views: 186

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151132

In the simplest form, you keep it simply by not using $unwind in the first place. Your conditions applied imply that you are looking for the "unique set" of matches to specific values.

For this you instead use $filter, and a "set operator" like $setUnion to reduce the input values to a "set" in the first place:

Item.aggregate([
  { "$match": { "active": true } },
  { "$project": {
    "name": 1,
    "category": 1,
    "occupied": { 
      "$filter": {
        "input": { "$setUnion": [ "$occupied", []] },
        "as": "o",
        "cond": {
          "$and": [
            { "$gte": ["$$o", 100 ] },
            { "$lt": ["$$o", 200] }
          ]
        }
      }
    }
  }}
], (err, items) => {
  if (err) throw err;
  return res.json({ data: items });
});

Both have been around since MongoDB v3, so it's pretty common practice to do things this way.

If for some reason you were still using MongoDB 2.6, then you could apply $map and $setDifference instead:

Item.aggregate([
  { "$match": { "active": true } },
  { "$project": {
    "name": 1,
    "category": 1,
    "occupied": { 
      "$setDifference": [
        { "$map": {
          "input": "$occupied",
          "as": "o",
          "in": {
            "$cond": {
              "if": {
                "$and": [
                  { "$gte": ["$$o", 100 ] },
                  { "$lt": ["$$o", 200] }
                ]
              },
              "then": "$$o",
              "else": false
            }
          }
        }},
        [false]
      ]
    }
  }}
], (err, items) => {
  if (err) throw err;
  return res.json({ data: items });
});

It's the same "unique set" result as pulling the array apart, filtering the items and putting it back together with $addToSet. The difference being that its far more efficient, and retains ( or produces ) an empty array without any issues.

Upvotes: 0

Related Questions