I have a documents like below. I want to retrieve all documents whose address.city == "newyork" and address.id == active.

    [  
     {
    "name": "star1",
    "active": 1,
    "address": [
      {
        "id": 1,
        "city": "newyork"
      },
      {
        "id": 2,
        "city": "sydney"
      }
    ]  
   },  
  {
    "name": "star2",
    "active": 2,
    "address": [
      {
        "id": 1,
        "city": "newyork"
      },
      {
        "id": 2,
        "city": "london"
      }
    ]  
}
]

I have written below query and it Partially works, But It is not returning complete document. I can't use unwind. Do we have any solution without using "unwind". Is it possible to solve a problem only with $match

db.collection.aggregate([
  {
    $unwind: "$address"
  },
  {
    $match: {
      $expr: {
        $eq: [
          "$active",
          "$address.id"
        ]
      },
      "address.city": "newyork"
    }
  }
])

CodePudding user response：

Maybe something like this:

db.collection.aggregate([
{
"$addFields": {
  "address": {
    "$filter": {
      "input": "$address",
      "as": "a",
      "cond": {
        $and: [
          {
            $eq: [
              "$$a.id",
              "$active"
            ]
          },
          {
            $eq: [
              "$$a.city",
              "newyork"
            ]
          }
        ]
      }
    }
  }
}
},
{
 $match: {
  address: {
     $ne: []
    }
  }
}
])

Explained:

1. Use addFields/filter to match only matching documents in the array.
2. Remove the documents with empty address from the array for the cases where no subdocuments is found.

Playground

CodePudding user response：

In case you need to match the whole document containing at least one entry having {address.id==active and address.city==newyork } here is an option:

db.collection.aggregate([
 {
   $match: {
    $expr: {
      "$in": [
        {
          id: "$active",
          city: "newyork"
        },
        "$address"
       ]
     }
   }
 }
])

Explained:

Match only documents having at least one object in address array with id==$active and city=="newyork"

Playground

In case we expect different order inside the address objects , the more correct option is as follow:

db.collection.aggregate([
{
 $match: {
  $expr: {
    $or: [
      {
        "$in": [
          {
            id: "$active",
            city: "newyork"
          },
          "$address"
        ]
      },
      {
        "$in": [
          {
            city: "newyork",
            id: "$active"
          },
          "$address"
        ]
      }
    ]
   }
  }
 }
])

Explained:

Match only documents having at least one object in array with { id==$active and city=="newyork" } or { city=="newyork" and id==$active }

Playground 2