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

Mongodb – Query to return documents with similar fields

Currently, I have these MongoDB documents:

{
    "_id" : ObjectId("someId1"),
    "employeeId": 1,
    "vehicleName": "accord"
},
{
    "_id" : ObjectId("someId12"),
    "employeeId": 1,
    "vehicleName": "camry"
},
{
    "_id" : ObjectId("someId3"),
    "employeeId": 2,
    "vehicleName": "civic"
},
{
    "_id" : ObjectId("someId4"),
    "employeeId": 2,
    "motorcycleName": "shadow"
},
{
    "_id" : ObjectId("someId5"),
    "employeeId": 3,
    "vehicleName": "corolla"
},
{
    "_id" : ObjectId("someId6"),
    "employeeId": 3,
    "vehicleName": "elantra"
},
{
    "_id" : ObjectId("someId7"),
    "employeeId": 4,
    "vehicleName": "sonata"
}

I am trying to create a query to return the employeeIds that have more than record containing a vehicleName, which in this case is only 1 and 3. I’m not sure if this can be achieved with a simple query however. Does it require an aggregate?

Tags:

2

Answers


  1. 0

    Perform a simple $group to get all vehicleName. Depends on your situation, you may use either $push or $addToSet. Perform a $match on the $size of the resulting array.

    db.collection.aggregate([
  {
    "$group": {
      "_id": "$employeeId",
      "vehicleNames": {
        "$push": "$vehicleName"
      }
    }
  },
  {
    "$match": {
      $expr: {
        $gt: [
          {
            "$size": "$vehicleNames"
          },
          1
        ]
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    Since you have a vehicleName and a motorcycleName field it may be worth matching documents that only have a vehicleName field first to reduce the set then use $group to group them by employeeId and count how many occourances of each employeeId there were. At the end you can then $match where the count was greater than 1 like so:

    db.collection.aggregate([
  {
    $match: {
      vehicleName: {
        $exists: true
      }
    }
  },
  {
    $group: {
      _id: "$employeeId",
      count: {
        $sum: 1
      }
    }
  },
  {
    $match: {
      count: {
        $gt: 1
      }
    }
  }
])

    See HERE for a working example.

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

Back To Top
Search