CODEWITHSUNDEEP
pythonmongodbpymongoaggregation
Hanan Cohen
Hanan Cohen

Reputation: 383

Pymongo - aggregating from two databases

Noob here. I need to get a report that is an aggregate of two collections from two databases. Tried to wrap my head around the problem but failed. The examples I have found are for aggregating two collections from the same database.

Collection 1

SweetsResults = client.ItemsDB.sweets

Collection sweets : _id, type, color

Collection2

SearchesResults = client.LogsDB.searches

Collection searches : _id, timestamp, type, color

The report I need will list all the sweets from the type “candy” with all the listed colors in the sweets collection, and for each line, the number (count) of searches for any available combination of “candy”+color.

Any help will be appreciated.

Thanks.

Upvotes: 0

Views: 945

Answers (2)

aiven
aiven

Reputation: 4313

Exactly the same as in @Veeram answer but with python:

uri = 'mongodb://localhost'
client = MongoClient(uri)
items_db = client.get_database('ItemsDB')
logs_db = client.get_database('LogsDB')

docs = []
aggr = items_db.get_collection('sweets').aggregate([
    {'$match': {"type": "candy"}},
    {'$group': {'_id': {'type': "$type", 'color': "$color"}}},
    {'$project': {'_id': 0, 'type': "$_id.type", 'color': "$_id.color"}},
])
for doc in aggr:
    doc['count'] = logs_db.get_collection("searches").count({"type": "candy", "color": doc['color']})
    docs.append(doc)

Upvotes: 1

s7vr
s7vr

Reputation: 75914

You can use the below script in mongo shell.

Get the distinct color for each type followed by count for each type and color combination. 

var itemsdb = db.getSiblingDB('ItemsDB');
var logsdb = db.getSiblingDB('LogsDB');

var docs = [];
itemsdb.getCollection("sweets").aggregate([
    {$match:{"type":"candy"}},
    {$group: {_id:{type:"$type", color:"$color"}},
    {$project: {_id:0, type:"$_id.type", color:"$_id.color"}}
]).forEach(function(doc){ 
    doc.count = logsdb.getCollection("searches").count({ "type":"candy","color":doc.color});
    docs.push(doc)
});

Upvotes: 1

Related Questions