What I have is a collection of documents in MongoDB that have the structure something like this

[
  {
    "userid": "user1",
    "addresses": [
      {
        "type": "abc",
        "street": "xyz"
      },
      {
        "type": "def",
        "street": "www"
      },
      {
        "type": "hhh",
        "street": "mmm"
      },
      
    ]
  },
  {
    "userid": "user2",
    "addresses": [
      {
        "type": "abc",
        "street": "ccc"
      },
      {
        "type": "def",
        "street": "zzz"
      },
      {
        "type": "hhh",
        "street": "yyy"
      },
      
    ]
  }
]

If I can give the "type" and "userid", how can I get the result as

[
  {
    "userid": "user2",
    "type": "abc",
    "street": "ccc",   
  }
]

It would also be great even if I can get the "street" only as the result. The only constraint is I need to get it in the root element itself and not inside an array

CodePudding user response：

Something like this:

db.collection.aggregate([
{
 $match: {
   userid: "user1" , "address.type":"abc"
}
},
{
$project: {
  userid: 1,
  address: {
    $filter: {
      input: "$addresses",
      as: "a",
      cond: {
        $eq: [
          "$$a.type",
          "abc"
        ]
      }
    }
  }
}
},
{
  $unwind: "$address"
},
{
$project: {
  userid: 1,
  street: "$address.street",
  _id: 0
  }
}
])

explained:

1. Filter only documents with the userid & addresess.type you need
2. Project/Filter only the addresses elements with the needed type
3. unwind the address array
4. project only the needed elements as requested

For best results create index on the { userid:1 } field or compound index on { userid:1 , address.type:1 } fields

playground

CodePudding user response：

You should be able to use unwind, match and project as shown below:

db.collection.aggregate([
{
  "$unwind": "$addresses"
},
{
  "$match": {
    "addresses.type": "abc",
    "userid": "user1"
  }
},
{
  "$project": {
    "_id": 0,
    "street": "$addresses.street"
  }
}
])

You can also duplicate the match step as the first step to reduce the number of documents to unwind.

Here is the playground link.

There is a similar question/answer here.