Mongodb aggregation query by grouping multiple collections

Question

I have two collections in mongodb.

requests collection

_id
ipaddress
createdon

geoip collection

_id
ipaddress
country
city

geoip is a look up table for IP address, which is unique here.

I need the following result, to find the total number of requests from each country. In other words, the following 2 columns.

country and recordcount (from requests collection based on the ipaddress)

Since this requires grouping 2 separate collections and combining them to get the results, I am at a loss here.

hyades · Accepted Answer · 2016-12-08 19:36:10Z

4

You would need to use a $lookup stage operation which is used to perform joins with other collections and fetch the results. Then group on the selected field to the required counts.

[
 {$lookup: {
    from: 'geoip',
    localField: 'ipaddress',
    foreignField: 'ipaddress',
    as: 'data'
 }},
 {$unwind: 'data'},
 {$group: {_id: '$data.country', count: {$sum: 1}}}

]

answered Dec 8, 2016 at 19:36

hyades

3,2101 gold badge22 silver badges36 bronze badges

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

4 Comments

FoxShrill Over a year ago

This works great! Is it possible to achieve this through map reduce as lookup is not supported in older mongodb versions.

hyades Over a year ago

@FoxShrill I havn't used map-reduce a lot. You can probably read up on this here tebros.com/2011/07/…

taxilian Over a year ago

map/reduce did not support any form of cross-collection join; the best you could do would be to run two separate map/reduce steps and have the second one "reduce" the output of the first, filling in the missing data... but it would be really inefficient

Khaleel Hmoz Over a year ago

but how to do it if we need to join 3 collections for ex?

Collectives™ on Stack Overflow

Mongodb aggregation query by grouping multiple collections

requests collection

geoip collection

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

requests collection

geoip collection

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related