CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Omi A
Omi A

Reputation: 95

How to compare attributes of different objects in mongodb object array

I have mongodb document structure like below:

{
"_id" : ObjectId("5b58673721b4b95a193d4e91"),
"pageId" : "page1",
"fields" : [ 
    {
        "fieldId" : "Field1",
        "value" : "test"
    }, 
    {
        "fieldId" : "Field2",
        "value" : 6.0
    },
    {
        "fieldId" : "Field3",
        "value" : 8.0
    }
]}

}

I want to find all the documents in the collection where the value for the object having fieldId as 'Field2' is greater than value for the object having fieldId as 'Field3'.

i.e. Field2(value) > Field3(value)

The number of objects in fields array is not fixed. I can not compare by position. I have to match objects by field Id. So, in this case, it would have to be on the lines of:
Compare: object value where fieldId='Field2' with object value where fieldId='Field3'

How do I write MongoDB query for this?

I am using MongoDB version 4.0
(There might be instances where fields may not be present in documents. e.g. documents created before these fields are introduced in the design)

Upvotes: 3

Views: 1992

Answers (1)

s7vr
s7vr

Reputation: 75914

Assuming you always have two fields in document you can use below query in 3.6.

Locate the field doc using indexofarray with fieldId as search criteria followed by value comparison.

db.collectionname.find(
{"$expr":{
  "$let":{
    "vars":{
      "field2":{"$arrayElemAt":["$fields",{"$indexOfArray":["$fields.fieldId","Field2"]}]},
      "field3":{"$arrayElemAt":["$fields",{"$indexOfArray":["$fields.fieldId","Field3"]}]}
    },
    "in":{"$gt":["$$field2.value","$$field3.value"]}}
}})

Upvotes: 2

Related Questions