combine array fields into a single array field mongo

Question

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()

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() — Anurag Sharma
– Anurag Sharma, Commented Jun 1, 2018 at 8:22

mickl · Accepted Answer · 2018-06-01 09:04:04Z

8

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

edited Jun 1, 2018 at 9:04

answered Jun 1, 2018 at 8:23

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anurag Sharma Over a year ago

Hey, How can I get distinct objectIds in my final "JobSkills" array

mickl Over a year ago

@AnuragSharma just use $setUnion instead of $concatArrays, it simply ignores duplicates

Alex Blex Over a year ago

A nice workaround to jira.mongodb.org/browse/SERVER-31991. You could post it there.

Collectives™ on Stack Overflow

combine array fields into a single array field mongo

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related