Find first element in nested array that match conditions

Question

I have following documents:

doc1: { 
  'array': [
    {'field': 'ABC', 'enabled': 'false'},
    {'field': 'BCD', 'enabled': 'true'},
    {'field': 'DEF', 'enabled': 'false'},
    {'field': 'XYZ', 'enabled': 'true'},
  ]
}


doc2: { 
  'array': [
    {'field': 'ABC', 'enabled': 'true'},
    {'field': 'BCD', 'enabled': 'true'},
    {'field': 'DEF', 'enabled': 'false'},
    {'field': 'XYZ', 'enabled': 'true'},
  ]
}

I'm performing search by particular field. I want to get all documents that have this field enabled, and there are no other fields enabled before this (in the list).

For ex.:

search for field: BCD, enabled: true - should return only first document (because in second document ABC is enabled too).

search for XYZ, enabled: true - should not return any documents at all, because there are other fields enabled on top of this list.

I was trying to do smth with $elemMatch, but I don't know if it's possible to apply filter on top of element that elemMatch has matched.

Any suggestions?

Neil Lunn · Accepted Answer · 2017-08-17 14:19:54Z

Best probably to search using $where in addition to the normal query, and still keep things on the server:

db.getCollection('collection').find({
  "array": {
    "$elemMatch": { "field": "BCD", "enabled": "true" },
  },
  "$where": function() {
    return this.array.map((e,i) => Object.assign(e,{ i }))
      .filter( e => e.field === "BCD" && e.enabled === "true" )
      .map( e => e.i )[0] <=
    this.array.map(e => e.enabled).indexOf("true")
  }  
})

And if you have MongoDB 3.4 with support for $indexOfArray and $range, then it may look longer but it actually most efficient with $redact:

db.getCollection('collection').aggregate([
  { "$match": {
    "array": {
      "$elemMatch": { "field": "BCD", "enabled": "true" },
    }
  }},
  { "$redact": {
    "$cond": {  
      "if": {
        "$lte": [
          { "$arrayElemAt": [
            { "$map": {
              "input": {
                "$filter": {
                  "input": {
                    "$map": {
                      "input": {
                        "$zip": {
                          "inputs": [
                            "$array",
                            { "$range": [0, { "$size": "$array" }] }
                          ]
                        }    
                      },
                      "as": "a",
                      "in": {
                        "field": { "$arrayElemAt": [ "$$a.field", 0 ] },
                        "enabled": { "$arrayElemAt": [ "$$a.enabled", 0 ] },
                        "index": { "$arrayElemAt": [ "$$a", 1 ] }    
                      }
                    }
                  },
                  "as": "a",
                  "cond": {
                    "$and": [
                      { "$eq": [ "$$a.field", "BCD" ] },
                      { "$eq": [ "$$a.enabled", "true" ] }
                    ]
                  }    
                }
              },
              "as": "a",
              "in": "$$a.index"  
            }},
            0
          ]},
          { "$indexOfArray": [ "$array.enabled", "true" ] } 
        ] 
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

So there really is no actual query operation that enforces that, but both of these cases keep the selection "on the server" as opposed to sending data over the wire to the client and then filtering.

Because if you do that it kind of negates the purpose of using a database in the first place. So you really want this to happen on the server.

Jakub Kutrzeba · Accepted Answer · 2017-08-17 14:12:59Z

1

Why not search by enabled field and then check if field is appropriate or not?

db.collection("col").findOne({
    "array.enabled": true
}, {
    array: {
        $elemMatch {
            enabled: true
        }
    }
})
.then(function(docs){
    docs.forEach(function(doc){
        if(doc.array[0].field == "ABC"){
            // Ok, we get it
        }
    })
})

Second argument to find is projection, so application doesn't download complete array from document, but only first matched element of it.

edited Aug 17, 2017 at 14:12

answered Aug 17, 2017 at 13:59

Jakub Kutrzeba

1,0039 silver badges13 bronze badges

Comments

vvg · Accepted Answer · 2017-08-17 22:45:59Z

0

There is $where keyword that allow to generate complex conditions for similar purpose.

find($where: 'this.array.filter(function(e){return e.enabled=="true"})[0].field=="ABC"')

As this does not use any indexes, I'll add more conditions to benefit from optimisation.

edited Aug 17, 2017 at 22:45

answered Aug 17, 2017 at 14:05

vvg

6,40522 silver badges36 bronze badges

Collectives™ on Stack Overflow

Find first element in nested array that match conditions

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related