Need help with building complex aggregation pipeline

Working with Data
, ,
1

Hey,

Maybe someone can help me, I need an aggregation pipeline that I’m struggling to create.
In the application, a user can select which fields from the document he needs will be returned.
What I want to accomplish is to select some fields from “data” property, for ex: “projectName”, “createdAt” fields. And for the companies, I need to select different fields for different companies, for ex: from “Audi” companies document I need “companyName” and “businessAreas” fields, and from “Medium Company” I need “companyName” and “projectType” field.

The document that needs to be aggregated:

{"_id":“rZWT59rFDmr3tnpz2”,“data”:{“createdAt”:“2021/10/22 00:00:00”,“projectName”:“Airport”,“projectNumber”:“air-754154”,“createdBy”:“CEO”,“lastEdited”:“Inter”,“editedBy”:“Today”,“numberTest”:777888999,“selectTest”:{“key”:“strategy”},“tagTest”:[{“key”:“strategy”},{“key”:“marketing”}],“textAreaTest”:“Lorem Ipsum”,“dateTest”:{"$date":“2021-10-19T21:00:00Z”}},“companies”:[{"_id":“hiNCWrkAWEeaqSzcH”,“companyName”:“Audi”,“selectBox”:{“key”:“audi”},“projectType”:{“key”:“industrial”},“companyId”:“794541”,“businessAreas”:[{“key”:“strategy”}]},{"_id":“pDWxsJ8WKYB3mftJb”,“companyName”:“Medium Company”,“businessAreas”:[{“key”:“marketing”},{“key”:“humanResources”}],“companyId”:“89745”,“projectType”:{“key”:“social”}},{"_id":“m6Hze9dxHj4F396fp”,“companyName”:“Small Company”,“mediaCreatedAt”:{"$date":“2021-10-25T21:00:00Z”},“endDay”:{"$date":“2021-10-30T21:00:00Z”},“companyId”:“78421”}],“formTemplateId”:“mLmxGGWYXiRdwgddb”,“updatedAt”:“timestamp”}

A settings object from which need to create an aggregation pipeline is like this:

{“projectId”:“rZWT59rFDmr3tnpz2”,“data”:{“projectName”:1,“createdAt”:1},“companies”:[{"_id":“hiNCWrkAWEeaqSzcH”,“companyName:”:1,“businessAreas”:1},{"_id":“boKaoLL7AfsLFNunK”,“companyName:”:1,“projectType”:1}]}

So I’m guessing the aggregation steps will be somethink like this:

  1. $match document by { _id: projectId };
  2. $project fields from data property
  3. find company by company id and $project fields - the part at which I am strugling.

The return document should look like this:
{
projectId: “rZWT59rFDmr3tnpz2”,
data: {
projectName: “Some project name”,
createdAt: “a timestamp”
},
companies: [{
_id: “hiNCWrkAWEeaqSzcH”,
companyName: “Audi”,
businessAreas: [{
key: ‘some area’
}]
},
{
_id: “pDWxsJ8WKYB3mftJb”,
companyName: “Medium Company”,
projectType: {
“key”: “social”
}
}]
}

Help will be very appreciated :slight_smile:

2

Hello @Tadas_Balsys, welcome to the MongoDB Community forum!

I tried this aggregation builder and hope this helps in building your query. The settings object, I have defined it as criteria.

var criteria = {
   "projectId":"rZWT59rFDmr3tnpz2",
   "data":{
      "projectName":1,
      "createdAt":1
   },
   "companies":[
      {
         "_id":"hiNCWrkAWEeaqSzcH",
         "companyName":1,
         "businessAreas":1
      },
      {
         "_id":"pDWxsJ8WKYB3mftJb",
         "companyName":1,
         "projectType":1
      }
   ]
}

The aggregation pipeline with various stages, and its rather elaborate:

var match = 
{ 
    $match: { _id: criteria.projectId } 
}

var dataProjection = 
{ 
    $project: { 
        data: criteria.data, 
        companies: 1 
    } 
}

var filterCompanies = 
{ 
  $addFields: { 
     companies: {
        $map: {
            input: criteria.companies,
            as: "cr_comp",
            in: {
              $let: {
                 vars: { 
                     doc: {
                         $filter: {
                             input: "$companies",
                             as: "comp",
                             cond: {
                                 $eq: [ "$$comp._id", "$$cr_comp._id" ]
                            }
                         }
                     } 
                 },
                 in: { $arrayElemAt: [ "$$doc", 0 ] }
            }
        }
    }
  }}
}

var unwind = 
{ 
    $unwind: "$companies" 
}

var companiesProjection =
{
    $addFields: { 
        cr_companies: { 
            $reduce: { 
                input: criteria.companies, 
                initialValue: [],
                in: {
                    $cond: [ { $eq: [ "$$this._id", "$companies._id" ] }, 
                             { $concatArrays: [ "$$value",  { $objectToArray: "$$this" } ] },
                             { $concatArrays: [ "$$value", [] ] }
                    ]
                }
            }
        },
        companies: { $objectToArray: "$companies" }
    }
}

var companyMatch = 
{ 
  $addFields: { 
      companies: {
          $map: {
              input: "$cr_companies",
              as: "cr_comp",
              in: {
                $let: {
                   vars: { 
                     doc: {
                         $filter: {
                             input: "$companies",
                             as: "comp",
                             cond: {
                                 $eq: [ "$$comp.k", "$$cr_comp.k" ] 
                             }
                         }
                     } 
                   },
                   in: { $arrayElemAt: [ "$$doc", 0 ] }
                }
              }
          }
      }
  } 
}

var finalProjection = 
{ 
    $addFields: { 
        companies: { $arrayToObject: "$companies" }, 
        cr_companies: "$$REMOVE" 
    }
}

var group = 
{ 
    $group: { 
        _id: "$_id", 
        data: { $first: "$data" }, 
        companies: { $push: "$companies" }
    }
}

var pipeline = [ 
  match, 
  dataProjection, 
  filterCompanies, 
  unwind, 
  companiesProjection, 
  companyMatch, 
  finalProjection, 
  group 
]

Run the aggregation query using the above pipeline (I tried it in mongo shell):

db.collection.aggregate(pipeline)


This is the output I got:

{
        "_id" : "rZWT59rFDmr3tnpz2",
        "data" : {
                "createdAt" : "2021/10/22 00:00:00",
                "projectName" : "Airport"
        },
        "companies" : [
                {
                        "_id" : "hiNCWrkAWEeaqSzcH",
                        "companyName" : "Audi",
                        "businessAreas" : [
                                {
                                        "key" : "strategy"
                                }
                        ]
                },
                {
                        "_id" : "pDWxsJ8WKYB3mftJb",
                        "companyName" : "Medium Company",
                        "projectType" : {
                                "key" : "social"
                        }
                }
        ]
}
2 Likes
3

Hi @Prasad_Saya, thanks for welcoming. And big thanks for the query!! Help me so much :slight_smile:

1 Like