Question 115293 in Mongodb Question posted in
The official documentation can be found here.

MongoDB – How to query unique fields in many documents?

I have a db with 80,000 documents in it and each document has a field named Location like below:

Location: "82 Some Street | Gautham PS M1M 1M1"

This address field is repeated in many documents. I want to do a query that does a count of the documents with unique addresses only in Location field. How to do that?

So even though I have 80,000 documents, probably only 50,000 of them are unique and other 30,000 have the repeat address so I want to get that 50,000 count accurately.

I guess first, I have to search all the address and then take out the repeats and then do a count?

I tried distinct command but failed.

Tags:

3

Answers


  1. 0

    You can do it with Aggregation framework:

    • $group with $addToSet – to get all the unique locations and add them to the unique_locations array.
    • $project with $size – to get the total count of unique locations by calculating the length of the unique_locations array.
    db.collection.aggregate([
  {
    "$group": {
      "_id": null,
      "unique_locations": {
        "$addToSet": "$Location"
      }
    }
  },
  {
    "$project": {
      "total_unique": {
        "$size": "$unique_locations"
      }
    }
  }
])

    Working example

    Login or Signup to reply.
  2. 0
    • Maybe you should try;
      let location="82 Some Street | Gautham PS M1M 1M1";

  const getUniqueLocations = await YourDB.find({Location : {$ne: location}}).countDocuments();

    countDocuments() returns the count.

    Login or Signup to reply.
  3. 0

    You can use this query:

    db.collection.aggregate([
  {
    "$group": {
      "_id": "$Location",
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$match": {
      count: 1
    }
  },
  {
    "$count": "uniqueAddresses"
  }
])

    In this query, first, we group the documents by address and calculate the counts. Next, we only keep the counts where the value is 1. Then using the $count operator we calculate the count.

    Playground link.

    Try this as well:

    db.collection.aggregate([
  {
    "$group": {
      "_id": null,
      "unique": {
        "$addToSet": "$statusCode"
      },
      
    }
  },
  {
    "$addFields": {
      "count": {
        "$size": "$unique"
      }
    }
  },
  {
    "$project": {
      _id: 0
    }
  }
])

    Playground link.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search