I'm would like to do a data transformation using MongoDB Aggregation Framework. I'm stuck for several hours and can't seem to get my head around it.
Sample input data:
{
a1: "value a1",
a2: "value a2"
},
{
a1: "value a3",
a2: "value a4"
},
{
a1: "value a5",
a2: "value a6"
}
I would like to have the output below.
{
a1: ["value a1", "value a3", "value a5"],
a2: ["value a2", "value a4", "value a6"]
}
I'm assuming you want to run such aggregation dynamically (not knowing key names). In such case you need to start with $objectToArray, then run double $group to get an array for each key and in the last step you need to run $arrayToObject to get back key names as keys:
db.collection.aggregate([
{
$project: {
doc: {
$filter: {
input: { $objectToArray: "$$ROOT" },
cond: { $ne: [ "$$this.k", "_id" ] }
}
}
}
},
{
$unwind: "$doc"
},
{
$group: {
_id: "$doc.k",
values: { $push: "$doc.v" }
}
},
{
$group: {
_id: null,
data: { $push: { k: "$_id", v: "$values" } }
}
},
{
$replaceRoot: {
newRoot: {
$arrayToObject: "$data"
}
}
}
])
EDIT: the problem becomes very simple if you know the names of the keys:
db.collection.aggregate([
{
$group: {
_id: null,
a1: { $push: "$a1" },
a2: { $push: "$a2" }
}
}
])
$filter, here's an example: mongoplayground.net/p/sWIfQ8d3XcD