CODEWITHSUNDEEP
mongodbprojectpipelinelookupaggregation
boulepick
boulepick

Reputation: 79

Mongodb aggregation pipeline project

Good day all,

i need some help in getting an aggregation pipeline to work, been trying to get it working for some time but getting only half way. this is my setup

Users document is as follow:

{
    "_id": { "$oid": "5fea4f976ca46d93c010d33d" },
    .....................
    "firstname": "firstname1",
    "lastname": "lastname1",
    "key1": "value1",
    "key2": "value2",
    "key3": "value3",
    "key4": "value4",
    ..................
    "certifications": {
        certs: [
            {
                "_id": "5fea44b96ca46d93c010d330",
                "itemID": 1,
                "date_certified": "2011-05-30 05:09:48"
            },
            {
                "_id": "5fea44b96ca46d93c010d334",
                "itemID": 3,
                "date_certified": "2007-01-30 06:01:51"
            },
            {
                "_id": "5fea44b96ca46d93c010d337",
                "itemID": 5,
                "date_certified": "2007-09-21 16:52:52"
            }
        ]
    },
    .......................
    ...................
    .....................
},

{
    "_id": { "$oid": "5fea4f976ca46d93c010545453" },
    .....................
    .....................
    "firstname": "firstname2",
    "lastname": "lastname2",
    "key1": "value1",
    "key2": "value2",
    "key3": "value3",
    "key4": "value4",
    ..................
    "certifications": {
        certs: [
            {
                "_id": "5fea44b96ca46d93c010d330",
                "itemID": 2,
                "date_certified": "2011-05-30 05:09:48"
            },
            {
                "_id": "5fea44b96ca46d93c010d334",
                "itemID": 4,
                "date_certified": "2007-01-30 06:01:51"
            }
        ]
    },
    .......................
    ...................
    .....................
}

Certifications document is a follow

[{
  "_id": {
    "$oid": "5fea44b96ca46d93c010d32e"
  },
  "itemID": 1,
  "acronym": "acro1",
  "country": "my Great Country",
  "name": {
    "key1": "My Key 1 description",
    "key2": "My Key 2 description",
    "key3": "My Key 3 description",
    "key4": "My Key 4 description"
  },
  "website": "www.myGreatWebsite.com"
},

{
  "_id": {
    "$oid": "5fea44b96ca46d93c010d32f"
  },
  "itemID": 2,
  "acronym": "acro2",
  "country": "my Great Country",
  "name": {
    "key1": "My Key 1 description",
    "key2": "My Key 2 description",
    "key3": "My Key 3 description",
    "key4": "My Key 4 description"
  },
  "website": "www.myGreatWebsite.com"
},

{
  "_id": {
    "$oid": "5fea44b96ca46d93c010d330"
  },
  "itemID": 3,
  "country": "my Great Country",
  "name": {
    "key1": "My Key 1 description",
    "key2": "My Key 2 description",
    "key3": "My Key 3 description",
    "key4": "My Key 4 description"
  },
  "website": "www.myGreatWebsite.com"
},

{
  "_id": {
    "$oid": "5fea44b96ca46d93c010d331"
  },
  "itemID": 4,
  "country": "my Great Country",
  "name": {
    "key1": "My Key 1 description",
    "key2": "My Key 2 description",
    "key3": "My Key 3 description",
    "key4": "My Key 4 description"
  },
  "website": "www.myGreatWebsite.com"
},

{
  "_id": {
    "$oid": "5fea44b96ca46d93c010d332"
  },
  "itemID": 5,
  "country": "my Great Country",
  "name": {
    "key1": "My Key 1 description",
    "key2": "My Key 2 description",
    "key3": "My Key 3 description",
    "key4": "My Key 4 description"
  },
  "website": "www.myGreatWebsite.com"
}]

now my aggregation

db.users.aggregate([
  {
    '$lookup': {
      'from': 'certifications', 
      'let': {
        'certs': '$certifications.certs'
      }, 
      'pipeline': [
        {
          '$match': {
            '$expr': {
              '$in': [
                '$itemID', '$$certs.itemID'
              ]
            }
          }
        }, {
          '$project': {
            '_id': 0, 
            'itemID': '$itemID', 
            'description': '$name.key1', 
            'acronyme': '$acronym', 
            'country': '$country', 
            'count': {
              '$size': '$$certs'
            }, 
            'date_cert': '$$certs.date_certified'
          }
        }
      ], 
      'as': 'certifications'
    }
  }
])

and the result is

{
  .........
  ...........
  "firstname": "firstname1",
  "lastname": "lastname1",
  "key1": "value1",
  "key2": "value2",
  "key3": "value3",
  "key4": "value4",
  ..................
  certification: {
    certs: [
      {
        itemID: 1,
        country: 'my Great Country',
        description: 'My Key 1 description',
        count: 1,
        date_cert: [
          0:"2005-01-06 10:48:16"
        ]
      },
    ]
  }
  ........
  .........
},

{
  .........
  ...........
  "firstname": "firstname2",
  "lastname": "lastname2",
  "key1": "value1",
  "key2": "value2",
  "key3": "value3",
  "key4": "value4",
  ..................
  certification: {
    certs: [
      {
        itemID: 2,
        country: 'my Great Country',
        description: 'My Key 2 description',
        count: 2,
        date_cert: [
          "2005-01-06 10:48:16"
          "2014-06-21 22:44:56"
        ]
      },
      {
        itemID: 4,
        country: 'my Great Country',
        description: 'My Key 4 description',
        count: 2,
        date_cert: [
          "2005-01-06 10:48:16"
          "2014-06-21 22:44:56"
        ]
      }
    ]
  }
  ........
  .........
}

but the expected result is

{
  .........
  ...........
  "firstname": "firstname1",
  "lastname": "lastname1",
  "key1": "value1",
  "key2": "value2",
  "key3": "value3",
  "key4": "value4",
  ..................
  certification: {
    certs: [
      {
        itemID: 1,
        country: 'my Great Country',
        description: 'My Key 1 description',
        date_cert: "2005-01-06 10:48:16"
      },
    ],
    count: 1,
  }
  ........
  .........
},

{
  .........
  ...........
  "firstname": "firstname2",
  "lastname": "lastname2",
  "key1": "value1",
  "key2": "value2",
  "key3": "value3",
  "key4": "value4",
  ..................
  certification: {
    certs: [
      {
        itemID: 2,
        country: 'my Great Country',
        description: 'My Key 2 description',
        date_cert: "2005-01-06 10:48:16"
      },
      {
        itemID: 4,
        country: 'my Great Country'
        description: 'My Key 4 description',
        date_cert: "2014-06-21 22:44:56"
      }
    ],
    count: 2
  }
  ........
  .........
}

basically i'm looking to merge the result of the pipeline match stage with the documents of the certifications lookup, so that i can use both. any pointers would be greatly appreciated in formulation the aggregation.

Thank you

Upvotes: 1

Views: 72

Answers (1)

turivishal
turivishal

Reputation: 36094

  • $unwind deconstruct certs array
  • $lookup with certifications pass certifications.certs.itemID as localField and itemID as foreignField and result as certification
  • $unwind deconstruct result from lookup and in certification
  • $group by _id and reconstruct certs array and push your required fields after merging using $mergeObjects, get count using $sum
db.users.aggregate([
  { $unwind: "$certifications.certs" },
  {
    $lookup: {
      from: "certifications",
      localField: "certifications.certs.itemID",
      foreignField: "itemID",
      as: "certification"
    }
  },
  { $unwind: "$certification" },
  {
    $group: {
      _id: "$_id",
      certs: {
        $push: {
          $mergeObjects: [
            "$certifications.certs",
            {
              country: "$certification.country",
              description: "$certification.name.key1"
            }
          ]
        }
      },
      count: { $sum: 1 }
    }
  }
])

Playground

Second approach without $unwind and $group, this may cause performance and speed issues,

  • $lookup with certifications collection
  • $map to iterate loop of certifications.certs array
  • $reduce to iterate loop of certification, check condition of itemID and it will return specific field,
  • back to $map, merge current object and return object from $reduce using $mergeObjects
  • get count of certs using $size`
db.users.aggregate([
  {
    $lookup: {
      from: "certifications",
      localField: "certifications.certs.itemID",
      foreignField: "itemID",
      as: "certification"
    }
  },
  {
    $addFields: {
      "certification": "$$REMOVE",
      "certifications.certs": {
        $map: {
          input: "$certifications.certs",
          as: "c",
          in: {
            $mergeObjects: [
              "$$c",
              {
                $reduce: {
                  input: "$certification",
                  initialValue: {},
                  in: {
                    $cond: [
                      { $eq: ["$$this.itemID", "$$c.itemID"] },
                      {
                        country: "$$this.country",
                        description: "$$this.name.key1"
                      },
                      "$$value"
                    ]
                  }
                }
              }
            ]
          }
        }
      },
      "certifications.count": { $size: "$certifications.certs" }
    }
  }
])

Playground

Upvotes: 1

Related Questions