MongoDB condition for all fields of array

Question

Is it possible in mongodb do a query for find documents where the field is an array and all its elements satisfy a condition?

For example:

[
  {
    "_id": ObjectId("53d63d0f5c7ff000227cd372"),
    "works": [
      {
        "code": "A001",
        "items": [
          {
            "_id": "534664b081362062015d1b77",
            "qty": 6
          },
          {
            "_id": "534ba71f394835a7e51dd938",
            "qty": 5
          }
        ],
        "name": "Cambiar bombilla",
        "price": 100,
        "Date": "2014-07-30T09:43:17.593Z",
        "TechnicianId": "538efd918163b19307c59e8e",
        "percent": 2,
        "_id": ObjectId("53d63d0f5c7ff002207cd372")
      },
      {
        "code": "A001",
        "name": "Cambiar bombilla",
        "price": 100,
        "type": "Bombillas",
        "TechnicianId": "538efd918163b19307c59e8e",
        "date": "2014-07-31T13:36:34.019Z",
        "orderId": "53d63d0f5c7ff000007cd372",
        "_id": ObjectId("53da466568c26f8a72b50fcb"),
        "percent": 66
      }
    ]
  },
  {
    "_id": ObjectId("53d63d0f5c7ff000007cd372"),
    "works": [
      {
        "code": "A001",
        "items": [
          {
            "_id": "534664b081362062015d1b77",
            "qty": 6
          },
          {
            "_id": "534ba71f394835a7e51dd938",
            "qty": 5
          }
        ],
        "name": "Cambiar bombilla",
        "price": 100,
        "Date": "2014-07-30T09:43:17.593Z",
        "TechnicianId": "538efd918163b19307c59e8e",
        "percent": 2,
        "_id": ObjectId("53d63d0f5c7ff002207cd372")
      },
      {
        "code": "A001",
        "name": "Cambiar bombilla",
        "price": 100,
        "type": "Bombillas",
        "TechnicianId": "538efd918163b19307c59e8e",
        "date": "2014-07-31T13:36:34.019Z",
        "orderId": "53d63d0f5c7ff000007cd372",
        "_id": ObjectId("53da466568c26f8a72b50fcb"),

      }
    ]
  }
]

I need to find the documents like first document because in works field his subdocuments have percent, but in the second document, works array at second position doesnt have percent.

So what are the query conditions? Even if it doesn't work, so we can at least see what you want. — Neil Lunn
– Neil Lunn, Commented Aug 11, 2014 at 10:05

Neil Lunn · Accepted Answer · 2014-08-18 08:22:36Z

It's not easy to test for the existence of a field in every element of an array in a simple way. Values can be tested for buy keys take a bit more work.

The approach is done with the aggregation framework in order to process conditions for the array elements and then match the result.

Firstly with MongoDB 2.6 and greater you get some helpers:

db.collection.aggregate([
    // Filter out to match only possible documents
    { "$match": {
        "works.percent": { "$exists": true }
    }},

    // Find matching through projection
    { "$project": {
        "works": 1,
        "matched": {
            "$allElementsTrue": {
                "$map": {
                    "input": "$works",
                    "as": "el",
                    "in": { "$ifNull": [ "$$el.percent", false ] }
                }
            }
        }
    }},

    // Filter to return only the true matches
    { "$match": { "matched": true } }

])

Or a bit longer and possibly slower with $unwind in prior versions:

db.collection.aggregate([
    // Filter out to match only possible documents
    { "$match": {
        "works.percent": { "$exists": true }
    }},

    // Unwind the array
    { "$unwind": "$works" },

    // Find matching by conditionally evaluating and grouping
    { "$group": {
        "_id": "$_id",
        "works": { "$push": "$works" },
        "matched": { 
            "$min": {
                "$cond": [
                    { "$ifNull": [ "$works.percent", false ] },
                    true,
                    false
                ]
            }
        }
    }}

    // Filter to return only the true matches
    { "$match": { "matched": true } }

])

In either case, it is the $ifNull operator that works as the equivalent of the $exists operator in a aggregation operator sense. Where the "query" form for $exists tests whether a field is present, $ifNull evaluates that where the field is present then the value is returned, otherwise the alternate argument is returned instead.

But the arrays basically need to be processed to see if all elements have the required field present as there is no standard query equivalent for this.

Collectives™ on Stack Overflow

MongoDB condition for all fields of array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related