Query multiple queries and combine data into a single array based on a condition in mongodb
I have four collections of mongodb users, accounts, boats, crews; all with different data structures. Here are some examples:
user collection
{
"_id" : ObjectId("58b80a5f922a9656b78e86a7"),
"email" : "bhargavksdn@gmail.com",
"password" : "$2a$10$OVZfqHgKlB9gvIn38/DmL.h4/6n0g4frC8.QcHqHIbMQ0W4Fwv1BK",
"access" : [
{"id" : ObjectId("58b80a62922a9656b78e86a8"),"role" : "boats"},
{"id" : ObjectId("580df78d3543ce1d905ffb11"),"role" : "accounts"},
{"id" : ObjectId("563160bb671f0c130e6f09b5"),"role" : "accounts"},
{"id" : ObjectId("58e35fea31d05f0e95e0c8fd"),"role" : "crews"}
],
"admins" : [ "test1798@mailinator.com", "test179@mailinator.com", "xyz1793@mailinator.com", "xyz189@mailinator.com"]
}
boat collection
{
"_id" : ObjectId("58b80a62922a9656b78e86a8"),
"name" : "brgvk",
"email" : "bhargavksdn@gmail.com",
"path" : "",
"photo" : "",
"accounts" : [ ObjectId("58b80a5f922a9656b78e86a7"), ObjectId("580df78d3543ce1d905ffb11")],
"account_id" : "58b80a5f922a9656b78e86a7"
}
example of a crew collection
{
"_id" : ObjectId("58e35fea31d05f0e95e0c8fd"),
"firstName" : "brgv",
"lastName" : "k",
"phone" : "",
"email" : "bhargavksdn@gmail.com",
"bio" : "",
"photo" : "",
"path" : "",
"boats" : [],
"accounts" : [ ObjectId("563160bb671f0c130e6f09b5"), ObjectId("58b80a5f922a9656b78e86a7")]
}
collecti accounts with id580df78d3543ce1d905ffb11
{
"_id" : ObjectId("580df78d3543ce1d905ffb11"),
"profile" : {
"accountName" : "Nightquest",
"firstName" : "Jhon",
"lastName" : "Smith",
"phone" : "32122333333333",
photo:"123.jpg",
path:"1/23"
"address" : "Melbourne"
},
"settings" : {
color1:"red",
color2:"blue"
}
}
collection of accounts with id563160bb671f0c130e6f09b5
{
"_id" : ObjectId("563160bb671f0c130e6f09b5"),
"profile" : {
"accountName" : "Testing 123",
"firstName" : "Tom",
"lastName" : "Kru",
photo:"123.jpg",
path:"1/23"
"phone" : "04181459",
"address" : "Melbourne"
},
"settings" : {
color1:"green",
color2:"yellow"
}
}
I am currently using the following aggregation pipeline and I can query the data
db.users.aggregate(
// Pipeline
[
{
$unwind: { "path": "$access" }
},
{
$project: { "roleId": "$access.id", "email": "$email" }
},
{
$lookup: {
"from" :"accounts",
"localField": "roleId",
"foreignField": "_id",
"as": "accounts"
}
},
{
$lookup: {
"from" :"crews",
"localField": "roleId",
"foreignField": "_id",
"as": "crews"
}
},
{
$lookup: {
"from" :"boats",
"localField": "roleId",
"foreignField": "_id",
"as": "boats"
}
},
{
$unwind: {
"path": "$boats",
"preserveNullAndEmptyArrays": true
}
},
{
$unwind: {
"path": "$crews",
"preserveNullAndEmptyArrays": true
}
},
{
$unwind: {
"path": "$accounts",
"preserveNullAndEmptyArrays": true
}
},
{
$group: { "_id": "$_id",
"email" : {
"$first": "$email"
},
"accounts": {
"$addToSet" : {
roleId:"$accounts._id",
name:"$accounts.profile.accountName",
"role":{$literal: 'accounts'} ,
path:"$accounts.profile.path",
photo:"$accounts.profile.photo"
}
},
"boats": {
"$addToSet" : {
roleId:"$boats._id",
name:"$boats.name",
"role":{$literal: 'boats'} ,
path:"$boats.path",
photo:"$boats.photo"
}
},
"crews": {
"$addToSet" : {
roleId:"$crews._id",
name:{ "$concat": [ "$crews.firstName", "$crews.lastName" ] }
"role":{$literal: 'crews'} ,
path:"$crews.path",
photo:"$crews.photo"
}
}
},
},
{
"$project":{
"_id": 1,
"email": 1,
"access": { "$setUnion": [ "$accounts", "$boats", "$crews" ] }
}
}
]
)
Although the request works, it inserts empty fields into the accessor properties array
[
{
"roleId" : ObjectId("58e35fea31d05f0e95e0c8fd"),
"name" : "brgv k",
"role" : "crews",
"path" : "",
"photo" : ""
},
{
"role" : "boats"
},
{
"name" : null,
"role" : "crews"
},
{
"role" : "accounts"
},
{
"roleId" : ObjectId("580df78d3543ce1d905ffb11"),
"name" : "Nightquest",
"role" : "accounts"
},
{
"roleId" : ObjectId("58b80a62922a9656b78e86a8"),
"name" : "brgvk",
"role" : "boats",
"path" : "",
"photo" : ""
},
]
I want to delete objects that don't have one associated with them roleId
.
+3
source to share
1 answer
db.users.aggregate (
// Pipeline
[
// Stage 1
{
$match: {
"email": "bhargavksdn@gmail.com"
}
},
// 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
}
},
{
$unwind: {
"path": "$crews",
"preserveNullAndEmptyArrays": true
}
},
{
$unwind: {
"path": "$accounts",
"preserveNullAndEmptyArrays": true
}
},
// Stage 11
// Stage 11
{
$group: { "_id": "$_id",
"email" : {
"$first": "$email"
},
"accounts": {
"$addToSet" : {
roleId:"$accounts._id",
name:"$accounts.profile.accountName",
"role":{"$cond": { "if": { "$eq": [ "$role", "accounts" ] },
"then": "accounts","else": null}},
path:"$accounts.profile.path",
photo:"$accounts.profile.photo"
}
},
"boats": {
"$addToSet" : {
roleId:"$boats._id",
name:"$boats.name",
"role":{"$cond": { "if": { "$eq": [ "$role", "boats" ] },
"then": "boats","else": null}},
path:"$boats.path",
photo:"$boats.photo"
}
},
"crews": {
"$addToSet" : {
roleId:"$crews._id",
name:{ "$concat": [ "$crews.firstName"," ", "$crews.lastName" ] },
"role":{"$cond": { "if": { "$eq": [ "$role", "crews" ] },
"then": "crews","else": null}},
path:"$crews.path",
photo:"$crews.photo"
}
}
},
},
{
"$project":{
"_id": 1,
"email": 1,
"access": { "$setUnion": ["$accounts", "$boats", "$crews" ] }
}
},
{
"$project": {
"_id": 1,
"email": 1,
access: {
$filter: {
input: "$access",
as: "item",
cond: { $ne: [ "$$item.role", null ] }
}
}
}
}
]
);
+1
source to share