MongoDb: Find exact array match with duplicates

Question

m trying to write a mongoDB query to find documents matching given example:

I have collection of users

{
  "userId": "1",
  "visitedPlaces": [
    {
      "city": "Kair",
      "country": "Egypt"
    },
    {
      "city": "Paris",
      "country": "France"
    },
    {
      "city": "Kair",
      "country": "Egypt"
    },

  ]
},

{
  "userId": "2",
  "visitedPlaces": [
    {
      "city": "Kair",
      "country": "Egypt"
    },
    {
      "city": "Paris",
      "country": "France"
    },
    {
      "city": "Paris",
      "country": "France"
    }
  ]
}

I want to write a query that will return me users which visited 'Kair' twice and 'Paris' once (user "2" is not matching this case)

I have tried query like

.find({"visitedPlaces.city": { "$all": ["Kair", "Paris", "Kair"] }, "visitedPlaces": { "$size": 3  } })

but it returns both users.

Is there a way to write such query in mongo ?

Ashok · Accepted Answer · 2020-11-27 16:28:05Z

1

what about this one

db.getCollection('vists').aggregate([{
  '$addFields': {
    'countVisitedPlaces': {
      '$map': {
        'input': { '$setUnion': ['$visitedPlaces.city']},
        'as': 'city',
        'in': {
          'city': '$$city',
          'noOfTimeCityVists': {
            '$size': {
              '$filter': {
                'input': '$visitedPlaces',
                'as': 'visitedCity',
                'cond': { '$eq': ['$$visitedCity.city', '$$city'] }
            }
          }
        }
      }
    }
  }
},
// here you got city wise visits count
{ 
 $match: { 
  $and: [{
   'countVisitedPlaces': {
     '$elemMatch': {'noOfTimeCityVists': 2, 'city': 'Kair'} 
   }},
   {
    'countVisitedPlaces': {
     '$elemMatch': {'noOfTimeCityVists': 1, 'city': 'Paris'} 
   }}
  ]}
}])

You change last stag of pipeline for different differnt search and your result.

edited Nov 27, 2020 at 16:28

answered Nov 27, 2020 at 16:02

Ashok

2,9621 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

homik Over a year ago

i dont see actual search condition here.

RLD · Accepted Answer · 2020-11-28 15:37:18Z

Check the below query. You just need to modify $match depending on your condition.

db.collection.aggregate({
  "$unwind": "$visitedPlaces"
},
{
  "$group": {
    "_id": {
      "userId": "$userId",
      "city": "$visitedPlaces.city"
    },
    "count": {
      "$sum": 1
    }
  }
},
{
  "$project": {
    "userId": "$_id.userId",
    "cityCount": {
      "city": "$_id.city",
      "count": "$count"
    },
    "_id": 0
  }
},
{
  "$group": {
    "_id": "$userId",
    "cityCount": {
      "$push": "$cityCount"
    }
  }
},
{
  "$match": {
    "cityCount": {
      "$all": [
        {
          "$elemMatch": {
            "city": "Kair",
            "count": 2
          }
        },
        {
          "$elemMatch": {
            "city": "Paris",
            "count": 1
          }
        }
      ]
    }
  }
},
{
  "$project": {
    "userId": "$_id",
    "_id": 0
  }
})

Here is MongoPlayground for you.

Collectives™ on Stack Overflow

MongoDb: Find exact array match with duplicates

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related