Mongo DB Query to check if document array field element present in more than one document

Question

I have been searching through the MongoDB query syntax with various combinations of terms to see if I can find the right syntax for the type of query I want to create.

We have a collection containing documents with an array field. This array field contains ids of items associated with the document.

I want to be able to check if an item has been associated more than once. If it has then more than one document will have the id element present in its array field.

I don't know in advance the id(s) to check for as I don't know which items are associated more than once. I am trying to detect this. It would be comparatively straightforward to query for all documents with a specific value in their array field.

What I need is some query that can return all the documents where one of the elements of its array field is also present in the array field of a different document.

I don't know how to do this. In SQL it might have been possible with subqueries. In Mongo Query Language I don't know how to do this or even if it can be done.

s7vr s7vr · Accepted Answer · 2018-03-08T13:06:42

You can use $lookup to self join the rows and output the document when there is a match and $project with exclusion to drop the joined field in 3.6 mongo version.

$push with [] array non equality match to output document where there is matching document.

db.col.aggregate([
  {"$unwind":"$array"},
  {"$lookup":{
    "from":col,
    "localField":"array",
    "foreignField":"array",
    "as":"jarray"
  }},

  {"$group":{
    "_id":"$_id",
    "fieldOne":{"$first":"$fieldOne"},
    ... other fields
    "jarray":{"$push":"$jarray"}
  }},
  {"$match":{"jarray":{"$ne":[]}}},
  {"$project":{"jarray":0}}
])

Mongo DB Query to check if document array field element present in more than one document

1 Answers