How to find MongoDB records that have matching values in an array

Question

I am tracking user IP's by adding them to an array on the user document like so:

{
  "_id": "LafnHzmQL6rBmXNxJ",
  "name": "someuser",
  "displayName": "SomeUser",
  "knownIPs": ["1.1.1.1", "2.2.2.2", "3.3.3.3"]
}

How can I find any documents where any 1 of the knownIPs match any 1 of the knownIPs in another (without specifying a particular value) regardless of what the actual IP is.

The goal is to identify people who are using multiple accounts so they can be flagged programmatically for further inspection. I have over 40,000 users, will this be too intensive of an aggregation?

What command you have tried so far?

dikesh
– dikesh

2016-01-30 06:54:49 +00:00
Commented Jan 30, 2016 at 6:54 — dikesh
– dikesh, Commented Jan 30, 2016 at 6:54
Use $in docs.mongodb.org/master/reference/operator/query/in

styopdev
– styopdev

2016-01-30 07:59:10 +00:00
Commented Jan 30, 2016 at 7:59 — styopdev
– styopdev, Commented Jan 30, 2016 at 7:59

chridam · Accepted Answer · 2016-01-30 11:59:23Z

2

Use the following aggregation pipeline:

db.collection.aggregate([
    { "$unwind": "$knownIPs" },
    {
        "$group": {
            // Group by the IP address
            "_id": "$knownIPs",

            // Count number of matching docs for the group
            "count": { "$sum":  1 },

            // Save the _id for matching docs
            "docs": { "$push": "$_id" }
        }
    },
    {
        "$match": {
            "count": { "$gt": 1 }
        }
    }
])

answered Jan 30, 2016 at 11:59

chridam

104k26 gold badges246 silver badges243 bronze badges

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

Comments

ΦXocę 웃 Пepeúpa ツ · Accepted Answer · 2016-01-30 12:40:26Z

1

I think you should use $unwind function on the array of knownIPs. so that it will give you three child object from parent object.

For example:

db.document_name.aggregate( [ { $unwind : "$knownIPs" } ] ) gives you

{
  "_id": "LafnHzmQL6rBmXNxJ",
  "name": "someuser",
  "displayName": "SomeUser",
  "knownIPs": "1.1.1.1"
}

{
  "_id": "LafnHzmQL6rBmXNxJ",
  "name": "someuser",
  "displayName": "SomeUser",
  "knownIPs": "2.2.2.2"
}

{
  "_id": "LafnHzmQL6rBmXNxJ",
  "name": "someuser",
  "displayName": "SomeUser",
  "knownIPs": "3.3.3.3"
}

After unwind operation, perform GroupBy operation on all the objects generated from unwind operation. So it will give the number of users who have same Ip.

edited Jan 30, 2016 at 12:40

ΦXocę 웃 Пepeúpa ツ

48.4k17 gold badges77 silver badges102 bronze badges

answered Jan 30, 2016 at 12:08

Brijesh Patel

211 bronze badge

Collectives™ on Stack Overflow

How to find MongoDB records that have matching values in an array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related