Aggregation Match Array Element within another Array

Question

I'm trying to return the field of an array based on the values in this one but using a 2nd $eq in a field present in an array (array of an array specifically) gives me systematically an empty result

Here's the structure part of my collection :

    ...
    _id: ObjectId("S"),
  array: [
     {
        X: "A",
        Y: NumberInt(0),
        Z: [NumberInt(5),NumberInt(6)]
     },
     {
        X: "B",
        Y: NumberInt(1),
        Z: [NumberInt(5),NumberInt(8)]
     },
     {
        X: "C",
        Y: NumberInt(0)
     }
  ],
    ...

and part of my code :

db.collection.aggregate(
   [
      { $match: { _id: ObjectId("56a108914b179d6efafca6f3") } },
      { $project: { _id: 1, list: { $setDifference: [ { $map: { input: "$array", as: "el", in: { $cond: [ { $and: [ { $eq: [ "$$el.Y", 0 ] }, { $eq: [ "$$el.Z", 5 ] } ] }, "$$el.X", false ] } } }, [false] ] } } } 
   ]
);

but the 2nd $eq argument dont work...when i delete it i have good result (without $$el.Z equality).

I want return :

{ "_id" : ObjectId("S"), "list" : [ "A" ] }

Thank you in advance,

Best regards.

Answer 1

The $eq test as an aggregation conditional with an array works a bit differently than it does in a $match or regular query. The main differnce being that this will not test each element of the array, but rather the whole "array" instead.

So you basically need a logical return for the array "elements" if they match the condition. This could either be another $map operation to trandform and then test with $anyElementTrue. Or use $setIsSubset:

db.collection.aggregate(
   [
      { "$match": { "_id": ObjectId("56a108914b179d6efafca6f3") } },
      { "$project":{ 
          "list": { 
              "$setDifference": [ 
                  { "$map": { 
                      "input": "$array", 
                      "as": "el",
                      "in": { 
                          "$cond": [ 
                              { "$and": [ 
                                  { "$eq": [ "$$el.Y", 0 ] }, 
                                  { "$setIsSubSet": [ [5], "$$el.Z" ] }
                              ] }, 
                              "$$el.X",
                              false
                          ]
                      }
                  }},
                  [false]
              ]
          }
      }} 
   ]
);

And of course if you are using MongoDB 3.2 or later, then the syntax here becomes a little simpler with $filter in order to reduce down the array:

db.collection.aggregate(
   [
      { "$match": { "_id": ObjectId("56a108914b179d6efafca6f3") } },
      { "$project":{ 
          "list": { 
              "$map": {
                  "input": { "$filter": { 
                      "input": "$array", 
                      "as": "el",
                      "cond": { 
                         "$and": [ 
                             { "$eq": [ "$$el.Y", 0 ] }, 
                             { "$setIsSubSet": [ [5], "$$el.Z" ] }
                         ]
                      }
                  }},
                  "as": "el",
                  "in": "$$el.X"
              }}
          }
      }} 
   ]
);

But the basic case is that this logical test needs to apply to an "array" rather than the query form which will look at each member of the array. So the correct operations to inspect must be used.

Of course, array aggregation operations are also not as forgiving as basic query operators to match elements. So if the array is not even present, then you need to substitute that element within the statement for something that is valid. The $ifNull operator is of use here:

db.collection.aggregate(
   [
      { "$match": { "_id": ObjectId("56a108914b179d6efafca6f3") } },
      { "$project":{ 
          "list": { 
              "$map": {
                  "input": { "$filter": { 
                      "input": "$array", 
                      "as": "el",
                      "cond": { 
                         "$and": [ 
                             { "$eq": [ "$$el.Y", 0 ] }, 
                             { "$setIsSubSet": [ [5], { "$ifNull": [ "$$el.Z", [] ] } ] }
                         ]
                      }
                  }},
                  "as": "el",
                  "in": "$$el.X"
              }}
          }
      }} 
   ]
);

And again in MongoDB 3.2 there is also $isArray, but it is a bit lengthy in syntax for this purpose as it would be used in conjuction with $cond. Still there are valid reasons for this, as shown later.

But really, for your goals here it makes sense to also test that the conditions basically exist somewhere in the array element before even applying the array manipulation and filtering. So make it part of the $match condition instead:

db.collection.aggregate(
   [
      { "$match": { 
          "_id": ObjectId("56a108914b179d6efafca6f3"),
          "array": { "$elemMatch": { "Y": 0, "Z": 5 } }
      }},
      { "$project":{ 
          "list": { 
              "$map": {
                  "input": { "$filter": { 
                      "input": "$array", 
                      "as": "el",
                      "cond": { 
                         "$and": [ 
                             { "$eq": [ "$$el.Y", 0 ] }, 
                             { "$setIsSubSet": [ 
                                 [5], 
                                { "$cond": [ 
                                    { "$isArray": "$$el.Z" }.
                                    "$$el.Z",
                                    ["$$el.Z"]
                                ]}
                             ]}
                         ]
                      }
                  }},
                  "as": "el",
                  "in": "$$el.X"
              }}
          }
      }} 
   ]
);

With again the paranoind check that $$el.Z actually is an array or replacing it in evaluation with something that is actually an array in order to process with an operator that expects an array.

The $elemMatch in the query component at least only selects documents that have the same conditions you want to apply in the filter, even if that check does not ensure by itself that "Z" is actually an array itself.