CODEWITHSUNDEEP
mongodbmongodb-query
Pacman
Pacman

Reputation: 2245

$elemMatch against two Array elements if one fails

A bit odd but this is what I am looking for. I have an array as follow: Document 1:

Items: [
{
"ZipCode": "11111",
"ZipCode4" "1234"
}

Document 2:

Items: [
{
"ZipCode": "11111",
"ZipCode4" "0000"
}

I would like to use a single query, and send a filter on ZipCode = 1111 && ZipCode4 = 4321, if this fails, the query should look for ZipCode = 1111 && ZipCode4: 0000

Is there a way to do this in a single query ? or do I need to make 2 calls to my database ?

Upvotes: 1

Views: 120

Answers (1)

Bertrand Martel
Bertrand Martel

Reputation: 45352

For matching both data set (11111/4321) and (11111/0000), you can use $or and $and with $elemMatch like the following :

db.test.find({
    $or: [{
        $and: [{
            "Items": {
                $elemMatch: { "ZipCode": "11111" }
            }
        }, {
            "Items": {
                $elemMatch: { "ZipCode4": "4321" }
            }
        }]
    }, {
        $and: [{
            "Items": {
                $elemMatch: { "ZipCode": "11111" }
            }
        }, {
            "Items": {
                $elemMatch: { "ZipCode4": "0000" }
            }
        }]
    }]
})

As you want conditional staging, this is not possible but we can get closer to it like this : 

db.test.aggregate([{
    $match: {
        $or: [{
            $and: [{ "Items.ZipCode": "11111" }, { "Items.ZipCode4": "4321" }]
        }, {
            $and: [{ "Items.ZipCode": "11111" }, { "Items.ZipCode4": "0000" }]
        }]
    }
}, {
    $project: {
        Items: 1,
        match: {
            "$map": {
                "input": "$Items",
                "as": "val",
                "in": {
                    "$cond": [
                        { $and: [{ "$eq": ["$$val.ZipCode", "11111"] }, { "$eq": ["$$val.ZipCode4", "4321"] }] },
                        true,
                        false
                    ]
                }
            }
        }
    }
}, {
    $unwind: "$match"
}, {
    $group: {
        _id: "$match",
        data: {
            $push: {
                _id: "$_id",
                Items: "$Items"
            }
        }
    }
}])
  • The first $match is for selecting only the items we need
  • The $project will build a new field that check if this items is from the 1st set of data (11111/4321) or the 2nd set of data (11111/0000).
  • The $unwind is used to remove the array generated by $map.
  • The $group group by set of data

So in the end you will have an output like the following :

{ "_id" : true, "data" : [ { "_id" : ObjectId("58af69ac594b51730a394972"), "Items" : [ { "ZipCode" : "11111", "ZipCode4" : "4321" } ] }, { "_id" : ObjectId("58af69ac594b51730a394974"), "Items" : [ { "ZipCode" : "11111", "ZipCode4" : "4321" } ] } ] }
{ "_id" : false, "data" : [ { "_id" : ObjectId("58af69ac594b51730a394971"), "Items" : [ { "ZipCode" : "11111", "ZipCode4" : "0000" } ] } ] }

Your application logic can check if there is _id:true in this output array, just take the corresponding data field for _id:true. If there is _id:false in this object take the corresponding data field for _id:false.

In the last $group, you can also use $addToSet to builds 2 field data1 & data2 for both type of data set but this will be painful to use as it will add null object to the array for each one of the opposite type :

"$addToSet": {
    "$cond": [
        { "$eq": ["$_id", true] },
        "$data",
        null
    ]
}

Here is a gist

Upvotes: 2

Related Questions