adding field value from aggregation to an array

Question

Skill collection:

    "skillID" : "10cff2ae-76e7-455a-b1f2-07c6104d254b",
    "skillname" : "java",
    "languageID" : "84c2613c-81c3-4477-ab38-433aaef74373",
    "languagename" : "English",
    "noofendorsement" : 0

    "skillID" : "10cff2ae-76e7-455a-b1f2-07c6104d254b",
    "skillname" : "जावा",
    "languageID" : "hindi",
    "languagename" : "hindi",
    "noofendorsement" : 0



"skillID" : "39bca1af-bea4-4a24-9f50-33992f8f8a6e",
    "skillname" : "python",
    "languageID" : "84c2613c-81c3-4477-ab38-433aaef74373",
    "languagename" : "English",
    "noofendorsement" : 0

"skillID" : "39bca1af-bea4-4a24-9f50-33992f8f8a6e",
    "skillname" : "अजगर",
    "languageID" : "hindi",
    "languagename" : "hindi",
    "noofendorsement" : 0

Jobs collection:

    "_id" : ObjectId("5bace08ce4b022aa88870ab2"),
    "_class" : "com.citizenchat.model.Jobs",
    "userID" : "ff7f88d9-d0e5-4c10-a7b0-b9fd8b5e89eb",
    "jobID" : "4d0b02b9-c508-42cb-a2c1-417f6b3d422b",
    "jobProfile" : "Legislators",
    "lastDate" : "29-09-2018",
    "jobEndDateMilliSeconds" : NumberLong(1538245799999),
    "jobEndDate" : ISODate("2018-09-29T18:29:59.999Z"),
    "jobstatus" : "Expired",
    "jobType" : "Fulltime",
    "jobDescription" : "Job",
    "postingDate" : "27-09-2018 13:52:12.787",
    "user_Skilllist" : [ 
        "10cff2ae-76e7-455a-b1f2-07c6104d254b"
    ],
    "user_occupationname" : "Legislators",
    "user_occupationid" : "154bddd1-5224-47cf-b3d0-f7dfc3ae9c83",
    "jobPostDateTimeMilliseconds" : NumberLong(1538850599999),

i am using the following aggregation query.

db.Jobs.aggregate([
{"$match":{"jobID":"4d0b02b9-c508-42cb-a2c1-417f6b3d422b"}},
 {
    $lookup:
       {
          from: "Skill",
          let:{"user_Skilllist":"$user_Skilllist"},
           pipeline:[
              {"$match":
                 {"$or":[
                    {"languageID":"hindi","$expr":{"$in":["$skillID","$$user_Skilllist"]}},
                    {"languageID":"84c2613c-81c3-4477-ab38-433aaef74373","$expr":{"$in":["$skillID","$$user_Skilllist"]}}
                         ]

                    }

               },

          ],
          as: "skills"
      }
 },



])

i am getting the following result.But my requirement is 1)i will pass the languageID to get the skills in that language only.If that my prefered langauge is not there for that skill the need to get from default langauge English.But when i pass the language id same skill which is present in different languages i am getting.

2)After getting all the skills from aggregation.Pass all the skill names to an array.

 {
    "_id" : ObjectId("5bace08ce4b022aa88870ab2"),
    "_class" : "com.citizenchat.model.Jobs",
    "userID" : "ff7f88d9-d0e5-4c10-a7b0-b9fd8b5e89eb",
    "jobID" : "4d0b02b9-c508-42cb-a2c1-417f6b3d422b",
    "jobProfile" : "Legislators",
    "lastDate" : "29-09-2018",
    "jobEndDateMilliSeconds" : NumberLong(1538245799999),
    "jobEndDate" : ISODate("2018-09-29T18:29:59.999Z"),
    "jobstatus" : "Expired",
    "jobType" : "Fulltime",
    "jobDescription" : "Job",
    "postingDate" : "27-09-2018 13:52:12.787",
    "user_Skilllist" : [ 
        "10cff2ae-76e7-455a-b1f2-07c6104d254b"
    ],
    "user_occupationname" : "Legislators",
    "user_occupationid" : "154bddd1-5224-47cf-b3d0-f7dfc3ae9c83",
    "jobPostDateTimeMilliseconds" : NumberLong(1538850599999),

    "skills" : [ 
        {
            "_id" : ObjectId("5ba0fbc7e4b03e2c8b8f6519"),
            "_class" : "com.citizenchat.model.Skill",
            "skillID" : "10cff2ae-76e7-455a-b1f2-07c6104d254b",
            "skillname" : "java",
            "languageID" : "84c2613c-81c3-4477-ab38-433aaef74373",
            "languagename" : "English",
            "noofendorsement" : 0
        }, 
        {
            "_id" : ObjectId("5bb47d07fc7ab61be62de768"),
            "skillID" : "10cff2ae-76e7-455a-b1f2-07c6104d254b",
            "skillname" : "जावा",
            "languageID" : "hindi",
            "languagename" : "hindi",
            "noofendorsement" : 0
        }
    ]
}

I want output should be like this

"userID" : "ff7f88d9-d0e5-4c10-a7b0-b9fd8b5e89eb",
    "jobID" : "4d0b02b9-c508-42cb-a2c1-417f6b3d422b",
    "jobProfile" : "Legislators",
    "lastDate" : "29-09-2018",
    "jobEndDateMilliSeconds" : NumberLong(1538245799999),
    "jobEndDate" : ISODate("2018-09-29T18:29:59.999Z"),
    "jobstatus" : "Expired",
    "jobType" : "Fulltime",
    "jobDescription" : "Job",
    "postingDate" : "27-09-2018 13:52:12.787",
    "user_Skilllist" : [ 
        "10cff2ae-76e7-455a-b1f2-07c6104d254b"
    ],
    "user_occupationname" : "Legislators",
    "user_occupationid" : "154bddd1-5224-47cf-b3d0-f7dfc3ae9c83",
    "jobPostDateTimeMilliseconds" : NumberLong(1538850599999),

    "skills" : [ 
        "जावा",
         "अजगर"
    ]
}

You have $or. If you want document that matches specified language just use `{"languageID":"hindi","$expr":{"$in":["$skillID","$$user_Skilllist"]}},` Is that what you're after ? — s7vr, Oct 03 '18 at 18:23
Hindi is my prefered language.with hindi languge skill document is not there means it has to take default language english. — nithin, Oct 03 '18 at 18:26
Can you update your question to include all scenarios ? like different kind of inputs, outputs and etc. — s7vr, Oct 05 '18 at 20:12

dnickless · Answer 1 · 2018-10-04T09:15:56.540

Here is an approach using $facet:

db.Jobs.aggregate([{
    $match: { "jobID": "4d0b02b9-c508-42cb-a2c1-417f6b3d422b" }
}, {
    $lookup: {
        "from": "Skill",
        "let": { "user_Skilllist": "$user_Skilllist" },
        "pipeline": [{
                $match: { "$expr": { "$in": ["$skillID", "$$user_Skilllist"] } }
            }, {
                $facet: {
                    "perfectMatch": [ { $match: { "languageID": "hindi" } } ],
                    "fallBack": [ { $match: { "languageID": "84c2613c-81c3-4477-ab38-433aaef74373" } } ],
                }
            }, {
                $project: {
                    "skillname": { $arrayElemAt: [ { $concatArrays: [ "$perfectMatch.skillname", "$fallBack.skillname" ] }, 0 ] } // take the first proper value
                }
            }
        ],
        "as": "skills"
    }
}, {
    $addFields: { "skills": "$skills.skillname" }
}])

And here is one using $switch - the idea being to map the preferred value to 1, the fall-back to 2 and all the rest to 3 so we can sort:

db.Jobs.aggregate([{
    $match: { "jobID": "4d0b02b9-c508-42cb-a2c1-417f6b3d422b" }
}, {
    $lookup: {
        "from": "Skill",
        "let": { "user_Skilllist": "$user_Skilllist" },
        "pipeline": [{
                $match: { "$expr": { "$in": ["$skillID", "$$user_Skilllist"] } }
            }, {
                $addFields: {
                    "preferredOrder": {
                        $switch: {
                           "branches": [
                              { "case": { $eq: [ "$languageID", "hindi" ] }, "then": 1 },
                              { "case": { $eq: [ "$languageID", "84c2613c-81c3-4477-ab38-433aaef74373" ] }, "then": 2 }
                           ],
                           "default": 3
                        }
                    }
                }
            }, {
                $sort: { "preferredOrder": 1 }
            }
        ],
        "as": "skills"
    }
}, {
    $addFields: {
        "skills": [{
            $arrayElemAt: [ "$skills.skillname", 0 ]
        }]
    }
}])

UPDATE based on your comment:

The $switch based version works with multiple entries in the user_Skilllist array like this:

db.Jobs.aggregate([{
    $match: { "jobID": "4d0b02b9-c508-42cb-a2c1-417f6b3d422b" }
}, {
    $lookup: {
        "from": "Skill",
        "let": { "user_Skilllist": "$user_Skilllist" },
        "pipeline": [{
                $match: { "$expr": { "$in": ["$skillID", "$$user_Skilllist"] } }
            }, {
                $addFields: {
                    "preferredOrder": {
                        $switch: {
                           "branches": [
                              { "case": { $eq: [ "$languageID", "hindi" ] }, "then": 1 },
                              { "case": { $eq: [ "$languageID", "84c2613c-81c3-4477-ab38-433aaef74373" ] }, "then": 2 }
                           ],
                           "default": 3
                        }
                    }
                }
            }, {
                $sort: { "preferredOrder": 1 }
            }, {
                $group: {
                    _id: "$skillID",
                    languages: { $first: "$skillname" }
                }
            }
        ],
        "as": "skills"
    }
}, {
    $addFields: {
        "skills": "$skills.languages"
    }
}])

i am using your facet approach.suppose if two or more skills are there in user_Skilllist then in result skills contains only the last added skill to the skills array.It mean its not appending to the array.It is overriding the array. — nithin, Oct 04 '18 at 08:02
Thanks for your reply.I have updated the skill collection and my result document.please check once.Thank you — nithin, Oct 04 '18 at 08:09
here we are passing jobID in match.But i need to do for all jobs in that collection with their corresponding skills should in result. — nithin, Oct 04 '18 at 08:14
I would suggest you go with @AnthonyWinzlet's solution instead then. It seems to work reliably in the given circumstances. My suggestions only work with one element in the `user_Skilllist` array and I cannot think of an easy fix for that just now. — dnickless, Oct 04 '18 at 08:23
I tested @AnthonyWinzlet's solution again and it doesn't yield correct results in some cases. I think the only one so far that really works is the `$switch` based on. — dnickless, Oct 04 '18 at 09:29
suppose i will pass only one language then all the skills should be in that language.Is it possbile?.If possible please send me the query.Thankyou — nithin, Oct 04 '18 at 11:19

adding field value from aggregation to an array

1 Answers1