Find and groupby

Question

If I have the following documents in my collection

{ "_id" : 1, "domainName" : "test1.com", "hosting1" : "hostgator.com",    "hosting2" : "hostgator.com",    sID : 1}
{ "_id" : 2, "domainName" : "test2.com", "hosting1" : "cloud.google.com", "hosting2" : "aws.amazon.com",   sID : 2}
{ "_id" : 3, "domainName" : "test3.com", "hosting1" : "aws.amazon.com",   "hosting2" : "cloud.google.com", sID : 2}

Suppose if I want to find "cloud.google.com" in either hosting1 or hosting2

I would write a Query like

db.chats.find({$or : [{ hosting1 : 'cloud.google.com'}, { hosting2 : 'cloud.google.com'}]}).pretty(); 

this will fetch me two records like below

{ "_id" : 2, "domainName" : "test2.com", "hosting1" : "cloud.google.com", "hosting2" : "aws.amazon.com",   sID : 2}
{ "_id" : 3, "domainName" : "test3.com", "hosting1" : "aws.amazon.com",   "hosting2" : "cloud.google.com", sID : 2}

Suppose if I want to find and groupby "sID" field

Suppose I want to find "cloud.google.com" in either hosting1 or hosting2 and then GROUPBY by "sID" : 2 means

My result will be

{ "_id" : 2, "domainName" : "test2.com", "hosting1" : "cloud.google.com", "hosting2" : "aws.amazon.com",   sID : 2}

How to write a query for my above requirement

My sql query will be

SELECT *
FROM chats
WHERE (hosting1 = 'cloud.google.com' OR hosting2 = 'cloud.google.com')
GROUPBY sID;

I have gone through mongoDB $group but I could not get it work

Can you please give me the insight of how to acheive this.Your help is greatly appreciated.Thanks.

Answer 1

You can use Mongo Aggregation using $first and $$ROOT as following:

db.collection.aggregate({
    $match: {
    $or: [{
        "hosting1": "aws.amazon.com"
    }, {
        "hosting2": "aws.amazon.com"
    }]
    }
}, {
    $group: {
    "_id": "$sID",
    "domain": {
        $first: "$$ROOT"
    }
    }
})

$$ROOT - It always references the root document. This means the top-level document which is currently being processed in the aggregation pipeline stage.

$first - It returns the value that results from applying an expression to the first document in a group of documents that share the same group by key. Only meaningful when documents are in a defined order.

Also you can use simple find query like -

db.collection.find({
    $or: [{
    hosting1: 'cloud.google.com'
    }, {
    hosting2: 'cloud.google.com'
    }]
}).limit(1)

Answer 2

In your case you don't need to group your document. use $limit instead.

db.collection.aggregate(
    [
      { $match: 
        { 
          $or : [
                  { hosting1 : 'cloud.google.com' }, 
                  { hosting2 : 'cloud.google.com'}
          ]
        }
      },
      { $limit: 1 }
    ]
)

Also you really don't need aggregation you can use the find method.

db.collection.find({ 
    $or :[
           { hosting1 : 'cloud.google.com'},
           { hosting2 : 'cloud.google.com'}]
     }
).limit(1)