How do I exclude a sepcific object from an array in mongodb?

Question

I have a collection with the following structure:

{
        "_id" : ObjectId("59ef54445134d7d70e1cf531"),
        "CustomerId" : "Gym_2",
        "History" : [
                {
                        "Created_At" : ISODate("2017-10-24T14:54:59Z"),
                        "Unit" : 600,
                        "ReferenceCode" : "1cd15b4d-bc42-4a51-a8b3-307db6dc3dee",

                },
                {
                        "Created_At" : ISODate("2017-10-28T00:22:19Z"),
                        "Sent" : true
                },
                {
                        "Created_At" : ISODate("2017-10-29T10:22:23Z"),
                        "Unit" : 600,
                        "ReferenceCode" : "998e7fce-8a1c-4f7c-b48c-c02cb5c5ad5c",
                }
        ]
}
{
        "_id" : ObjectId("59ef54465134d7d70e1cf534"),
        "CustomerId" : "Gym_1",
        "History" : [
                {
                        "Created_At" : ISODate("2017-10-24T14:55:02Z"),
                        "Unit" : 600,
                        "ReferenceCode" : "d19ebeec-bd81-4a0a-aed5-006f746b50ff",
                },
                {
                        "Unit" : 600,
                        "ReferenceCode" : "a991504f-be1f-4e77-b59f-fba73c59e6f1",
                        "Created_At" : ISODate("2017-10-26T13:51:14Z")
                }
        ]
}

I'm trying to build a query that returns only CustomerId along with history objects that do not have the "Sent" field set. The result should look like this:

       {
                "_id" : ObjectId("59ef54445134d7d70e1cf531"),
                "CustomerId" : "Gym_2",
                "History" : [
                        {
                                "Created_At" : ISODate("2017-10-24T14:54:59Z"),
                                "Unit" : 600,
                                "ReferenceCode" : "1cd15b4d-bc42-4a51-a8b3-307db6dc3dee",

                        },

    {
                            "Created_At" : ISODate("2017-10-29T10:22:23Z"),
                            "Unit" : 600,
                            "ReferenceCode" : "998e7fce-8a1c-4f7c-b48c-c02cb5c5ad5c",
                    }
            ]
    }
    {
            "_id" : ObjectId("59ef54465134d7d70e1cf534"),
            "CustomerId" : "Gym_1",
            "History" : [
                    {
                            "Created_At" : ISODate("2017-10-24T14:55:02Z"),
                            "Unit" : 600,
                            "ReferenceCode" : "d19ebeec-bd81-4a0a-aed5-006f746b50ff",
                    },
                    {
                            "Unit" : 600,
                            "ReferenceCode" : "a991504f-be1f-4e77-b59f-fba73c59e6f1",
                            "Created_At" : ISODate("2017-10-26T13:51:14Z")
                    }
            ]
    }

The closest I could reach is the following query:

db.Customers.aggregate([ 
  {$project:{"Sent":{$exists:false},count:{$size:"$History" }}}
  ]);  

But I get "errmsg" : "Unrecognized expression '$exists'" . How can I achieve this result?

Answer 1

There is a solution for your problem, and aggregation framework is definitely the right way to achieve what you want. To modify nested collection you have to:

1. Unwind this collection ($unwind)
2. Filter out documents where Sent exists
3. Group by common properties

4. Project your data to receive it's original form

   db.customers.aggregate([
      {$unwind: "$History"},
      {$match: {"History.Sent": {$exists: false}}},
      {$group: {"_id": { "_id": "$_id", "CustomerId": "$CustomerId" }, History: { $push: "$History"} }},
      {$project: { "_id": "$_id._id", "CustomerId": "$_id.CustomerId", History: 1}}
   ]);
   

As you can see this query is rather complicated and for larger collections you might encounter problems with performance because we're doing a lot more than simple collection filtering. So although it works I'd suggest you should consider changing your data model, for instance having each history item as a separate document like this:

{
    _id: "some_id"
    "Created_At" : ISODate("2017-10-24T14:54:59Z"),
    "CustomerId" : "Gym_2",
    "Unit" : 600,
    "Sent" : true //can be set or not
    "ReferenceCode" : "1cd15b4d-bc42-4a51-a8b3-307db6dc3dee"
}

Then your query will be just simple find with $exists.