How to compare attributes of different objects in mongodb object array

Question

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)

Would you like to only include documents for comparison when both fields are present ? — s7vr
– s7vr, Commented Feb 4, 2019 at 14:41
That will do. But if there is a way to include documents without those fields using some default value. I would like to know that too. — Omi A
– Omi A, Commented Feb 4, 2019 at 14:52

s7vr · Accepted Answer · 2019-02-04 14:46:50Z

2

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"]}}
}})

edited Feb 4, 2019 at 14:46

answered Feb 4, 2019 at 13:46

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DaveStSomeWhere Over a year ago

Aren't all the field entries at array index 0?

s7vr Over a year ago

@DaveStSomeWhere Not sure I follow. The field entries are at different position in different documents. So $indexofarray to search the index of field entry by field id followed by $arrayElemAt to retrieve the field entry at that index.

DaveStSomeWhere Over a year ago

Ok - was testing and this didn't work until your most recent update, glad it works now - so ignore my confusion.

Collectives™ on Stack Overflow

How to compare attributes of different objects in mongodb object array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related