Need to append values based on one column value

Working with Data
1 
{ "_id" : ObjectId("56e0a3a2d59feaa43fba49d5"), "timestamp" :2022-11-14T17:36:06.555+00:00, "city" : "London ", "mobilenumber" :  "983xxxxxxxx" }
 { "_id" : ObjectId("56e0a3a2d59feaa43fba49d6"), "timestamp" : 2022-11-14T17:36:06.555+00:00, "City" : "London", "mobilenumber" :  "943xxxxxxxx" }
 { "_id" : ObjectId("56e0a3a2d59feaa43fba49d7"), "timestamp" : 2022-11-14T17:36:06.555+00:00, "City" : "Dublin", "mobilenumber" :  "9324xxxxxxxx" }
{ "_id" : ObjectId("56e0a3a2d59feaa4ba49d7"), "timestamp" : 2022-11-14T17:36:06.555+00:00, "City" : "Dublin", "mobilenumber" :  "91233xxxxxxxx" }

This is a collection in my Mongodb.But i want the result to be like below:

{ "_id" : ObjectId("56e0a3a2d59feaa43fba49d5"), "timestamp" :2022-11-14T17:36:06.555+00:00, "city" : "London ", "mobilenumber" :  ["983xxxxxxxx","943xxxxxxxx"] }
 
 { "_id" : ObjectId("56e0a3a2d59feaa43fba49d7"), "timestamp" : 2022-11-14T17:36:06.555+00:00, "City" : "Dublin", "mobilenumber" : [ "9324xxxxxxxx", "91233xxxxxxxx"] }

I want all the mobile numbers of london in one list under mobile number column and same thing even for dublin
How do i achieve this in mongodb?
Thanks in advance

3

There is a few things about your sample documents that need to be resolve first.

  1. In some documents, the city field is speed city lower-case c, while in others it is spelled City. Is that a typo or you really have different spelling. Field names are case sensitives.

  2. The timestamp of all input documents are the same. Is that always the case? If not do you still group by city if the timestamp is different? Or do you group by city/timestamp If the timestamps are different and you still group by city, which timestamp do you keep? The smallest or the biggest?

  3. You have 2 documents with the same _id in the source documents. That is not really possible so it makes hard to know which one you keep in the result set. Do you really what to keep one of the source document’s _id?

  4. Is there any other fields from the source documents that needs to be in the result set?

What ever the answers to the above the solution will make use of $group and $push, so take a look at the documentation:

3 Likes
4

Hi @steevej,
The mistakes occured as what i provided in above example is just a sample data which i created on my own just for better understanding to the reader of what exactly i wanted.
Anyways, I have tried the $group and $push as suggested by you and it is working fine.
This is my query now:

reports_col.aggregate([{"$group": {"_id": "$City", "MobileNumber": {"$push": "$mobilenumber"}}}, {"$project": { "City":"$_id","MobileNumber":1,"_id":0 }},{"$out": "updatedreports"}])

But applying the $group and $push to millions of collections will make the process slow,right?
Is there anyway other than $group and $push to make it more time efficient ?

5

Yes but $group is how you group documents together and this is your use-case.

The index {City:1,mobilenumber:1} might help. With the index may be $sort:{City:1} before $group may improve.

A funky idea would be to use self $lookup after a simpler $group as like the following untested code:

group = { "$group" : { "_id" : "$City" } }
lookup = { "$lookup" : {
  "from" : "reports_col" ,
  "localField" : "_id" ,
  "foreignField" : "City" ,
  "as" : "MobileNumber" ,
  "pipeline" : [ { "$project" : { "mobilenumber" : 1 , "_id" : 0 } }  ]
} }
/*
  After $lookup MobileNumber is an array of objects, but we want an array
  of phone numbers.   So we $map to convert each object to the value of the field mobile number.
*/
map = { "$set" : { 
  "MobileNumber" : { "$map" : { /* details left out */ } }
} }
reports_col.aggregate( [ group , lookup , map ] )

I have no clue if the above is faster but I suspect that it might take less memory. A $group stage output its documents when all input documents are processed. We still have a $group but since we only keep the city name, this group might be faster and definitively takes less memory.

2 Likes
6

Hi @steevej ,
This above solution works for me .It was time efficient too :smile:

2 Likes
7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.