CODEWITHSUNDEEP
arraysmongodbmongodb-queryaggregation-framework
Anurag Sharma
Anurag Sharma

Reputation: 5039

combine array fields into a single array field mongo

I am using mongo version 3.4.3 and I have my documents stored in mongo like this - 

{
    "_id" : ObjectId("5ad5ab8aaf2808b739ba6ab2"),
    "ResumeId" : "105839064",
    "ResumeDetails" : {

            "WorkProfile" : [
                    {
                            "Company" : "XXXXXXXXX",
                            "JobTitle" : "YYYYY",
                            "JobSkills" : {
                                "CommonSkills": [],
                                "OtherSkills": []
                            }
                    },
                    {
                            "Company" : "XXXXXXXX",
                            "JobTitle" : "YYYYYY",
                            "JobSkills" : {
                                    "CommonSkills" : [
                                            ObjectId("5ad5ab860b94c96c738e914a")
                                    ],
                                    "OtherSkills" : [
                                            ObjectId("5ad5ab860b94c96c738e9146")
                                    ]
                            }
                    },
                    {
                            "Company" : "XXXXXXX",
                            "JobTitle" : "YYYY"
                    }
            ],
            "AdditionalSkills" : {
                    "CommonSkills" : [
                            ObjectId("5ad5ab860b94c96c738e9175"),
                            ObjectId("5ad5ab860b94c96c738e91f0"),
                            ObjectId("5ad5ab860b94c96c738e9241"),
                            ObjectId("5ad5ab860b94c96c738e919b")
                    ],
                    "OtherSkills" : [
                            ObjectId("5ad5ab860b94c96c738e90e6"),
                            ObjectId("5ad5ab860b94c96c738e9142"),
                            ObjectId("5ad5ab860b94c96c738e9211"),
                            ObjectId("5ad5ab860b94c96c738e9293"),
                            ObjectId("5ad5ab860b94c96c738e92c8")
                    ]
            }
    },
    "DocId" : "51cb2f49-fcb9-46a0-9040-67e0f986be11"

}

I want to combine all the skills under WorkProfile and AdditionalSkills under 2 separate arrays. I tried the following query

db.ResumeParsedData.aggregate([
{$match: {'DocId': '51cb2f49-fcb9-46a0-9040-67e0f986be11'}},
{$project: {
        'JobSkills': {'$concatArrays': [
            '$ResumeDetails.WorkProfile.JobSkills.CommonSkills', '$ResumeDetails.WorkProfile.JobSkills.OtherSkills']
        },
        'AdditionalSkills': {'$setUnion': [
            '$ResumeDetails.AdditionalSkills.CommonSkills', '$ResumeDetails.AdditionalSkills.OtherSkills']},
    }
}]).pretty()

But I am getting the following output - 

{
    "_id" : ObjectId("5ad5ab8aaf2808b739ba6ab2"),
    "JobSkills" : [
            [
                    ObjectId("5ad5ab860b94c96c738e914a")
            ],
            [
                    ObjectId("5ad5ab860b94c96c738e9146")
            ]
    ],
    "AdditionalSkills" : [
            ObjectId("5ad5ab860b94c96c738e90e6"),
            ObjectId("5ad5ab860b94c96c738e9142"),
            ObjectId("5ad5ab860b94c96c738e9175"),
            ObjectId("5ad5ab860b94c96c738e919b"),
            ObjectId("5ad5ab860b94c96c738e91f0"),
            ObjectId("5ad5ab860b94c96c738e9211"),
            ObjectId("5ad5ab860b94c96c738e9241"),
            ObjectId("5ad5ab860b94c96c738e9293"),
            ObjectId("5ad5ab860b94c96c738e92c8")
    ]

}

How can I fix the JobSkills array field. It currently coming as array of array fields.

I also tried to concatArrays twice as following:

db.ResumeParsedData.aggregate([ 
    {$match: {'DocId': '51cb2f49-fcb9-46a0-9040-67e0f986be11'}}, 
    {$project: { 
        'JobSkills': {'$concatArrays': { '$concatArrays': [ 
            '$ResumeDetails.WorkProfile.JobSkills.CommonSkills', 
            '$ResumeDetails.WorkProfile.JobSkills.OtherSkills'
        ]}}, 
        'AdditionalSkills': {'$setUnion': [ 
            '$ResumeDetails.AdditionalSkills.CommonSkills', 
            '$ResumeDetails.AdditionalSkills.OtherSkills'
        ]}, 
    } }
]).pretty()

Upvotes: 4

Views: 3093

Answers (1)

mickl
mickl

Reputation: 49945

You can use $reduce (which is available in 3.4) to flatten your array of arrays:

db.ResumeParsedData.aggregate([
    { $match: {"DocId": "51cb2f49-fcb9-46a0-9040-67e0f986be11"} },
    {
        $project: {
            "JobSkills": {
                $reduce: {
                    input: {
                            $concatArrays: ["$ResumeDetails.WorkProfile.JobSkills.CommonSkills", "$ResumeDetails.WorkProfile.JobSkills.OtherSkills"]
                        },
                    initialValue: [],
                    in: { $setUnion: [ "$$this", "$$value" ] }
                }
            },
            "AdditionalSkills": {"$setUnion": [
                "$ResumeDetails.AdditionalSkills.CommonSkills", "$ResumeDetails.AdditionalSkills.OtherSkills"]}
        }
    }
])

$setUnion guarantees that there will be no duplicates in final array

Upvotes: 8

Related Questions