I have a JSON document where one array has multiple objects. I want to combine all objects into an array having the same key.
I want to perform such conversion using the MongoDB aggregation pipeline.
Input:
[
{
"_id": "65e6afb50f89ab48c027c54f",
"items": [
{
"A": {
"id": "ci33985107542",
"sku": "2519438"
}
},
{
"A": {
"id": "ci34045151326",
"sku": "2517213"
}
},
{
"B": {
"id": "ci34045151457",
"sku": "2261539"
}
},
{
"B": {
"id": "ci34045151509",
"sku": "2302283"
}
},
{
"B": {
"id": "ci34045151436",
"sku": "2520098"
}
}
]
}
]
Desired output:
[
{
"_id": "65e6afb50f89ab48c027c54f",
"items": [
{
"A": [
{
"id": "ci33985107542",
"sku": "2519438"
},
{
"id": "ci34045151326",
"sku": "2517213"
}
],
"B": [
{
"id": "ci34045151457",
"sku": "2261539"
},
{
"id": "ci34045151509",
"sku": "2302283"
},
{
"id": "ci34045151436",
"sku": "2520098"
}
]
}
]
}
]
I'm quite new to the MongoDB aggregation pipeline. I was going through some documents and looks like it can be done using $group
, but haven't been able to achieve that yet.
Can someone please help?
Side note: A well-designed document structure could save you time and better performance in querying.
Solution 1
$unwind
- Deconstruct the items
array into multiple documents.
$set
- Convert the items
document to an array of documents with k
and v
fields.
$group
- Group by _id
and item.k
and add the first v
document into items
array.
$group
- Group by _id._id
and push the converted to key-value pair document into the items
array.
db.collection.aggregate([
{
$unwind: "$items"
},
{
$set: {
items: {
$objectToArray: "$items"
}
}
},
{
$group: {
_id: {
_id: "$_id",
key: {
$first: "$items.k"
}
},
items: {
$push: {
$first: "$items.v"
}
}
}
},
{
$group: {
_id: "$_id._id",
items: {
$push: {
$arrayToObject: [
[
{
k: "$_id.key",
v: "$items"
}
]
]
}
}
}
}
])
Demo Solution 1@ Mongo Playground
Solution 2
Another solution is slightly complex and does not use the $unwind
and $group
operators.
$set
- Iterate each element and convert it into an array of the documents with k
and v
fields in the items
array.
$set
- Combine nested (dimensional) arrays from the previous stage into a single (level) array.
$set
- Iterating each key from the flattedItems
array ($map
), converting the array into objects ($arrayToObject
) by performing grouping/filtering based on k
fields and retrieving the v
field.
$unset
- Remove the flattedItems
field
db.collection.aggregate([
{
$set: {
flattedItems: {
$map: {
input: "$items",
in: {
$objectToArray: "$$this"
}
}
}
}
},
{
$set: {
flattedItems: {
$reduce: {
input: "$flattedItems",
initialValue: [],
in: {
$concatArrays: [
"$$value",
"$$this"
]
}
}
}
}
},
{
$set: {
items: {
$map: {
input: {
$setUnion: "$flattedItems.k"
},
in: {
$arrayToObject: [
[
{
k: "$$this",
v: {
$map: {
input: {
$filter: {
input: "$flattedItems",
as: "item",
cond: {
$eq: [
"$$this",
"$$item.k"
]
}
}
},
in: "$$this.v"
}
}
}
]
]
}
}
}
}
},
{
$unset: "flattedItems"
}
])