Question 117444 in Mongodb Question posted in
The official documentation can be found here.

How to compare a field to a field of a array in mongodb

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"
    }
  }
])
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search