I have this below collection and I use MongoDB aggregation to acheive an output(where I aggregate all order line items for an order) and trying to merge into another collection(order).
[
{
"order_line_item_id": 1,
"order_id": 100,
"products": [
{
"name": "Shoe",
"hasDefect": "YES"
},
{
"name": "Pant",
"hasDefect": "NO"
},
{
"name": "Shirt",
"hasDefect": "NOT_SURE"
}
]
},
{
"order_line_item_id": 2,
"order_id": 100,
"products": [
{
"name": "Shoe",
"hasDefect": "YES"
},
{
"name": "Pant",
"hasDefect": "YES"
},
{
"name": "Shirt",
"hasDefect": "YES"
}
]
},
{
"order_line_item_id": 3,
"order_id": 101,
"products": [
{
"name": "Shoe",
"hasDefect": "YES"
},
{
"name": "Pant",
"hasDefect": "NO"
},
{
"name": "Shirt",
"hasDefect": "NOT_SURE"
}
]
}
]
I use the below aggregation to achieve the output
db.collection.aggregate([
{
$match: {
order_id: {
$eq: 100
}
}
},
{
$unwind: "$products"
},
{
$match: {
"products.name": {
$eq: "Pant"
}
}
},
{
$group: {
_id: {
order_id: "$order_id",
productName: "$products.name"
},
hasDefects: {
$push: "$products.hasDefect"
},
products: {
$push: "$products"
}
}
},
{
$set: {
hasDefect: {
$switch: {
branches: [
{
case: {
$allElementsTrue: {
$map: {
input: "$hasDefects",
as: "d",
in: {
$eq: [
"$$d",
"YES"
]
}
}
}
},
then: "YES"
},
{
case: {
$in: [
"NO",
"$hasDefects"
]
},
then: "NO"
},
{
case: {
$in: [
"NOT_SURE",
"$hasDefects"
]
},
then: "NOT_SURE"
}
],
default: "<DEFAULT>"
}
}
}
},
{
$group: {
_id: "$_id.order_id",
order_id: {
"$first": "$_id.order_id"
},
products: {
$push: {
name: "$_id.productName",
hasDefect: "$hasDefect",
lastModified: {
$dateToString: {
date: new Date(),
timezone: "America/New_York"
}
}
}
}
}
},
{
$unset: [
"_id"
]
},
{
$merge: {
into: "order_res",
on: [ "order_id" ],
whenMatched: "replace",
whenNotMatched: "insert"
}
}
])
The output can be a new document altogether or an update for one of the products in the order document (like below).
[
{
"order_id": 100,
"products": [
{
"name": "Shoe",
"hasDefect": "YES"
},
]
}
]
I m using the merge aggregation stage to insert/update to another mongoDB collection.
{ $merge: { into: "order", on: "order_id", whenMatched: "replace", whenNotMatched: "insert" }}
The issue is it is an update operation and the order has previous products it replace all existing product with this new one. For example if the order has the below document, it should update only "Shoe" product.
[
{
"order_id": 100,
"products": [
{
"name": "Shoe",
"hasDefect": "NO"
},
{
"name": "Pant",
"hasDefect": "NO"
},
{
"name": "Shirt",
"hasDefect": "NOT_SURE"
}
]
}
]
Referring to the query from my previous answer,
You can provide the aggregation pipeline to whenMatched.
An important key is to refer to the (result) field returned from the aggregation pipeline, you need to use the $$new variable.
db.collection.aggregate([
{
$unwind: "$products"
},
{
$group: {
_id: {
order_id: "$order_id",
productName: "$products.name"
},
hasDefects: {
$push: "$products.hasDefect"
},
products: {
$push: "$products"
}
}
},
{
$set: {
hasDefect: {
$switch: {
branches: [
{
case: {
$allElementsTrue: {
$map: {
input: "$hasDefects",
as: "d",
in: {
$eq: [
"$$d",
"YES"
]
}
}
}
},
then: "YES"
},
{
case: {
$in: [
"NO",
"$hasDefects"
]
},
then: "NO"
},
{
case: {
$in: [
"NOT_SURE",
"$hasDefects"
]
},
then: "NOT_SURE"
}
],
default: "<DEFAULT>"
}
}
}
},
{
$group: {
_id: "$_id.order_id",
products: {
$push: {
name: "$_id.productName",
hasDefect: "$hasDefect"
}
}
}
},
{
$merge: {
into: "order",
whenMatched: [
{
$addFields: {
products: {
$let: {
vars: {
newProducts: "$$new.products"
},
in: {
$concatArrays: [
{
$map: {
input: "$products",
as: "oldProduct",
in: {
$cond: {
if: {
$in: [
"$$oldProduct.name",
"$$newProducts.name"
]
},
then: {
$first: {
$filter: {
input: "$$newProducts",
cond: {
$eq: [
"$$this.name",
"$$oldProduct.name"
]
}
}
}
},
else: "$$oldProduct"
}
}
}
},
{
$filter: {
input: "$$newProducts",
cond: {
$not: {
$in: [
"$$this.name",
"$products.name"
]
}
}
}
}
]
}
}
}
}
}
],
whenNotMatched: "insert"
}
}
])