duplicates in array with condition of value

Question

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

turivishal · Accepted Answer · 2022-01-18 17:58:47Z

1

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]
  }
]

edited Jan 18, 2022 at 17:58

answered Jan 18, 2022 at 17:50

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

Collectives™ on Stack Overflow

duplicates in array with condition of value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related