MongoDB select from array based on multiple conditions

Question

I have the following structure (this can't be changed, that is I have to work with):

{
        "_id" : ObjectId("abc123"),
        "notreallyusedfields" : "dontcare",
        "data" : [
                {
                        "value" : "value1",
                        "otherSomtimesInterestingFields": 1
                        "type" : ObjectId("asd123=type1"),
                },
                {
                        "value" : "value2",
                        "otherSometimesInterestingFields": 1
                        "type" : ObjectId("asd1234=type2"),
                },
                many others
        ]
}

So basically the fields for a schema are inside an array and they can be identified based on the type field inside 1 array element (1 schema field and it's value is 1 element in the array). For me this is pretty strange, but I'm new to NoSQL so this may be ok. (also for different data some fields may be missing and the element order in the data array is not guaranteed)

Maybe it's easier to understand like this: Table a: type1 column | type2 column | and so on (and these are stored in the array like the above)

My question is: how can you select multiple fields with conditions? What I mean is (in SQL): SELECT * FROM table WHERE type1=value1 AND type2=value2

I can select 1 like this: db.a.find( {"data.type":ObjectId("asd1234=type2"), "data.value":value2}).pretty()

But I don't know how could I include that type1=value1 or even more conditions. And I think this is not even good because it can match any data.value field inside the array so it doesn't mean that where the type is type2 the value has to be value2.

How would you solve this?

I was thinking of doing 1 query for 1 condition and then do another based on the result. So something like the pipeline for aggregation but as I see $match can't be used more times in an aggregation. I guess there is a way to pipeline these commands but this is pretty ugly. What am I missing? Or because of the structure of the data I have to do these strange multiple queries?

I've also looked at $filter but the condition there also applies to any element of the array. (Or I'm doing it wrong)

Thanks!

Sorry if I'm not clear enough! Please ask and I can elaborate.

(Basically what I'm trying to do based on this structure ( Retrieve only the queried element in an object array in MongoDB collection ) is this: if shape is square then filter for blue colour, if shape is round then filter for red colour === if type is type1 value has to be value1, if type is type2 value has to be value2)

score 4 · Accepted Answer · answered Nov 06 '18 at 10:44

This can be done like:

db.document.find( { $and: [ 
    { type:ObjectId('abc') }, 
    { data: { $elemMatch: { type: a, value: DBRef(...)}}},
    { data: { $elemMatch: { type: b, value: "string"}}}
] } ).pretty()

So you can add any number of "conditions" using $and so you can specify that an element has to have type a and a value b, and another element type b and value c...

If you want to project only the matching elements then use aggregate with filter:

db.document.aggregate([
{$match: { $and: [ 
    { type:ObjectId('a') }, 
    { data: { $elemMatch: { Type: ObjectId("b"), value: DBRef(...)}}},
    { data: { $elemMatch: { Type: ObjectId("c"), value: "abc"}}}
    ] }
},
{$project: {
      metadata: {
        $filter: {
            input: "$data",
            as: "data",
            cond: { $or: [
                {$eq: [ "$$data.Type", ObjectId("a") ] },
                {$eq: [ "$$data.Type", ObjectId("b") ] }]}
          }
        }
      }
}
]).pretty()

This is pretty ugly so if there is a better way please post it! Thanks!

score 1 · Answer 2 · answered Oct 29 '18 at 21:05

If you need to retrieve documents that have array elements matching multiple conditions, you have to use $elemMatch query operator.

db.collection.find({
  data: {
      $elemMatch: {
        type: "type1",
        value: "value1"
      }
    }
  })

This will output whole documents where an element matches.

To output only first matching element in array, you can combine it with $elemMatch projection operator.

db.collection.find({
  data: {
    $elemMatch: {
      type: "type1",
      value: "value1"
    }
  }
},
{
  data: {
    $elemMatch: {
      type: "type1",
      value: "value1"
    }
  }
})

Warning, don't forget to project all other fields you need outside data array.

And if you need to output all matching elements in array, then you have to use $filter in an aggregation $project stage, like this :

db.collection.aggregate([
  {
    $project: {
      data: {
        $filter: {
          input: "$data",
          as: "data",
          cond: {
            $and: [
              {
                $eq: [
                  "$$data.type",
                  "type1"
                ]
              },
              {
                $eq: [
                  "$$data.value",
                  "value1"
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Thanks for your reply! Unfortunately this doesn't solve what I need: I'd need to also add that where type is type2 then value has to be value2 (and others). But this query can't do that. So I'd need something like this: (type="type1" AND value="value1") AND (type="type2" AND value="value2"). So if type is type1 then value is value1 but also if type is type2 then value is something else. So based on the type of an arrayelement I want to query. Given the mongodb example: (product="abc" and score=10) AND (product="xyz" and score=5) - I need documents where this is true. — bartfer, Oct 30 '18 at 14:54
More clarification: If the ordering of the arrayelements would be the same for all records, then something like this should work: `db.collection.find({ data.firstelement.value=value1, data.secondelement.value=value2, data.thirdelement.value=value3, and so on })` But unfortunately one record/document can have 2 elements in the data array another can have 8 elements so I need the type of the element to identify which elements does it have. — bartfer, Oct 30 '18 at 16:47

MongoDB select from array based on multiple conditions

2 Answers2