C#•3y ago

❔ MONGO - Aggregate pipeline - GroupBy mutliple fields

I'm trying to run duplicate detection on nested object like this:

Here's 3 object in the same collection:

[{
  UserId:1,
  Badges: [
    { BadgeId:1, Email: "my.mail@foo.com", ExternalId: 111 },
    { BadgeId:2, Email: "my.mail@foo.com", ExternalId: 222 }
  ]
},
{
  UserId:2,
  Badges: [{
    { BadgeId:3, Email: "my.mail@foo.com", ExternalId: 111 }
  ]
},
{
  UserId:3,
  Badges: [{
    { BadgeId:4, Email: "my.mail@foo.com", ExternalId: 222 }
    { BadgeId:5, Email: "NOPNOP@foo.com", ExternalId: 222 }
  }],
},
{
  UserId:4,
  Badges: [{
    { BadgeId:6, Email: "my.mail@foo.com", ExternalId: 111 }
    { BadgeId:7, Email: "my.mail@foo.com", ExternalId: 111 }
    { BadgeId:8, Email: "NOPNOP@foo.com", ExternalId: 222 }
  }],
}]

[{
  UserId:1,
  Badges: [
    { BadgeId:1, Email: "my.mail@foo.com", ExternalId: 111 },
    { BadgeId:2, Email: "my.mail@foo.com", ExternalId: 222 }
  ]
},
{
  UserId:2,
  Badges: [{
    { BadgeId:3, Email: "my.mail@foo.com", ExternalId: 111 }
  ]
},
{
  UserId:3,
  Badges: [{
    { BadgeId:4, Email: "my.mail@foo.com", ExternalId: 222 }
    { BadgeId:5, Email: "NOPNOP@foo.com", ExternalId: 222 }
  }],
},
{
  UserId:4,
  Badges: [{
    { BadgeId:6, Email: "my.mail@foo.com", ExternalId: 111 }
    { BadgeId:7, Email: "my.mail@foo.com", ExternalId: 111 }
    { BadgeId:8, Email: "NOPNOP@foo.com", ExternalId: 222 }
  }],
}]

[{
  UserId:1,
  Badges: [
    { BadgeId:1, Email: "my.mail@foo.com", ExternalId: 111 },
    { BadgeId:2, Email: "my.mail@foo.com", ExternalId: 222 }
  ]
},
{
  UserId:2,
  Badges: [{
    { BadgeId:3, Email: "my.mail@foo.com", ExternalId: 111 }
  ]
},
{
  UserId:3,
  Badges: [{
    { BadgeId:4, Email: "my.mail@foo.com", ExternalId: 222 }
    { BadgeId:5, Email: "NOPNOP@foo.com", ExternalId: 222 }
  }],
},
{
  UserId:4,
  Badges: [{
    { BadgeId:6, Email: "my.mail@foo.com", ExternalId: 111 }
    { BadgeId:7, Email: "my.mail@foo.com", ExternalId: 111 }
    { BadgeId:8, Email: "NOPNOP@foo.com", ExternalId: 222 }
  }],
}]

[{
  UserId:1,
  Badges: [
    { BadgeId:1, Email: "my.mail@foo.com", ExternalId: 111 },
    { BadgeId:2, Email: "my.mail@foo.com", ExternalId: 222 }
  ]
},
{
  UserId:2,
  Badges: [{
    { BadgeId:3, Email: "my.mail@foo.com", ExternalId: 111 }
  ]
},
{
  UserId:3,
  Badges: [{
    { BadgeId:4, Email: "my.mail@foo.com", ExternalId: 222 }
    { BadgeId:5, Email: "NOPNOP@foo.com", ExternalId: 222 }
  }],
},
{
  UserId:4,
  Badges: [{
    { BadgeId:6, Email: "my.mail@foo.com", ExternalId: 111 }
    { BadgeId:7, Email: "my.mail@foo.com", ExternalId: 111 }
    { BadgeId:8, Email: "NOPNOP@foo.com", ExternalId: 222 }
  }],
}]

Badge Duplicate detection should return, dup'

Badge

Badge

Badge

Badge that have same

Email

Email

Email

Email

```
ExternalId
```
ExternalId
```
ExternalId
```
ExternalId
- Badge 1, 3, 6, 7 as same badge => on user 1, 2, 4 (4 twice)
- Badge 2 and 4 => for user 1 and 3

'm guessing

$unwind

$unwind

$unwind

$unwind will eventually helps as it will flatMap the data on ever Badges:

db.Foos.aggregate([
  { $unwind: { path: "$Badges" } }
])

db.Foos.aggregate([
  { $unwind: { path: "$Badges" } }
])

db.Foos.aggregate([
  { $unwind: { path: "$Badges" } }
])

db.Foos.aggregate([
  { $unwind: { path: "$Badges" } }
])

but I don't know how to

Group By

Group By

Group By

Group By

{ "Badge.Email", "Badge.ExternalId" }

{ "Badge.Email", "Badge.ExternalId" }

{ "Badge.Email", "Badge.ExternalId" }

{ "Badge.Email", "Badge.ExternalId" } and associating all the user with it

donfishy•10/18/23, 8:28 AM

Couldnt you just flatmap the bages, associate the bade and the user for each item, and then just groupBy externalId and email?

var badges = users
    .SelectMany(user => user.Badges, (user, badge) => new { User = user, Badge = badge })
    .GroupBy(item => (item.Badge.ExternalId, item.Badge.Email))
    .ToList()

var badges = users
    .SelectMany(user => user.Badges, (user, badge) => new { User = user, Badge = badge })
    .GroupBy(item => (item.Badge.ExternalId, item.Badge.Email))
    .ToList()

Ddonfishy Couldnt you just flatmap the bages, associate the bade and the user for each ite...

TeBeCoOP•10/18/23, 2:11 PM

do you know how I can see that translated to a Mongo aggregate pipeline

TeBeCoOP•10/18/23, 2:11 PM

so not a C# code

TeBeCoOP•10/18/23, 2:11 PM

because yeah that's what the C# code would look like

TeBeCoOP•10/18/23, 2:12 PM

sadly mongo IQuerayble / Linq Provider was a joke few years ago

TeBeCoOP•10/18/23, 2:12 PM

and I don't know it state today

TeBeCoOP•10/18/23, 2:12 PM

i'm trying ot do exploratory detection for now (so i can't have the full dev loop cycle of build / publish / deploy / run try)

TeBeCoOP•10/18/23, 2:13 PM

hitting with mongo query would saves the exploratory time I think

donfishy•10/18/23, 7:09 PM

Something like this?

db.users.aggregate([
    { $unwind: "$Badges" },
    { 
        $group: {
            _id: { ExternalId: "$Badges.ExternalId", Email: "$Badges.Email" },
            Users: { $push: "$$ROOT" }
        }
    },
    {
        $project: {
            _id: 0,
            Badge: "$_id",
            Users: 1
        }
    }
]);

db.users.aggregate([
    { $unwind: "$Badges" },
    { 
        $group: {
            _id: { ExternalId: "$Badges.ExternalId", Email: "$Badges.Email" },
            Users: { $push: "$$ROOT" }
        }
    },
    {
        $project: {
            _id: 0,
            Badge: "$_id",
            Users: 1
        }
    }
]);

TeBeCoOP•10/18/23, 7:18 PM

$$ROOT$$ROOT i had no idea about that, nice

TeBeCoOP•10/18/23, 7:20 PM

i'll look into that, thx

Ddonfishy Something like this? `db.users.aggregate([ { $unwind: "$Badges" }, { ...

TeBeCoOP•10/19/23, 6:08 AM

stupid question, but did you know Mongo orignally or you had the mongo C# driver to spit the query out ?

donfishy•10/19/23, 6:19 AM

I have not used mongo much in C#, but I used to use it a lot in Node js.

Accord•10/20/23, 6:34 AM

Was this issue resolved? If so, run

/close

/close

otherwise I will mark this as stale and this post will be archived until there is new activity.

❔ MONGO - Aggregate pipeline - GroupBy mutliple fields

Similar Threads

Similar Threads

Similar Threads