How do I do a lookup based on the results of a previous lookup in the pipeline?-CodePudding

I am having a hard time trying to get the lookup from the results of the previous lookup

How can I do a lookup from a previous lookup ?

Schema

Event gift describes a gift that is given for a particular event:

This is the schema for event gift:

const EventGiftSchema = new Schema({
  REF_EventID: { type: Schema.Types.ObjectId, required: true },
});

Event describes the event

Here is the schema for the event

const EventSchema = new Schema({
  name: { type: String, required: true },
  REF_EventTypeID: { type: Schema.Types.ObjectId, required: true },
});

the event type defines what type of event it is here is the schema for the event type:

const EventTypeSchema = new Schema({
  name: { type: String, required: true },
  value: { type: Number, required: true },
});

Data

Here is data for event gift

{
  "_id": "61d1e8bdc742575b38a15536",
  "REF_EventID": "61d1e844c742575b38a15526"
}

Here is data for Event:

{
  "_id": "61d1e844c742575b38a15526" ,
  "name": "Christmas",
  "REF_EventTypeID": "61d1e756c742575b38a15517",
}

Here is data for event type

{
  "_id": "61d1e756c742575b38a15517",
  "name": "Every Year",
  "value": "1",
}

here is what I doing in node js (without the EventType)

  var agg_match = {
    $match: {}, // get everything
  };

 // this does the lookup on event document
  var agg_lookup_event = {
    $lookup: {
      from: 'events',
      localField: 'REF_EventID',
      foreignField: '_id',
      as: 'event',
    },
  };

  // this fills in all the event data
  var agg_unwind_event = {
    $unwind: {
      path: '$event',
      preserveNullAndEmptyArrays: true,
    },
  };

  var agg_sort = {
    $sort: {
      'event.dateBeg': 1,
      'gift.searchableName': 1,
    },
  };

  var agg = [agg_match, agg_lookup_event, agg_unwind_event];
  var myAggregate = EventGifts.aggregate(agg);

  // I am using mongoose-aggregate-paginate-v2 on my schema... but the pipeline should not be affected.

  var ret = await EventGifts.aggregatePaginate(myAggregate);

I have tried adding this:

  // this does the lookup on eventtype document
  var agg_lookup_eventtype = {
    $lookup: {
      from: 'eventtypes',
      localField: 'event.REF_EventTypeID',
      foreignField: '_id',
      as: 'eventtype',
    },
  };

  // this fills in all the eventtype data
  var agg_unwind_eventtype = {
    $unwind: {
      path: '$eventtype',
      preserveNullAndEmptyArrays: true,
    },
  };

then modifying the pipeline

  var agg = [
    agg_match,
    agg_lookup_event,
    agg_unwind_event,
    agg_lookup_eventtype,
    agg_unwind_eventtype,
  ];

which leaves the REF_EventTypeID in the output - which I don't want.

{
  "_id": "61d1e844c742575b38a15526",
  "name": "Christmas",
  "REF_EventTypeID": "61d1e756c742575b38a15517",
}

This is what I want is

{
  "_id": "61d1e8c2c742575b38a15536",
  "event": {
    "_id": "61d1e844c742575b38a15526",
    "name": "Christmas",
    "eventtype": {
      "_id": "61d1e756c742575b38a15517",
      "name": "Every Year",
      "value": "1"
    }
  }
}

ANSWER

I figured it out

[
  {
    '$lookup': {
      'from': 'events', 
      'localField': 'REF_EventID', 
      'foreignField': '_id', 
      'as': 'event'
    }
  }, {
    '$unwind': {
      'path': '$event', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$lookup': {
      'from': 'eventtypes', 
      'localField': 'event.REF_EventTypeID', 
      'foreignField': '_id', 
      'as': 'eventtype'
    }
  }, {
    '$unwind': {
      'path': '$eventtype', 
      'preserveNullAndEmptyArrays': true
    }
  }
]

CodePudding user response：

[
  {
    '$lookup': {
      'from': 'events', 
      'localField': 'REF_EventID', 
      'foreignField': '_id', 
      'as': 'event'
    }
  }, {
    '$unwind': {
      'path': '$event', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$lookup': {
      'from': 'eventtypes', 
      'localField': 'event.REF_EventTypeID', 
      'foreignField': '_id', 
      'as': 'eventtype'
    }
  }, {
    '$unwind': {
      'path': '$eventtype', 
      'preserveNullAndEmptyArrays': true
    }
  }
]

CodePudding user response：

Try this on for size; it is the lookup-within-lookup made possible by the more advanced $lookup API (introduced in 4.0). It avoids doing an $unwind.

var r = [
    { _id:"G0", REF_EventID:"E0"}
];
db.Xgift.insert(r);

var r = [
    { _id:"E0", name:"Christmas", REF_EventTypeID: "T1"},
    { _id:"E1", name:"New Years", REF_EventTypeID: "T1"},
    { _id:"E2", name:"Solstice", REF_EventTypeID: "T2"}
];
db.Xevent.insert(r);

var r = [
    { _id:"T1", name:"Every Year", value:1}
];
db.XeventType.insert(r);

c = db.Xgift.aggregate([
    {$lookup: {"from": "Xevent",
               let: { eid: "$REF_EventID" },
               pipeline: [
                   {$match: {$expr: {$eq: [ "$_id", "$$eid" ]} }},
                   {$lookup: {"from": "XeventType",
                              let: { etid: "$REF_EventTypeID" },
                              pipeline: [
                                  {$match: {$expr: {$eq: [ "$_id", "$$etid" ]} }}
                              ],
                              as: "REF_EventTypeID" // replace orig fld with lookup info            
                             }},
                   // event->evenType is 1:1 so "lift" array[0] out:                                
                   {$addFields: {REF_EventTypeID: {$arrayElemAt: ["$REF_EventTypeID",0]}} }
               ],
               as: "REF_EventID"
              }}
    // we know event is 1:1 so "lift" array[0] out and                                              
    // make it the only single object:                                                              
    ,{$addFields: {REF_EventID: {$arrayElemAt: ["$REF_EventID",0]}} }

]);

{
    "_id" : "G0",
    "REF_EventID" : {
        "_id" : "E0",
        "name" : "Christmas",
        "REF_EventTypeID" : {
            "_id" : "T1",
            "name" : "Every Year",
            "value" : 1
        }
    }
}

This is a "minimal" stage approach that uses overwrite of existing fields to keep the pipeline tight. If you really want the field to be named event for example, then you would have to add additional {$unset: "$oldfieldname"} stages to the $lookup pipeline.