what is the pipeline to get the phone and email from two joined documents?

Question

I am trying to consolidate and email and phone number into a single document returned for a user that is obtained from two other documents. I have a pipleline that 'almost' works... but it's not quite right.

Here are my schemas:

User

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const UserSchema = new Schema({
  fName: { type: String, required: true },
  lName: { type: String, required: true },

});

module.exports = User = mongoose.model('users', UserSchema);

some user data:

{
  "_id":"65d7e9a82bd4a4e423570962",
  "fName": "Mike",
  "lName": "Liss",

}

User Contact Infos schema

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

// create the schema
const UserContactInfoSchema = new Schema({
  REF_UserID: { type: Schema.Types.ObjectId, required: true, ref: 'users' },
  REF_ContactInfoID: { type: Schema.Types.ObjectId, required: true, ref: 'contactinfos' },
});

module.exports = UserContactInfo = mongoose.model('usercontactinfos', UserContactInfoSchema);

usercontactinfos data:

{
  "_id": "65d7e9a82bd4a4e423570965",
  "REF_UserID": "65d7e9a82bd4a4e423570962",
  "REF_ContactInfoID":"65d7e9a72bd4a4e423570959"
},
{
  "_id":"65d7e9a92bd4a4e42357096a",
  "REF_UserID":"65d7e9a82bd4a4e423570962",
  "REF_ContactInfoID":"65d7e9a82bd4a4e42357095e"
}

Contact Infos schema

const mongoose = require('mongoose');
const Schema = mongoose.Schema;


// create the schema
const ContactInfoSchema = new Schema({
  value: { type: String, required: true },// either phone number or email
  type: { type: Number, required: true },
});

module.exports = ContactInfo = mongoose.model('contactinfos', ContactInfoSchema);

contactinfos data:

{
  "_id": "65d7e9a72bd4a4e423570959",
  "value": "[email protected]",
  "type": 0
},
{
  "_id":"65d7e9a82bd4a4e42357095e",
  "value": "5102071234",
  "type": 1
}

Here is my pipeline:

[
  {
    $lookup: {
      from: "usercontactinfos",
      localField: "_id",
      foreignField: "REF_UserID",
      as: "uci",
    },
  },
  {
    $unwind: {
      path: "$uci",
      preserveNullAndEmptyArrays: true,
    },
  },
  {
    $lookup: {
      from: "contactinfos",
      localField: "uci.REF_ContactInfoID",
      foreignField: "_id",
      as: "ci",
    },
  },
  {
    $unwind: {
      path: "$ci",
      preserveNullAndEmptyArrays: true,
    },
  },
  {
    $project: {
      _id: 1,
      fName: 1,
      lName: 1,
      email: {
        $cond: {
          if: {
            $eq: ["$ci.type", 0],
          },
          then: "$ci.value",
          else: null,
        },
      },
      phone: {
        $cond: {
          if: {
            $eq: ["$ci.type", 1],
          },
          then: "$ci.value",
          else: null,
        },
      },
    },
  },
  {
    $group: {
      _id: "$_id",
      fName: {
        $first: "$fName",
      },
      lName: {
        $first: "$lName",
      },
      email: {
        $first: "$email",
      },
      phone: {
        $first: "$phone",
      },
    },
  },
]

what I am getting:

{
  "_id": "65d7e9a82bd4a4e423570962",
  "fName": "Mike",
  "lName": "Liss",
  "email": "[email protected]",
  "phone": "[email protected]"
}

what I am trying to achieve:

{
  "_id": "65d7e9a82bd4a4e423570962",
  "fName": "Mike",
  "lName": "Liss",
  "email": "[email protected]",
  "phone": "510-207-1234"
}

Answer 1

There is a small mistake in your $project. You seem to be checking type == 0 for both email and phone. After that to avoid the null when taking $first you can use $max instead so that it will take the maximum value of that field instead of the first

  {
    $project: {
      _id: 1,
      fName: 1,
      lName: 1,
      email: {
        $cond: {
          if: { $eq: [ "$ci.type", 0 ] },
          then: "$ci.value",
          else: null
        }
      },
      phone: {
        $cond: {
          if: { $eq: [ "$ci.type", 1 ] },
          then: "$ci.value",
          else: null
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      fName: { $first: "$fName" },
      lName: { $first: "$lName" },
      email: { $max: "$email" },
      phone: { $max: "$phone" }
    }
  }

playground