Filtering results in mongodb

Question

maybe a simple question for experimented developers with MongoDB, but I'm not getting a solution:

My json with "Stations" collection:

{
"code": "XX",
"variables": [
    {
        "code": 1,
        "items": [
            {
                "value": 81
            },
            {
                "value": 77
            }
        ]
    },
    {
        "code":2,
        "items": [
            {
                "value": 33
            }
        ]
    }
]
}
....

I want to filter "Station" collection to only get variable with code 1 and item with value 81, obtaining something similar to this:

{
"code": "XX",
"variables": [
    {
        "code": 1,
        "items": [
            {
                "value": 81
            }
        ]
    }
]
}

Due json contains arrays in different levels, my approach (mongo shell) was:

db.stations.find(
{"code": "XX"},
{
    "variables": 
    { $elemMatch: 
        {
            "code": 1,
            "items":
             { $elemMatch: 
                {
                    "value": 81
                }
            }
        }
    }
}
)

But that's getting all items of the same level of 'value: 81', not only this.

Any idea? I also tried to do something with "aggregate" operator and $redact, but no result...thanks!

Neo-coder · Accepted Answer · 2015-04-22 08:48:46Z

As per mongo $elemMatch documentation

The $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria.

hence using $elemMatch it match in items.value:81 and return whole matching items array like below query

db.stations.find({
  "code": "XX"
}, {
  "variables": {
    "$elemMatch": {
      "code": 1
    }
  },
  "variables": {
    "$elemMatch": {
      "items": {
        "$elemMatch": {
          "value": 81
        }
      }
    }
  }
}).pretty()

This return items.value:81 and items.value:77 because of elemMatch match one elements in array. Or same if used in project as below it shows same result like above query

 db.stations.find({
   "code": "XX",
   "variables": {
     "$elemMatch": {
       "code": 1
     }
   },
   "variables": {
     "$elemMatch": {
       "items": {
         "$elemMatch": {
           "value": 81
         }
       }
     }
   }
 }, {
   "code": 1,
   "variables.code.$": 1
 }).pretty()

So If you find your expected output then you should use mongo aggregation as below :

db.stations.aggregate({
  "$match": {
    "code": "XX",
    "variables.code": 1
  }
}, {
  "$unwind": "$variables"
}, {
  "$unwind": "$variables.items"
}, {
  "$match": {
    "variables.items.value": 81
  }
}, {
  "$group": {
    "_id": "$code",
    "data": {
      "$push": "$variables"
    }
  }
}, {
  "$project": {
    "code": "$_id",
    "variables": "$data",
    "_id": 0
  }
}).pretty()

Collectives™ on Stack Overflow

Filtering results in mongodb

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