How to match additional field values with $all?

[Manjunath_k_s]

Hi ,

Here is my data model

[
  {
    "_id": 1,
    "racks": [
      {
        "rackId": 1,
        "available": true
      },
      {
        "rackId": 2,
        "available": true
      },
      {
        "rackId": 3,
        "available": true
      },
      {
        "rackId": 4,
        "available": false
      },
      {
        "rackId": 5,
        "available": false
      },
      {
        "rackId": 6,
        "available": false
      },
      
    ]
  },
  {
    "_id": 2,
    "racks": [
      {
        "rackId": 1,
        "available": true
      },
      {
        "rackId": 2,
        "available": true
      },
      {
        "rackId": 3,
        "available": true
      }
      
    ]
  },
  {
    "_id": 3,
    "racks": [
      {
        "rackId": 1,
        "available": true
      },
      {
        "rackId": 2,
        "available": true
      },
      {
        "rackId": 3,
        "available": true
      }
      
    ]
  }
]

I want to find if a document matches the conditions provided.
a. Accept a list. Where list of “rackId” are given
b. For each of rackId mentioned in list, its “available” value should be considered. In this case it should be true.

Note i have to use $all as i might have many rackId’s to query so that i do not end up writing as many rack objects in the query.
Example if i need to match 1 to 6 rackId with “available” true , i can pass [1, 2, 3, 4, 5, 6] using $all.

My query so far. Which provides no documents found even though document “_id” 1 has rackId 1, rackId 2 and racked 3 with available field as “true”. Could you please help me in getting correct query considering $all operation ?
a. Mongo query
b. Corresponding java spring query equivalent ?

db.collection.find({
  "_id": 1,
  "racks": {
    "$elemMatch": {
      "rackId": {
        $all: [
          1,
          2,
          3
        ]
      },
      "available": true
    }
  }
})

I did researched similar topic on this thread How to match $all and equal to conditions in $elemMatch operator? but not satisfied with the approach as it is not using $all

[slava]

Hello, @Manjunath_k_s and welcome to the commjunity!

What you’re trying to achieve is not possible with a simple find operation. Instead, consider using aggregation pipeline.

Let me show you how it can be done by an example.

First, we will create a simplified dataset:

db.rackSets.insertMany([
  {
    _id: 'A',
    racks: [
      {
        rackId: 1,
        available: true
      },
      {
        rackId: 2,
        available: true
      },
    ]
  },
  {
    _id: 'B',
    racks: [
      {
        rackId: 1,
        available: true
      },
      {
        rackId: 2,
        available: true
      },
      {
        rackId: 3,
        available: true
      }

    ]
  },
  {
    _id: 'C',
    racks: [
      {
        rackId: 1,
        available: true
      },
      {
        rackId: 2,
        available: false
      },
      {
        rackId: 3,
        available: false
      },
    ]
  },

  {
    _id: 'D',
    racks: [
      {
        rackId: 1,
        available: false
      },
      {
        rackId: 2,
        available: true
      },
      {
        rackId: 3,
        available: true
      },
      {
        rackId: 4,
        available: true
      }
    ]
  },
  {
    _id: 'E',
    racks: [
      {
        rackId: 1,
        available: true
      },
      {
        rackId: 2,
        available: true
      },
      {
        rackId: 3,
        available: true
      },
      {
        rackId: 4,
        available: false
      }
    ]
  }
]);

Now, let’s say, that we want to get all the documents, that meet the following conditions:

1. Document must have in its racks array and object with rackId property equals to 1, 2 or 3 (for example).
2. If the first criteria is met, then each matched object in racks array must have property available equals to true.

To get the required result, we would use the following aggregation pipeline code:

db.rackSets.aggregate([
  {
    // first, we filter out the documents, that do not contain required rackIds,
    // so the machine's memory is not wasted processing them in the next stages
    $match: {
      'racks.rackId': {
        // this is first condition - we need to mention required rack ids
        $all: [1,2,3] 
      }
    },
  },
  {
    $addFields: {
      // next, we make sure that document with required rackId
      // also has property 'available' with boolean value 'true'.
      // nMatches - is a counter for how many objects 
      // in the 'racks' array do meet our second condition 
      nMatches: {
        $reduce: {
          input: '$racks',
          initialValue: 0,
          in: { 
            $cond: {
              if: {
                $and: [
                  { 
                    // do not forget to mention required rack ids here, in this condition 
                    $in: [ '$$this.rackId',  [1,2,3]],
                  },
                  {
                    $eq: ['$$this.available', true]
                  }
                ]
              },
              then: {
                $add: ['$$value', 1]
              },
              else: {
                $add: ['$$value', 0]
              }
            }
          }
        }
      }
    }
  },
  {
    // in this stage we filter out documents, that contain our selected racks, 
    // butwith 'available' field set to false
    $match: {
      nMatches: {
        // this is the sum of the rack ids we are looking for 
        // [1,2,3] = 3 rack ids
        $eq: 3 
      }
    }
  },
  // remove temporary field
  {
    $unset: ['nMatches']
  }
]);

The output result:

[
  {
    _id: 'B',
    racks: [
      { rackId: 1, available: true },
      { rackId: 2, available: true },
      { rackId: 3, available: true }
    ]
  },
  {
    _id: 'E',
    racks: [
      { rackId: 1, available: true },
      { rackId: 2, available: true },
      { rackId: 3, available: true },
      { rackId: 4, available: false }
    ]
  }
]

Notice, that document E also returned. It contains the rack objects with all the required ids (1,2,3) and available property set to true. But also It contains rack with rackId equals to 4. In case we want only the documents, that do not contain rack, that we are not seeking for - we can achieve it, just by modifying the end of the pipeline so it would look like this:

db.rackSets.aggregate([
  {
    $match: { /* unchanged */ },
  {
    $addFields: { /* unchanged */ }
  },
  {
    $addFields: {
      totalRacks: {
        $size: '$racks'
      }
    }
  },
  {
    $match: {
      nMatches: {
        $eq: 3
      },
      totalRacks: {
        $eq: 3
      }
    }
  },
  {
    $unset: ['nMatches', 'totalRacks']
  }
]);

The modification above will remove document E from the results.

[Manjunath_k_s]

Hi Slava,

Thank you. I appreciate your quick and idea to tackle the ask. I have some follow up questions.

1. I am interested in only one document (i,e A or B or C that is known along with rackId’'s [1, 2, 3 etc]). Can it be optimized ?
2. Looks to me the above process to get the final result is lengthy, involves compuation power and lot of code involved for each such request.
3. I am looking to do this job with atomicity and concurrent users. I was anticipating a find similar query and use findAndModify(query, update, class). As mentioned by you seems such query is not possible.
4. Do you still believe the above aggregation approach can fit to my use case ? Or should i look in to transactions concept ? or any other suggestion ?

Thanks a ton in advance.

[slava]

Agree, the aggregation turned out to be a bit long, but it covers an edge case

If you need to retrieve the document, that contain only 1 document with the rack ids you require and nothing more, then you can use simpler and more efficient solution

db.rackSets.find({
  'racks.rackId': {
    $all: [1,2,3] 
  },
  'racks.available': {
    $ne: false
  }
});

However, this solution does not cover the case, where you have two documents with exact same rack ids list. Like these ones:

[
  {
    _id: 'B1',
    racks: [
      {
        rackId: 1,
        available: true
      },
      {
        rackId: 2,
        available: true
      },
      {
        rackId: 3,
        available: true
      }
    ]
  },
  {
    _id: 'B2',
    racks: [
      {
        rackId: 1,
        available: true
      },
      {
        rackId: 2,
        available: true
      },
      {
        rackId: 3,
        available: true
      }
    ]
  },
]

If it is possible to have two documents with the same rack ids list in your system, you need to think about a logic on how to pick one singe document form the result .

Regarding the atomicity. Any update on 1 single document in MongoDB is atomic. That means, if you need to update one single document at a time, you don’t need to worry about atomicity. If you need to update multiple documents at once - use transactions. If you have to update tons of documents at once - consider making some modifications to your data model.

[Manjunath_k_s]

Great this the kind of query i am looking for. Appreciate your suggestion. Your query is very close but not working in below cases.

Does this consider matching the “available” field to all rackId’ s in given array (sort of AND condition between rackId and available field for a list of rackIds) ?

Looks like not !

If you take a look at below use case, input rackId list is [1, 2] for which available field is true. I know i have rack object rackId 3 which is set available false intentionally in the input. However the result is empty !
Is this expected behavior ?

db.rackSets.find({
  'racks.rackId': {
    $all: [1,2,3] 
  },
  'racks.available': {
    $ne: false
  }
});

[slava]

Try this one:

db.rackSets.find({
  racks: {
    $all: [
      { rackId: 1, available: true },
      { rackId: 2, available: true }
    ]
  },
});

Keep in mind, that this solution will work only if you provide the full list fields if the rack object you require. That means, if your real model has more properties in your rack objects, like this, for example:

[
  {
    _id: 'A',
    racks: [
      {
        rackId: 1,
        label: 'green',
        available: true
      },
      {
        rackId: 2,
        label: 'red',
        available: true
      },
    ]
  }
]

Then you will also have to mention that label field with its exact value in your query:

db.rackSets.find({
  racks: {
    $all: [
      { rackId: 1, label: 'green', available: true },
      { rackId: 2, label: 'red', available: true }
    ]
  },
});

So, if your model indeed has more fields or you’re planning to add them later - better to use that aggregation pipeline I showed you in the very first message .