I have the following documents in mongo:

{ 
    "_id" : 1, 
    "env" : "pilot", 
    "service_name" : "test1", 
    "instances" : 
    [ 
        { 
        "location" : "server1", 
        "type" : "passive", 
        }, 
        { 
        "worker_location" : "server2", 
        "type" : "active" 
        } 
    ]
},
{ 
    "_id" : 2, 
    "env" : "pilot", 
    "service_name" : "test2", 
    "instances" : 
    [ 
        { 
        "worker_location" : "server1", 
        "type" : "active", 
        }, 
        { 
        "worker_location" : "server2", 
        "type" : "passive" 
        } 
    ]
}

I'm trying to setup a query to return the only the services that are active on certain servers. So, if I want services that are only active on server2, I should get test1. However, I'm still getting all the services regardless of what server they are on.

This is my query string so far:

db.service.find({"env":"pilot", "instances.worker_location":"server2", "instances.type":"active"}, {"service_name":1})

I expect to only get test1, but I get both services. How would I go about doing this?

Edit: I have also tried using the and operator:

db.service.find( {$and: [
{"instances.worker_location":"server2", 
"instances.type":"active"}] }, {"service_name":1})

With no success.

CodePudding user response：

I believe you need to use the aggregation pipeline for this, specifically the $unwind operator, if you only want the matched instances. Although, I could be misunderstanding the question.

Consider the following ... (live demo here)

Database

[
  {
    "_id": 1,
    "env": "pilot",
    "service_name": "test1",
    "instances": [
      {
        "location": "server1",
        "type": "passive",
        
      },
      {
        "worker_location": "server2",
        "type": "active"
      }
    ]
  },
  {
    "_id": 2,
    "env": "pilot",
    "service_name": "test2",
    "instances": [
      {
        "worker_location": "server1",
        "type": "active",
        
      },
      {
        "worker_location": "server2",
        "type": "passive"
      }
    ]
  }
]

Query

db.collection.aggregate([
  {
    $unwind: "$instances"
  },
  {
    $match: {
      $and: [
        {
          env: "pilot"
        },
        {
          "instances.type": "active"
        },
        {
          "instances.worker_location": "server2"
        }
      ]
    }
  },
  {
    $project: {
      _id: 1,
      service_name: 1
    }
  }
])

Result

[
  {
    "_id": 1,
    "service_name": "test1"
  }
]