Mongodb Query

query.txt

db.users.aggregate(

	// Pipeline
	[
		// Stage 1
		{
			$match: {
				"email": "[email protected]"
			}
		},

		// Stage 2
		{
			$unwind: { "path": "$access" }
		},

		// Stage 3
		{
			$project: { "roleId": "$access.id", "role": "$access.role", "email": "$email" }
		},

		// Stage 4
		{
			$lookup: {
			        "from" :"accounts",
			        "localField": "roleId",
			        "foreignField": "_id",
			        "as": "accounts"
			}
		},

		// Stage 5
		{
			$lookup: {
			        "from" :"crews",
			        "localField": "roleId",
			        "foreignField": "_id",
			        "as": "crews"
			}
		},

		// Stage 6
		{
			$lookup: {
			        "from" :"boats",
			        "localField": "roleId",
			        "foreignField": "_id",
			        "as": "boats"
			}
		},

		// Stage 7
		{
			$unwind: { 
			  "path": "$boats",
			  "preserveNullAndEmptyArrays": true
			 }
		},

		// Stage 8
		{
			$unwind: { 
			  "path": "$crews",
			  "preserveNullAndEmptyArrays": true
			 }
		},

		// Stage 9
		{
			$unwind: { 
			  "path": "$accounts",
			  "preserveNullAndEmptyArrays": true
			 }
		},

		// Stage 11
		// Stage 11
		{
			$group: { "_id": "$_id", 
			  "role": { 
			    "$addToSet": "$role"
			   }, 
			   "email" : { 
			     "$first": "$email"
			   }, 
			   "accounts": { 
			     "$addToSet" :"$accounts"
			   }, 
			   "boats": {  
			     "$addToSet": "$boats" 
			   },
			   "crews": {  
			     "$addToSet": "$crews" 
			   } 
			}
			
		},{
                    "$project":{
                        "_id": 1,
                         "email": 1,
                        "role":1,
                         "access": { "$setUnion": ["$accounts", "$boats", "$crews" ]  }
                      }
                }
	]
);