How to ignore extra results that I consider duplicates based on property?

user_2479446 0
Viewed 572

It might a long shot, but here we go. Let's suppose I have the following document definition:

public class Test1 {
    public ObjectId Id {get;set;}
    public int NonUniqueId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Using IMongoCollection<Test1>, I can filter the results to documents the ones where Name = "somevalue", sort by Price and return 10 rows only.

var builder = Builders.Filters;
var filter = builder.Where(x=>x.Name == "somevalue");

var result = await collection.Find(filter).SortBy(x=>x.Price).Limit(10).ToListAsync();

Now to the question at hand. If more than one document is returned by the filter (with Name = "somevalue") and NonUniqueId occurs more than once in that set, I would like to exclude duplicates NonUniqueId and return just the one with the lowest Price.

This logic is quite easy to implement in the code: fetch 10 results, if any of them are 'duplicates' (i.e. NonUniqueId occurs more than once), keep the one with the smallest price and run another search excluding this NonUniqueId. However, it requires multiple DB calls which is not great (up to 10 in the worst case).

In MongoDB (with C# driver), is there a way to ignore these 'duplicate' results based on the property and, ideally, return just the min of another property (or anything to the same effect)?

mongodb-query mongodb-.net-driver mongodb c#
2 Answers

give this pipeline a try:

db.Product.aggregate([
    {
      $match: { Name: "book" }
    },
    {
      $sort: { Price: 1 }
    },
    {
      $group: {
        _id: "$NonUniqueId",
        product: { $first: "$$ROOT" }
      }
    },
    {
      $replaceWith: "$product"
    }
])

https://mongoplayground.net/p/sqSjiXQhdlF

update: added c#

c# version is a bit inconvenient due to the projection needed:

var result = collection.AsQueryable()
               .Where(p => p.Name == "book")
               .OrderBy(p => p.Price)
               .GroupBy(p => p.NonUniqueId)
               .Select(g => new Product
               {
                   Id = g.First().Id,
                   Name = g.First().Name,
                   NonUniqueId = g.First().NonUniqueId,
                   Price = g.First().Price
               })
               .ToList();

test program:

using MongoDB.Driver;
using MongoDB.Driver.Linq;
using MongoDB.Entities;
using MongoDB.Entities.Core;
using System.Linq;

namespace StackOverFlow
{
    public class Product : Entity
    {
        public int NonUniqueId { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }

    public static class Program
    {
        private static void Main()
        {
            new DB("test");

            var result = DB.Queryable<Product>()
                           .Where(p => p.Name == "book")
                           .OrderBy(p => p.Price)
                           .GroupBy(p => p.NonUniqueId)
                           .Select(g => new Product
                           {
                               ID = g.First().ID,
                               Name = g.First().Name,
                               NonUniqueId = g.First().NonUniqueId,
                               Price = g.First().Price
                           })
                           .ToList();
        }
    }
}

user_43684850

Try the Distinct method, as shown in this answer. You can even define a custom Equals function (by defining a class that implements IEqualityComparer) that performs the checks you need to perform

user_47950750
Related