Use multiple criteria with $elemMatch

Question

I have a Mongo document which has a list of items. Each of those items has some status associated with it.

I want to use $elemMatch to return documents who have statuses which match certain criteria.

Consider the following document. It represents an order, and each item in the order is different (they represent eyeglasses):

{
    "_id" : ObjectId("5335dcd6888a4f21dd77657c"),
    "items" : [ 
        {
            "lens_type" : "STANDARD_INDEX_LENS",
            "status" : {}
        }, 
        {
            "lens_type" : "NO_LENS",
            "status" : {
                "shipped" : true
            }
        }, 
        {
            "lens_type" : "RX_SUN_LENS",
            "status" : {
                "received_from_lab" : true,
                "sent_to_lab" : true,
                "shipped" : true
            }
        }
    ]
}

I want to find all items which are "unshipped" - that is, items.status.shipped does not exist. I want to find all items which are ready to be shipped.

However, if the item has a prescription lens - lens_type is either STANDARD_INDEX_LENS or RX_SUN_LENS - then I only consider it unshipped if it has been received from the lab. item.status.received_from_lab exists.

The above document should not be returned by my query. This is because two of the items have been shipped, and the other item is unshipped but has not yet been received_from_lab.

However, my query is indeed returning this document!

Here is my query:

{
    "$and": [
        {
            "items": {
                "$elemMatch": {
                    "lens_type": {
                       "$in": [
                            "STANDARD_INDEX_LENS",
                            "RX_SUN_LENS"
                        ]
                    },
                    "status.received_from_lab": {"$exists": true}
                }
            }
        },
        {
            "items": {
                "$elemMatch": {
                    "status.shipped": {"$exists": false}
                }
            }
        }
    ]
}

What is the logic error in my query? What construct should I be using instead? Will I need to do this kind of filtering on the client side?

Answer 1

The problem with the logic is that given the document, the type with "STANDARD_INDEX_LENS" matches the second condition, and the item matching "RX_SUN_LENS" matches the first condition. So since both are true then the document is returned.

What you did not do was state that your "prescription" lenses could have either of those conditions evaluate. So probably the easiest way to write this is as follows:

{
    "$and": [
        {
            "items": {
                "$elemMatch": {
                    "lens_type": {
                       "$in": [
                            "STANDARD_INDEX_LENS",
                            "RX_SUN_LENS"
                        ]
                    },
                    "status.received_from_lab": {"$exists": true}
                }
            }
        },
        {
            "items": {
                "$elemMatch": {
                    "lens_type": {
                       "$nin": [
                            "STANDARD_INDEX_LENS",
                            "RX_SUN_LENS"
                        ]
                    },
                    "status.shipped": {"$exists": false}
                }
            }
        }
    ]
}

So the $nin operator there makes sure that when checking for a "shipped" status that check is not performed on the "prescription" types and is only done on other items so it cannot then evaluate to true on one of these "prescriptions".

---

As I said, there is also another way to do it if you believed your logic to be more complex in this restriction. So then you would need a way to apply the conditions to all of the elements within the array. You can do that using .aggregate() :

db.order.aggregate([

    // Document level filtering still makes sense
    { "$match": {
        "$and": [
            {
                "items": {
                    "$elemMatch": {
                        "lens_type": {
                           "$in": [
                                "STANDARD_INDEX_LENS",
                                "RX_SUN_LENS"
                            ]
                        },
                        "status.received_from_lab": {"$exists": true}
                    }
                }
            },
            {
                "items": {
                    "$elemMatch": {
                        "status.shipped": {"$exists": false}
                    }
                }
            }
        ]
    }},

    // Unwind to de-normalize
    { "$unwind": "$items" },

    // Then actually "filter" each of the un-wound documents
    { "$match": {
        "$and": [
            {
                "items.lens_type": {
                    "$in": [
                        "STANDARD_INDEX_LENS",
                        "RX_SUN_LENS"
                    ]
                },
                "items.status.received_from_lab": {"$exists": true}
            },
            {
                "items.status.shipped": {"$exists": false}
            }
        ]          
    }}

    // Group back the results of any found "elements"
    { "$group": {
        "_id": "$_id",
        "items": { "$push": "$items" }
    }}

])

So what this basically does is after doing the initial $match, the documents in the array are "split" apart or generally "de-normalized" using $unwind so that they now appear as in individual document themselves.

The additional $match then makes sure that nothing that is left matches those conditions. Or you may want to alter the logic to another case. Then finally you can use $group to bring any results back into an array. But this array would only contain the documents that did not get filtered. So the form of the document would be altered.

You could "keep" the form of the original document with a technique shown here.

But it "depends" on what your needs are. It would seem that the first method should suit your needs, but at least you have a few things to try.