MongoDB - Find documents with earliest occurrence of duplicate value

Question

I have a collection within MongoDB (4.4 but the version isn't important to me) and one of the document values is an array of URLs. There will be multiple documents with multiple URLs per document (within the array) and some of those URLs will already exist. I would like to select each document with the earliest occurrence of each URL (the intention being to mark it as the 'origin'.

MongoPlayground link for sample collection - https://mongoplayground.net/p/ZAgCqr517-8

  {
    "title": "story1_first",
    "isoDate": "2022-01-01T00:00:00.000Z",
    "links": [
      "www.first.com/article1",
      "www.anotherdomain.com"
    ]
  },
  {
    "title": "story1_mention",
    "isoDate": "2022-01-10T00:00:00.000Z",
    "links": [
      "www.first.com/article1",
      "www.somesite.com"
    ]
  },
  {
    "title": "story2_first",
    "isoDate": "2022-01-20T00:00:00.000Z",
    "links": [
      "www.newstory.com/article2",
      "www.anothercompany.com"
    ]
  },
  {
    "title": "story2_mention",
    "isoDate": "2022-01-20T00:00:00.000Z",
    "links": [
      "www.newstory.com/article2",
      "www.anothercompany.com"
    ]
  }
]

In this example, I would like the query / aggregation to return the two documents with "first" in the title as they are the documents which share a common URL within 'links' and are the documents which have the earliest date. Similar to how a search engine ranks up a site depending on how many other sites link to it.

ray · Accepted Answer · 2022-01-13 03:15:50Z

2

You can do the followings in an aggregation pipeline:

$unwind links so the documents are in links level
$sort on isoDate to get the first document
$group by links to get count inbetween group and the id of the first document. In your example, title is taken as unique identifier.
$match with count > 1 to get title that share the same link
$group to dedupe the unique identifier we found in step 3
$lookup back the original document and do some cosmetics by $replaceRoot

db.collection.aggregate([
  {
    "$unwind": "$links"
  },
  {
    $sort: {
      isoDate: 1
    }
  },
  {
    $group: {
      _id: "$links",
      first: {
        $first: "$title"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $match: {
      count: {
        $gt: 1
      }
    }
  },
  {
    $group: {
      _id: "$first"
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "localField": "_id",
      "foreignField": "title",
      "as": "rawDocument"
    }
  },
  {
    "$unwind": "$rawDocument"
  },
  {
    "$replaceRoot": {
      "newRoot": "$rawDocument"
    }
  }
])

Here is the Mongo playground for your reference.

answered Jan 13, 2022 at 3:15

ray

15.6k12 gold badges98 silver badges105 bronze badges

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

2 Comments

Pramod Kharade Over a year ago

Excellent answer with details explanation @ray

SG84 Over a year ago

@ray that's awesome, thanks for the answer and the clear explanation too.

Collectives™ on Stack Overflow

MongoDB - Find documents with earliest occurrence of duplicate value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related