mongodb query

mongo dB tutorial link
https://data-flair.training/blogs/mongodb-aggregation-tutorial/
https://medium.com/@mahesh_joshi/mongodb-important-queries-4cad9e89a9ad

db.listings.aggregate([
    { $match: { "neighborhood": { $nin: ["", null] }, is_deleted: false } },
    { $group: { '_id': '$neighborhood', data: { $push: "$neighborhood" } }},
    { $sort: { _id: 1 } },
]);
db.listings.aggregate([
        { $match: { "neighborhood": { $nin: ["", null] }, is_deleted: false } },
        { $group: { '_id': '$neighborhood' } },
        { $sort: { _id: 1 } }
    ]);


[
  {
    "$lookup": {
      "from": "users",
      "localField": "agent_id",
      "foreignField": "_id",
      "as": "user_details"
    }
  },
  {
    "$unwind": "$user_details"
  },
  {
    "$project": {
      "user_details.password": 0,
      "user_details.__v": 0,
      "user_details.is_onboard": 0,
      "user_details.is_active": 0,
      "user_details.is_open": 0,
      "__v": 0
    }
  },
  {
    "$lookup": {
      "from": "user_types",
      "localField": "user_details.user_types",
      "foreignField": "_id",
      "as": "user_details.user_types"
    }
  },
  {
    "$unwind": "$user_details.user_types"
  },
  {
    "$match": {
      "is_deleted": false,
      "is_visibility": true,
      "is_complete_listing": true,
      "is_archived": false,
      "$or": [
        {
          "user_details.organisation": "eclips"
        },
        {
          "is_public": true
        }
      ],
      "bedroom_unit": {
        "$gte": 0
      },
      "bathroom_unit": {
        "$gte": 0
      },
      "rent": {
        "$gte": 0
      },
      "user_details.organisation": {}
    }
  },
  {
    "$sort": {
      "created_on": -1
    }
  },
  {
    "$facet": {
      "paginatedResults": [
        {
          "$skip": 0
        }
      ],
      "totalCount": [
        {
          "$count": "count"
        }
      ]
    }
  }
]
db.listing_links.aggregate([
    {
        $project: {
            "views": {
                $filter: {
                    input: '$views',
                    as: 'item',
                    cond: { $and: [{ $gt: ['$$item.viewed_on', ISODate("2019-09-22T00:39:31.280+05:30")] }, { $lte: ['$$item.viewed_on', ISODate("2019-09-25T00:39:31.280+05:30")] }] }
                }
            },
            "listing_id": "$listing_id",
            "creator_id": "$creator_id",
            "listing_owner_id": "$listing_owner_id",
            "last_accessed": "$last_accessed"
        }
    },
    {
        $lookup: {
            from: "users",
            localField: "creator_id",
            foreignField: "_id",
            'as': "user_types"
        }
    },
    {
        $group: {
            _id: "$creator_id",
            filteredData: {
                $push: {"user_types":"$user_types","views":"$views"}
            },
        }
    }
])
db.listing_links.aggregate([
    {$match: {
    "views.viewed_on": {
        $gte: ISODate("2019-09-19T20:54:26.210+05:30"),
        $lt: ISODate("2019-09-20T12:47:29.693+05:30")
    }
    }}
])

db.listing_links.aggregate([
    {
        $group : {
            _id:"$creator_id",
        entry: {$push: "$$ROOT"}
    }
  ])
 
  let user = await User.findOneAndUpdate({ email: {$regex : new RegExp(req.body.email, "i")}},{$set:{password:pass}},{ useFindAndModify: false });
 
 
  db.listings.aggregate([
                     { $match: { is_complete_listing: true } },
                     { $group: { _id:"$agent_id", total: { $sum: "$rent" } } },
                     { $sort: { total: 1 } }
                   ])
     




-----------------------------------------------------------------------------------------------------
db.listings.aggregate([{"$lookup":{"from":"users","localField":"agent_id","foreignField":"_id","as":"user_details"}},{"$unwind":"$user_details"},{"$project":{"user_details.password":0,"user_details.__v":0,"user_details.is_onboard":0,"user_details.is_active":0,"user_details.is_open":0,"__v":0}},{"$lookup":{"from":"user_types","localField":"user_details.user_types","foreignField":"_id","as":"user_details.user_types"}},{"$unwind":"$user_details.user_types"},{"$match":{"is_deleted":false,"is_visibility":true,"is_complete_listing":true,"is_archived":false,"$or":[{"user_details.organisation":"Codewalla"},{"is_public":true}],"bedroom_unit":{"$gte":0},"bathroom_unit":{"$gte":0},"rent":{"$gte":0}}},{"$sort":{"created_on":-1}},{"$facet":{"paginatedResults":[{"$skip":0},{"$limit":5}],"totalCount":[{"$count":"count"}]}}]);
-------------------------------------------------------------------------------------------------------------------------

$project example
https://stackoverflow.com/questions/35583569/mongodb-aggregation-with-lookup-limit-some-fields-to-return-from-query

---------------------------------------------------------------------------------------------------------------------

# MongoDB $lookup aggregation

[SO link](http://stackoverflow.com/questions/40350336/export-collection-and-replace-field-with-field-from-another-collection-aggregat)
https://gist.github.com/bertrandmartel/311dbe17c2a57e8a07610724310bf898
```
db.votes.aggregate([{
    $lookup: {
        from: "users",
        localField: "createdBy",
        foreignField: "_id",
        as: "user"
    }
}, {
    $unwind: "$user"
}, {
    $unwind: "$user.emails"
}, {
    $sort: { "user.emails.verified": -1 }
}, {
    $group: {
        _id: "$_id",
        createdBy: { $first: "$createdBy" },
        fellowId: { $first: "$fellowId" },
        companyId: { $first: "$companyId" },
        teamId: { $first: "$teamId" },
        user: { $first: "$user" }
    }
}, {
    $lookup: {
        from: "fellows",
        localField: "fellowId",
        foreignField: "_id",
        as: "fellow"
    }
}, {
    $unwind: "$fellow"
}, {
    $lookup: {
        from: "companies",
        localField: "companyId",
        foreignField: "_id",
        as: "company"
    }
}, {
    $unwind: "$company"
}, {
    $lookup: {
        from: "teams",
        localField: "teamId",
        foreignField: "_id",
        as: "team"
    }
}, {
    $unwind: "$team"
}, {
    $project: {
        "_id": 1,
        "firstName": "$user.profile.name.first",
        "lastName": "$user.profile.name.last",
        "emailAddress": "$user.emails.address",
        "companyTitle": "$company._id",
        "teamTitle": "$team._id",
        "fellowTitle": "$fellow._id",
        "isVerified": "$user.emails.verified"
    }
}, {
    $out: "results"
}])
```

* Output :

```
{ "_id" : "5qgfddRubJ32pS48B22222222", "firstName" : "CCCCCC", "lastName" : "DDDDDD", "emailAddress" : "someuser111@example.com", "companyTitle" : "CCC", "teamTitle" : "FFF", "fellowTitle" : "yCaqt5nT3LQCBLj8j", "isVerified" : false }
{ "_id" : "5qgfddRubJ32pS48B111111", "firstName" : "AAAAAAA", "lastName" : "BBBBBB", "emailAddress" : "someuser1@example.com", "companyTitle" : "BBB", "teamTitle" : "EEE", "fellowTitle" : "yCaqt5nT3LQCBLj8j", "isVerified" : true }
{ "_id" : "5qgfddRubJ32pS48B", "firstName" : "AZAZAZAZ", "lastName" : "SDSDSDSDSD", "emailAddress" : "someuser@example.com", "companyTitle" : "AAA", "teamTitle" : "DDD", "fellowTitle" : "yCaqt5nT3LQCBLj8j", "isVerified" : true }
```


# Sample collections

* votes collection :

```
db.votes.insert([{
    "_id": "5qgfddRubJ32pS48B",
    "createdBy": "HdKRfwzGriMMZgSQu",
    "companyId": "AAA",
    "teamId":"DDD",
    "fellowId": "yCaqt5nT3LQCBLj8j",
},{
    "_id": "5qgfddRubJ32pS48B111111",
    "createdBy": "HdKRfwzGriMMZgqsdqsdSQu",
    "companyId": "BBB",
    "teamId":"EEE",
    "fellowId": "yCaqt5nT3LQCBLj8j",
},{
    "_id": "5qgfddRubJ32pS48B22222222",
    "createdBy": "HdKRfwzGriMMZgqsdqsdSQu1212",
    "companyId": "CCC",
    "teamId":"FFF",
    "fellowId": "yCaqt5nT3LQCBLj8j",
}])
```

* companies collection :

```
db.companies.insert([{
    "_id": "AAA",
    "companyName":"companyA"
}, {
    "_id": "BBB",
    "companyName":"companyB"
}, {
    "_id": "CCC",
    "companyName":"companyC"
}]);
```

* teams collection :

```
db.teams.insert([{
    "_id": "DDD",
    "companyName":"teamD"
}, {
    "_id": "EEE",
    "companyName":"teamE"
}, {
    "_id": "FFF",
    "companyName":"teamF"
}]);
```

* users collection :

```
db.users.insert([{
    "_id": "HdKRfwzGriMMZgSQu",
    "emails": [{
        "address": "someuser@example.com",
        "verified": true
    }, {
        "address": "someuser1@example.com",
        "verified": false
    }],
    "profile":{
        "name":{
            "first":"AZAZAZAZ",
            "last":"SDSDSDSDSD"
        }
    }
},{
    "_id": "HdKRfwzGriMMZgqsdqsdSQu",
    "emails": [{
        "address": "someuser111@example.com",
        "verified": false
    }, {
        "address": "someuser1@example.com",
        "verified": true
    }],
    "profile":{
        "name":{
            "first":"AAAAAAA",
            "last":"BBBBBB"
        }
    }
},{
    "_id": "HdKRfwzGriMMZgqsdqsdSQu1212",
    "emails": [{
        "address": "someuser111@example.com",
        "verified": false
    }, {
        "address": "someuser1@example.com",
        "verified": false
    }],
    "profile":{
        "name":{
            "first":"CCCCCC",
            "last":"DDDDDD"
        }
    }
}])
```

* fellows collection :

```
db.fellows.insert({
    "_id": "yCaqt5nT3LQCBLj8j",
    "title": "Fellow Title"
})
```

------------------------------------------------------------------------------------------------------------
datewise unique
----------------------------
https://stackoverflow.com/questions/33109897/get-distinct-iso-dates-by-days-months-year

db.mycollection.aggregate([
    { "$project": { 
         "year": { "$year": "$date" }, 
         "month": { "$month": "$date" } 
    }},
    { "$group": { 
        "_id": null, 
        "distinctDate": { "$addToSet": { "year": "$year", "month": "$month" }}
    }}
])

------------------------------------------------------------------------------------------------------------------------------------------------

1. How to group query with multiple $cond?
collection.aggregate(
    { 
        $match : { 
            '_id' : {$in:ids}
        } 
    },
    {
        $group: {
            _id: '$someField',
            ...
            count: {$sum: { $cond: [ {$and : [ { $eq: [  "$otherField", false] },
                                               { $eq: [ "$anotherField","value"] }
                                     ] },
                                     1,
                                     0 ] }}
        }
    },
    function(err, result){
        ...
    }
);

2 Nested conditions in $cond aggregate
db.orders.aggregate( [
   { $project: { status: {
      $cond: { if: { $gt: ["$feedback", null] }, 
      then: 'finished', else: {
         $cond: { if: { $gt: ["$received", null] }, 
         then: 'received', else: {
            $cond: { if: { $gt: ["$shipped", null] }, 
            then: 'shipped', else: {
               $cond: { if: { $gt: ["$payment", null] }, 
                  then: 'payment received', else: 'created' }
            } }
         } } 
      } }
   } } },
   { $match: {  } }
] )

3 . $group and $project array to object for counts
db.collection.aggregate([    
    { 
        "$group": { 
            "_id": "$install_date",             
            "android_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$platform", "android" ] }, 1, 0 ]
                }
            },
            "ios_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$platform", "ios" ] }, 1, 0 ]
                }
            },
            "facebook_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$platform", "facebook" ] }, 1, 0 ]
                }
            },
            "kindle_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$platform", "kindle" ] }, 1, 0 ]
                }
            } 
        }  
    },
    {
        "$project": {
            "_id": 0, "install_date": "$_id",            
            "platform": {
                "android": "$android_count",
                "ios": "$ios_count",
                "facebook": "$facebook_count",
                "kindle": "$kindle_count"
            }
        }
    }
])

4 . mongo group and count with condition
db.foo.aggregate([
    {
        $project: {
            item: 1,
            lessThan10: {  // Set to 1 if value < 10
                $cond: [ { $lt: ["$value", 10 ] }, 1, 0]
            },
            moreThan10: {  // Set to 1 if value > 10
                $cond: [ { $gt: [ "$value", 10 ] }, 1, 0]
            }
        }
    },
    {
        $group: {
            _id: "$item",
            countSmaller: { $sum: "$lessThan10" },
            countBigger: { $sum: "$moreThan10" }
        }
    }
])

5. Conditional $sum in MongoDB
db.Sentiments.aggregate(
    { $project: {
        _id: 0,
        Company: 1,
        PosSentiment: {$cond: [{$gt: ['$Sentiment', 0]}, '$Sentiment', 0]},
        NegSentiment: {$cond: [{$lt: ['$Sentiment', 0]}, '$Sentiment', 0]}
    }},
    { $group: {
        _id: "$Company",
        SumPosSentiment: {$sum: '$PosSentiment'},
        SumNegSentiment: {$sum: '$NegSentiment'}
    }});

6. Is there an elseif thing in MongoDB to $cond while aggregating
db.items.aggregate([
    { "$project": {
        "name": 1,
        "customfield": {
            "$cond": { 
                "if": { "$eq": [ "$field1", "4" ] }, 
                "then": 30,
                "else": {
                    "$cond": {
                        "if": { "$eq": ["$field1","8"]}, 
                        "then": 25, 
                        "else": 10
                    }
                }
            }
        }
    }},
   { "$sort": { customfield: 1 }},
   { "$limit":12 }
]);

7. Group By Condition in MongoDB
db.collection.aggregate([
    { "$group": {
        "_id": {
            "year": { "$year": "$utc_timestamp" },
            "month": { "$month": "$utc_timestamp" },
            "day": { "$dayOfMonth": "$utc_timestamp" },
        },
        "defects": {
            "$sum": { "$cond": [
                { "$eq": [ "$status", "defect" ] },
                1,
                0
            ]}
        },
        "totalCount": { "$sum": 1 }
    }},
    { "$project": {
        "defect_rate": {
            "$cond": [
                { "$eq": [ "$defects", 0 ] },
                0,
                { "$divide": [ "$defects", "$totalCount" ] }
            ]
        }
    }}
])

8. Mongodb aggregate $group for multiple date ranges
var today = new Date(),
    oneDay = ( 1000 * 60 * 60 * 24 ),
    thirtyDays = new Date( today.valueOf() - ( 30 * oneDay ) ),
    fifteenDays = new Date( today.valueOf() - ( 15 * oneDay ) ),
    sevenDays = new Date( today.valueOf() - ( 7 * oneDay ) );

db.collection.aggregate([
    { "$match": {
        "date": { "$gte": thirtyDays" }
    }},
    { "$group": {
        "_id": {
            "$cond": [
                { "$lt": [ "$date", fifteenDays ] },
                "16-30",
                { "$cond": [
                    { "$lt": [ "$date", sevenDays ] },
                    "08-15",
                    "01-07"
                ]}
            ]
        },
        "count": { "$sum": 1 },
        "totalValue": { "$sum": "$value" }
    }}
])

9. Insert to all existing document of a collection
db.chatlogs.update({}, {$set: {"isAnswered": false}}, {multi: true});

10. To remove any existing field of document of a collection
db.chatlogs.update({}, {$unset: {"isAnswered": 0}}, false, true);db.collectionname.update({isFinished:false},{$set:{isFinished:true}})
------------------------------------------------------------------------------------------------------------------------------------------