CODEWITHSUNDEEP
mongodbmongodb-query
Prithviraj Mitra
Prithviraj Mitra

Reputation: 11812

Comparing two fields in MongoDb

I am new in MongoDB and trying to compare 2 fields in a collection in MongoDB.

The fields are attributes(object) and collections(array)

{ 
    "_id" : ObjectId("55ae1e47309ffcbd1b67bd6c"), 
    "collections" : [
        {
            "collection_code" : "9TEST", 
            "_id" : ObjectId("5c76eee4bb4afe3a3603eeb3")
        }
    ], 
   "attributes" : {
        "collection_code" : "9TEST"
    }
}

My query is

db.companies.find({ $where : "this.attributes.collection_code == this.collections[collection_code]" })

Getting below error 

{
    "message" : "TypeError: Cannot read property 'collection_code' of undefined" +
              "at _funcs1 (_funcs1:1:45) near 'on_code == this.collections[collection_co' ",
    "$err" : "TypeError: Cannot read property 'collection_code' of undefined" +
              "at _funcs1 (_funcs1:1:45) near 'on_code == this.collections[collection_co' ",
    "code" : 16722,
    "name" : "MongoError"
}

Any help is highly appreciated. Thanks in advance.

Upvotes: 1

Views: 464

Answers (1)

mickl
mickl

Reputation: 49945

You need an $expr to compare two fields in MongoDB. Since you have an array (collection) and you want to check if there's any element where fields match you can use $map with $anyElementTrue operators like below:

db.col.find({
    $expr: {
        $anyElementTrue: {
            $map: {
                input: "$collections",
                in: {
                    $eq: [ "$$this.collection_code", "$attributes.collection_code" ]
                }
            }
        }
    }
})

There is also more succinct way by using $in operator

db.col.find({
    $expr: {
        $in: [ "$attributes.collection_code", "$collections.collection_code" ]
    }
})

it works because collections.collection_code represents an array of strings in this case.

EDIT: As a workaround for 3.0 version you can use $anyElementTrue with $redact:

db.col.aggregate([
    { $redact: {
        $cond: {
        if: {$anyElementTrue: { $map: { input: "$collections", as: "c", in: { $eq: [ "$$c.collection_code", "$attributes.collection_code" ]  }  }}},
        then: "$$KEEP",
        else: "$$PRUNE"
        }
    }
    }
])

Upvotes: 1

Related Questions