Check If field exists in an sub-document of an Array

Question

I have a schema that is similar to this.

{id: Number,
  line_items: [{ 
    id: String,
    quantity: Number,
    review_request_sent :Boolean
  }],
  total_price: String,
  name: String,
  order_number: Number
}

What I want to find is all documents that don't have the field review_request_sent set for all items in the array.

Example

{ 
  id: 1, 
  line_items: [
    {
      id: 43,
      review_request_sent: true
    }
  ]
},
{
  id: 2,
  line_items: [
    {
      id: 1,
      review_request_sent: false
    },
    {
      id: 39
    },
 ]
},
{
  id: 3,
  line_items: [
    {
     id: 23,
     review_request_sent: true
    },
    {
     id: 85,
     review_request_sent: true
    },
    {
     id: 12,
     review_request_sent: false
    }
  ]
}

I would want help with a query/find that will return only the second document because it does not have the review_request_sent field in all its items in its array.

Answer 1

You basically want $elemMatch and the $exists operator, as this will inspect each element to see if the condition "field not exists" is true for any element:

Model.find({
  "line_items": {
      "$elemMatch": { "review_request_sent": { "$exists": false } }
  }
},function(err,docs) {

});

That returns the second document only as the field is not present in one of the array sub-documents:

{
        "id" : 2,
        "line_items" : [
                {
                        "id" : 1,
                        "review_request_sent" : false
                },
                {
                        "id" : 39
                }
        ]
}

Note that this "differs" from this form:

Model.find({
  "line_items.review_request_sent": { "$exists": false } 
},function(err,docs) {

})

Where that is asking does "all" of the array elements not contain this field, which is not true when a document has at least one element that has the field present. So the $eleMatch makes the condition be tested against "each" array element, and thus you get the correct response.

---

If you wanted to update this data so that any array element found that did not contain this field was to then receive that field with a value of false ( presumably ), then you could even write a statement like this:

    Model.aggregate(
      [
        { "$match": { 
          "line_items": {
            "$elemMatch": { "review_request_sent": { "$exists": false } }
          } 
        }},
        { "$project": {
          "line_items": {
            "$setDifference": [
              {"$map": {
                "input": "$line_items",
                "as": "item",
                "in": {
                  "$cond": [
                    { "$eq": [ 
                      { "$ifNull": [ "$$item.review_request_sent", null ] },
                      null
                    ]},
                    "$$item.id",
                    false
                  ]
                }
              }},
              [false]
            ]
          }
        }}
    ],
    function(err,docs) {
      if (err) throw err;
      async.each(
        docs,
        function(doc,callback) {
          async.each(
            doc.line_items,
            function(item,callback) {
              Model.update(
                { "_id": doc._id, "line_items.id": item },
                { "$set": { "line_items.$.review_request_sent": false } },
                callback
              );
            },
            callback
          );
        },
        function(err) {
          if (err) throw err;
          // done
        }
      );
    }
  );

Where the .aggregate() result not only matches the documents, but filters out content from the array where the field was not present, so as to just return the "id" of that particular sub-document.

Then the looped .update() statements match each found array element in each document and adds the missing field with a value at the matched position.

In that way you will have the field present in all sub-documents of every document where it was missing before.

If you want to do such a thing, then it would also be wise to change your schema to make sure the field is always there as well:

{id: Number,
  line_items: [{ 
    id: String,
    quantity: Number,
    review_request_sent: { type: Boolean, default: false }
  }],
  total_price: String,
  name: String,
  order_number: Number
}

So the next time you add new items to the array in your code the element will always exist with it's default value if not otherwise explicitly set. And it is probably a goood idea to do so, as well as setting required on other fields you always want, such as "id".

Answer 2

You can find this another way without $map and $unwind used $redact like this :

db.collectionName.aggregate({
  "$match": {
    "line_items.review_request_sent": {
      "$exists": true
    }
  }
}, {
  "$redact": {
    "$cond": {
      "if": {
        "$eq": [{
          "$ifNull": ["$review_request_sent", "null"]
        }, "null"]
      },
      "then": "$$DESCEND",
      "else": "$$PRUNE"
    }
  }
}, {
  "$match": {
    "line_items": {
      "$size": 1
    }
  }
}).pretty()

In above query first match check whether review_request_sent is presents or not in redact $ifNull use to check whether review_request_sent presents or not if not then it replaced to "null" and $eq to check "null" this will return documents like

{ "_id" : ObjectId("55e6ca322c33bb07ff2c163e"), "id" : 1, "line_items" : [ ] }
{ "_id" : ObjectId("55e6ca322c33bb07ff2c163f"), "id" : 2, "line_items" : [ { "id" : 39 } ] }
{ "_id" : ObjectId("55e6ca322c33bb07ff2c1640"), "id" : 3, "line_items" : [ ] }

and after that last match check only those documents whose line_items array contains value i.e $size to 1

Answer 3

If you just want to get the whole document then as mentioned by @Blakes Seven. You can query using $elemMatch.
But if you want to filter array element and get only those array elements which do not have a certain field then you can use $filter along with $type in aggregation pipeline.

[
    { $match: { "line_items": { "$elemMatch": { "review_request_sent": { "$exists": false } } } } },
    { $project: {
        "line_items": { "$filter": { 
            "input": "$line_items", 
            "as": "item", 
            "cond": { "$eq": [ { "$type": "$$item.review_request_sent" }, "missing" ] }
        } }
    } }
]

Note: $type returns "missing" when used with undefined fields.