Query all documents contains a list with multiple elements with the same value

Question

I tried to find a solution to this specific issue: I want to get all the documents that in their list ('metadata') there are more then one element with the same category.

An example:

As to the example above:

I have a document with 'metadata' list and there are more than one element with the same category ('yaw_rate') so I want to receive this document.

If I had just one element (or no elements) that matches that category I don't want to get this document.

Answer 1

this is possible through aggregation framework:

[
  {
    $unwind: {
      path: '$metadata'
    }
  },
  {
    $group: {
      _id: {
        _id: '$_id',
        category: '$metadata.category'
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $match: {
      count: {
        $gt: 1
      }
    }
  },
  {
    $lookup: {
      from: 'your_collection_name',
      localField: '_id._id',
      foreignField: '_id',
      as: 'object'
    }
  },
  {
    $project: {
      _id: {
        $arrayElemAt: ['$object._id',0]
      },
      metadata: {
        $arrayElemAt: ['$object.metadata',0]
      }
    }
  }
]

the last two stages are looking up resulted ids from previous stages, with your original collection, and obtaining wanted information from documents. if you just need the ids, these stages are unnecessary.