mongo dB tutorial link
https://data-flair.training/blogs/mongodb-aggregation-tutorial/
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" }}
}}
])
------------------------------------------------------------------------------------------------------------------------------------------------
collection.aggregate(
{
$match : {
'_id' : {$in:ids}
}
},
{
$group: {
_id: '$someField',
...
count: {$sum: { $cond: [ {$and : [ { $eq: [ "$otherField", false] },
{ $eq: [ "$anotherField","value"] }
] },
1,
0 ] }}
}
},
function(err, result){
...
}
);
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: { } }
] )
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"
}
}
}
])
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" }
}
}
])
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'}
}});
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 }
]);
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" ] }
]
}
}}
])
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}})
