CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
ProcolHarum
ProcolHarum

Reputation: 741

duplicates in array with condition of value

Monogplayground

I have the following document with an array (names gists). Example of a document:

 {
    "_id": ObjectId("60a2c0621e5f043b735e36ef"),
    "car_id": 78,
    "terminal": "JFK",
    "gists": [
      "A",
      "M",
      "C",
      "D",
      "Q",
      "J",
      "F"
    ]
  },

My objective is to query for all documents that have a duplicated "A" in them and get their car_id.

While I can find duplicates, I couldn't get the results of "A" in gists.

db.collection.aggregate([
  {
    "$project": {
      "gists": 1
    }
  },
  {
    "$unwind": "$gists"
  },
  {
    "$group": {
      "_id": {
        "_id": "$_id",
        "cid": "$gists"
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$match": {
      "$and": [
        {
          "count": {
            "$gt": 1
          }
        },
        {
          "gists": "A"  **//<---- this is where I get it off. Gist must contain A in the array**
        }
      ]
    }
  },
  {
    "$group": {
      "_id": "$_id._id",
      "gists": {
        "$addToSet": "$_id.cid"
      }
    }
  }
])

Upvotes: 1

Views: 372

Answers (1)

turivishal
turivishal

Reputation: 36104

You can try $expr expression operator with aggregation operators,

  • $filter to iterate loop of gists array and check for value "A"
  • $size to get total elements from above filtered result
  • $gt to check above size is greater than 1
  • $group by null and construct the array of car_id
db.collection.aggregate([
  {
    $match: {
      $expr: {
        $gt: [
          {
            $size: {
              $filter: {
                input: "$gists",
                cond: { $eq: ["$$this", "A"] }
              }
            }
          },
          1
        ]
      }
    }
  },
  {
    $group: {
      _id: null,
      car_id: { $push: "$car_id" }
    }
  }
])

Playground

Result:

[
  {
    "_id": null,
    "car_id": [79, 80]
  }
]

Upvotes: 1

Related Questions