MongoDB aggregation - show values from different arrays in one array

Question

I have this type of data

{ 
"_id" : 6444, 
"name" : [
    {
        "name" : "John", 
        "sourcesID" : [
            1, 
            2
        ]
    }, 
    {
        "name" : "Jack", 
        "sourcesID" : [
            3, 
            4 
        ]
    }       
    ],
"address" : [
    {
        "city" : "Chicago", 
        "sourcesID" : [
            3, 
            4 
        ]
    }, 
    {
        "city" : "Boston", 
        "sourcesID" : [
            5, 
            6
        ]
    }       
]

}

I want to aggregate the data so that I will be able to match a certain sourcesID and find all the information types that came from this source.

This is what I am looking to achieve

{"type" : "name", "sourceID" : 1}
{"type" : "name", "sourceID" : 2}
{"type" : "name", "sourceID" : 3}
{"type" : "name", "sourceID" : 4}
{"type" : "address", "sourceID" : 3}
{"type" : "address", "sourceID" : 4}
{"type" : "address", "sourceID" : 5}
{"type" : "address", "sourceID" : 6}

Thanks for your help.

Is it always sourcesID or sometimes sources and sometimes sourcesID or it's totally unpredictable ? — mickl
– mickl, Commented May 14, 2019 at 11:05
Good catch! It's a mistake in my example. All should be sourcesID. Will fix it now. — Hanan Cohen
– Hanan Cohen, Commented May 14, 2019 at 11:10

mickl · Accepted Answer · 2019-05-14 11:11:06Z

1

Assuming that there's always sourceID field you can run $objectToArray to transform be able to read object keys dynamically and then run $unwind three times to get single document per sourceID:

db.collection.aggregate([
    {
        $project: {
            data: {
                $filter: {
                    input: { $objectToArray: "$$ROOT" },
                    cond: {
                        $ne: [ "$$this.k", "_id" ]
                    }
                }
            }
        }
    },
    { $unwind: "$data" },
    { $unwind: "$data.v" },
    { $unwind: "$data.v.sourcesID" },
    {
        $project: {
            _id: 0,
            type: "$data.k",
            sourceID: "$data.v.sourcesID"
        }
    }
])

Mongo Playground

answered May 14, 2019 at 11:11

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

Collectives™ on Stack Overflow

MongoDB aggregation - show values from different arrays in one array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related