MongoDB Aggregation

lookup.js

export const populateUserActions = (userCuid, period = { from: new Date(), to: null }) => {
  const aggregation = [
    { $match: { 'cuid': userCuid }},
    { $lookup: CompanyLookup },
    { $lookup: FundraisingLookup },
    { $lookup: MissionLookup },
    { $lookup: CreatedExperienceLookup },
    { $lookup: SubscribedExperienceLookup },
    { $project: {
      ...simpleUserProjection,
      ...advancedUserProjection,
      fundraisings: {
        $filter: {
          input: `$${FundraisingLookup.as}`, as: 'item',
          cond: { $and: dateRangeFilterHelper('$$item.endDate', period) }
        }
      },
      missions: {
        $filter: { input: `$${MissionLookup.as}`, as: 'item',
          cond: { $and: [
            { $ne: ['$$item.status', FINISHED] },
            { $ne: ['$$item.status', CANCELLED] },
            { $ne: ['$$item.status', ARCHIVED] },
            { $ne: ['$$item.status', DRAFT] }
          ] }
        }
      },
      createdExperiences: {
        $filter: {
          input: `$${CreatedExperienceLookup.as}`, as: 'item',
          cond: {
            $and: [
              { $ne: ['$$item.status', FINISHED] },
              { $ne: ['$$item.status', CANCELLED] },
              { $ne: ['$$item.status', ARCHIVED] },
              { $ne: ['$$item.status', DRAFT] },
              ...dateRangeFilterHelper('$$item.startDate', period)
            ]
          }
        }
      },
      subscribedExperiences: {
        $filter: {
          input: `$${SubscribedExperienceLookup.as}`, as: 'item',
          cond: {
            $and: [
              { $ne: ['$$item.status', FINISHED] },
              { $ne: ['$$item.status', CANCELLED] },
              { $ne: ['$$item.status', ARCHIVED] },
              { $ne: ['$$item.status', DRAFT] },
              ...dateRangeFilterHelper('$$item.startDate', period)
            ],
          }
        }
      },
    }},
    { $project: {
      ...simpleUserProjection,
      ...advancedUserProjection,
      fundraisings: { cuid: 1, goal: 1, endDate: 1, opportunity: 1 },
      missions: { cuid: 1, opportunity: 1 },
      createdExperiences: { startDate: 1, endDate: 1, status: 1, cuid: 1, opportunity: 1 },
      subscribedExperiences: { startDate: 1, endDate: 1, status: 1, cuid: 1, opportunity: 1 }
    }},
    { $project: {
      ...simpleUserProjection,
      ...advancedUserProjection,
      fundraisings: { cuid: 1, goal: 1, endDate: 1, opportunity: 1 },
      missions: { cuid: 1, opportunity: 1 },
      experiences: { $concatArrays: ['$subscribedExperiences', '$createdExperiences'] }
    }},
    { $limit: 1 }
  ];

  return User.aggregate(aggregation);
};

match.js

/*
  Search through term
*/


export const getCompanies = () => {
  const { user, query: { sort = '-creationDate], search } } = req;
  const match = {};
  
  if (search) match.and = [{ $text: { $search: search, $language: user.language }}];

  Company
    .find(match)
    .sort(sort)
    .exec()
    .then(companies => res.json({ companies}))
}

query.js

// $and, $or, $lookup, $arrayElemAt

export const getUserActions = (userCuid, companyId, period = { from: new Date(), to: null }) => {
  const charityByActivityLookup = {
    from: 'charities',
    let: { charityId: '$charity' },
    pipeline: [
      { $match: { $expr: { $eq: ['$_id', '$$charityId'] } } },
      { $project: { name: 1 } }
    ],
    as: 'charity'
  };

  const activityByOpportunityLookup = {
    from: 'activities',
    let: { activityId: '$activity' },
    pipeline: [
      { $match: { $expr: { $eq: ['$_id', '$$activityId'] } } },
      { $lookup: charityByActivityLookup },
      { $project: {...commonActivityFields, charity: { $arrayElemAt: ['$charity', 0] } } }
    ],
    as: 'activity'
  };

  const companyByOpportunityLookup = {
    from: 'companies',
    let: { companyId: '$company' },
    pipeline: [
      { $match: { $expr: { $eq: ['$_id', '$$companyId'] } } },
      { $project: { globalCurrency: 1 } }
    ],
    as: 'company'
  };

  const opportunityLookup = {
    from: 'opportunities',
    let: { opportunityId: '$opportunity'},
    pipeline: [
      { $match: { $expr: { $eq: ['$_id', '$$opportunityId'] } } },
      { $lookup: companyByOpportunityLookup },
      { $lookup: activityByOpportunityLookup },
      { $project: { cuid: 1, company: { $arrayElemAt: ['$company', 0] }, activity: { $arrayElemAt: ['$activity', 0] } } }
    ],
    as: 'opportunity'
  };

  const fundraising = {
    from: 'fundraisings',
    let: { 'userId': '$_id' },
    pipeline: [
      { $match: { $expr: { $and: [
        { $eq: ['$user', '$$userId'] },
        ...dateRangeFilterHelper('$endDate', period),
      ] } } },
      { $lookup: opportunityLookup },
      { $project: { cuid: 1, goal: 1, endDate: 1, opportunity: { $arrayElemAt: ['$opportunity', 0] } }}
    ],
    as: 'fundraisings'
  };

  const missions = {
    from: 'missions',
    let: { 'userId': '$_id' },
    pipeline: [
      { $match: { $expr: { $and: [
        { $eq: ['$user', '$$userId'] },
        { $ne: ['$status', FINISHED] },
        { $ne: ['$status', CANCELLED] },
        { $ne: ['$status', ARCHIVED] },
        { $ne: ['$status', DRAFT] }
      ] } } },
      { $lookup: opportunityLookup },
      { $project: { opportunity: { $arrayElemAt: ['$opportunity', 0] } } }
    ],
    as: 'missions'
  };

  const experiences = {
    from: 'experiences',
    let: { 'userEmail': '$username', 'userCuid': '$cuid' },
    pipeline: [
      { $match: { $expr: {
        $and: [
          {
            $or: [
              { $eq: ['$organizer.email', '$$userEmail'] },
              { $in: ['$$userCuid', '$participants'] }
            ]
          },
          { $ne: ['$status', FINISHED] },
          { $ne: ['$status', CANCELLED] },
          { $ne: ['$status', ARCHIVED] },
          { $ne: ['$status', DRAFT] },
          ...dateRangeFilterHelper('$startDate', period)
        ]
      } } },
      { $lookup: opportunityLookup },
      { $project: { startDate: 1, endDate: 1, status: 1, organizer: 1, participants: 1, opportunity: { $arrayElemAt: ['$opportunity', 0] } } }
    ],
    as: 'experiences'
  };

  const aggregation = [
    { $match: { 'cuid': userCuid, $or: [{ 'company': companyId }, { 'active': true }] } },
    { $lookup: CompanyLookup },
    { $lookup: fundraising },
    { $lookup: missions },
    { $lookup: experiences },
    { $project: {
      _id: 0, cuid: 1, creationDate: 1, username: 1, roles: 1, active: 1,
      company: OrganizationProjection, fundraisings: 1, missions: 1, experiences: 1,
    } },
    { $limit: 1 }
  ];

  return User.aggregate(aggregation);
};