Mongodb: Aggregation/Pipilines to join two schema's data based on nested documents

Question

Here is my schema:

var mongoose = require('mongoose');

var EventSchema = new mongoose.Schema({
    name: {type:String},
    start_date: {type:Date},
    duration: {type:String},
    event_id:{type: mongoose.Schema.Types.ObjectId}
});
var Project = new mongoose.Schema({
    name: {
        type: String, 
        required: '{PATH} is required!'
    },
    user_id:{
        type: mongoose.Schema.Types.ObjectId,
        required:'{PATH} is required!'
    },
    client: {type: Object},
    no_of_events: {type: String, required: '{PATH} is required!'},
    start_date:{type:Date, required: '{PATH} is required!'},
    end_date:{type:Date, required: '{PATH} is required!'},
    budget:{type: String},
    groom:{type:Object},
    bride:{type:Object},
    events:[EventSchema],
    status:{type:Number,Default:1}, //0=Inactive, 1=Active, 2=Completed
    created_at: { type: Date, default: Date.now },
    updated_at: { type: Date, default: Date.now }
});

module.exports = mongoose.model('Project', Project);

While creating Project, I am adding multiple events. Therefore in 'events' there is array of objects. Here is the sample data:

{
    "_id": {
        "$oid": "5b43582a716d9a4e96345f4a"
    },
    "bride": {
        "city": "Hyderabad",
        "phone": "09876543211",
        "dob": "1993-05-06T18:30:00.000Z",
        "name": "Shriya Bhupal"
    },
    "groom": {
        "city": "Hyderabad",
        "phone": "09876543211",
        "dob": "1993-08-09T18:30:00.000Z",
        "name": "Anindith Reddy"
    },
    "client": {
        "name": "Apoorva Pagar"
    },
    "end_date": {
        "$date": "2018-07-22T18:30:00.000Z"
    },
    "start_date": {
        "$date": "2018-07-10T18:30:00.000Z"
    },
    "no_of_events": "4",
    "user_id": {
        "$oid": "5b126966bcc8072e526346ad"
    },
    "name": "Big Fat Wedding",
    "updated_at": {
        "$date": "2018-07-09T12:42:18.263Z"
    },
    "created_at": {
        "$date": "2018-07-09T12:42:18.263Z"
    },
    "events": [
        {
            "name": "Engagement",
            "event_id": {
                "$oid": "5b3b232914cdec23c19c034c"
            },
            "start_date": {
                "$date": "2018-07-11T18:30:00.000Z"
            },
            "duration": "1 Day",
            "_id": {
                "$oid": "5b43582a716d9a4e96345f4b"
            }
        },
        {
            "name": "Mehndi",
            "event_id": {
                "$oid": "5b3b23b314cdec23c19c034d"
            },
            "start_date": {
                "$date": "2018-07-12T18:30:00.000Z"
            },
            "duration": "1 Day",
            "_id": {
                "$oid": "5b43582a716d9a4e96345f4c"
            }
        },
        {
            "name": "Sangeet",
            "event_id": {
                "$oid": "5b3b232914cdec23c19c034c"
            },
            "start_date": {
                "$date": "2018-07-17T18:30:00.000Z"
            },
            "duration": "2 Days",
            "_id": {
                "$oid": "5b43582a716d9a4e96345f4d"
            }
        }
    ],
    "__v": 0
}

Here the case is, If I add event from the default events, then there is event_id, otherwise there is no event_id. So if I added, default events that are saved in another schema called 'defaultevents'. Now I want to add eventdetail in each event object if event_id exists in data.

    {"events": [
            {
// it should include event_detail:{ details about event from defaultevents}
                "name": "Engagement",
                "event_id": {
                    "$oid": "5b3b232914cdec23c19c034c"
                },
                "start_date": {
                    "$date": "2018-07-11T18:30:00.000Z"
                },
                "duration": "1 Day",
                "_id": {
                    "$oid": "5b43582a716d9a4e96345f4b"
                }
            },
            {
// it should not include event_details as there is no event_id
                "name": "Mehndi",
                "start_date": {
                    "$date": "2018-07-12T18:30:00.000Z"
                },
                "duration": "1 Day",
                "_id": {
                    "$oid": "5b43582a716d9a4e96345f4c"
                }
            }
    ]
    }

DefaultEvents Sameple Data:

{
    "_id": {
        "$oid": "5b3b232914cdec23c19c034c"
    },
    "image": "https://dostbucket.s3.us-east-2.amazonaws.com/events/1530601666360mehndi.svg",
    "description": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.",
    "name": "Sangeet",
    "updated_at": {
        "$date": "2018-07-03T07:18:01.166Z"
    },
    "created_at": {
        "$date": "2018-07-03T07:18:01.166Z"
    },
    "__v": 0
}

mongod version: 3.4.7 (MMAPv1)

Please post the sample collection for `defaultevents` schema and also which version of mongodb you are using/ — Ashh, Jul 18 '18 at 07:23
@AnthonyWinzlet jsut added the sample data for Default Events and mongoose version. Hope that helps — Simerjit Parmar, Jul 18 '18 at 07:39
I think question here is to update the Product Document to have **event_details** field if they have **event_id** in events array? correct? Please update the question accordingly. — Harshal Yeole, Jul 18 '18 at 07:54
I don't want to save event_details in the document but need this join to render the data @Jack — Simerjit Parmar, Jul 18 '18 at 08:00

Ashh · Answer 1 · 2018-07-18T10:11:28.197

1

You can do this with $lookup aggregation

db.colletion.aggregate([
  { "$unwind": "$events" },
  { "$lookup": {
    "from": DefaultEventSchema.collection.name,
    "localField": "events.event_id",
    "foreignField": "_id",
    "as": "events.event_id"
  }},
  { "$unwind": { "path": "$events.event_id", "preserveNullAndEmptyArrays": true } },
  { "$group": {
    "_id": "$_id",
    "events": { "$push": "$events" },
    "bride": { "$first": "$bride" },
    "groom": { "$first": "$groom" },
    "client": { "$first": "$client" },
    "end_date": { "$first": "$end_date" },
    "no_of_events": { "$first": "$no_of_events" },
    "name": { "$first": "$name" },
    "created_at": { "$first": "$created_at" },
    "updated_at": { "$first": "$updated_at" }
  }}
])

edited Jul 18 '18 at 10:11

answered Jul 18 '18 at 07:50

Ashh

44,693
14
105
132

It doesn't return other fields of the schema. Also, it doesn't return anything if there is no event id – Simerjit Parmar Jul 18 '18 at 08:43
Question changed? – Ashh Jul 18 '18 at 08:45
No, It returns: { "_id": "5b3c8e6d6283e24f522adaef", "events": [ [], [], [] ], "otherFields": null } if event_id not exists – Simerjit Parmar Jul 18 '18 at 08:49
Check the updated answer... If your both the localField and foreignField are same then it should work... And also your collection name i.e. (`DefaultEventSchema.collection.name`) should be correct... – Ashh Jul 18 '18 at 09:01
Why you are replacing events.event_id? I want to have data from events objects as well as from another schema as detail – Simerjit Parmar Jul 18 '18 at 09:33
I just made a change please try it again and if not then please show how your data looks like – Ashh Jul 18 '18 at 09:47
This works well but it doesn't return events for which event_id do not exist. Kindly check my question again. I want all events. If event_id exists then with event_detail otherwise events. – Simerjit Parmar Jul 18 '18 at 09:56
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/176238/discussion-between-simerjit-parmar-and-anthony-winzlet). – Simerjit Parmar Jul 18 '18 at 09:59

score 1 · Accepted Answer · answered Jul 18 '18 at 10:07

1

You can use below aggregation in 3.6.

ProjectModel.aggregate([
  {"$unwind":"$events"},
  {"$lookup":{
    "from": "event", //name of the foreign collection not model or schema name
    "localField": "events.event_id",
    "foreignField": "_id",
    "as": "events.eventdetail"
  }},
  {"$unwind":{"path":"$events.eventdetail", "preserveNullAndEmptyArrays":true }}, // Keep non matching events
  {"$group":{
    "_id": "$_id",
    "events": { "$push": "$events" },
    "data": { "$first": "$$ROOT" } // $$ROOT to keep the entire data
  }},
  {"$addFields":{"data.events":"$events", "events":0}}, // Replace the events  with grouped events.
  {"$replaceRoot":{"newRoot":"$data"}}
])

answered Jul 18 '18 at 10:07

s7vr

73,656
11
106
127

Will you please tell me the use of $unwind? I read the documentation too but doesn't understand. @Veeram – Simerjit Parmar Jul 18 '18 at 10:40
1

`$unwind` converts array [] into documents {}. We unwind to convert events array into documents for lookup stage. Again, as lookup returns array we convert the returned array back to documents. – s7vr Jul 18 '18 at 10:43

score 0 · Answer 3 · answered Jul 18 '18 at 07:52

Simerjit, as mongo does not provide $ (positioning operator) to search and update on the array of objects (If we try to do it only first object gets updated read more here), we would need to do it in following way:

var products = Product.find();

products.forEach(product => {
    product.events.forEach(event => {
        if ('event_id' in event) {
            var product = product.events[event];
            product.event.event_details = 'my details';
            product.save(product);
        }
    });
});

You might need to make some tweaks to the code, but I hope it gives you the idea how you can achieve what you are looking for.

I don't want to save the details in the document. I want these details to render the data in the front-end. — Simerjit Parmar, Jul 18 '18 at 08:47

Mongodb: Aggregation/Pipilines to join two schema's data based on nested documents

3 Answers3