Question 395404 in Mongodb Question posted in
The official documentation can be found here.

MongoDB aggregate merge condition

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"
      }
    ]
  }
]
Tags:

3

Answers


  1. 0

    You can make use of $mergeObjects in place of $merge to create the new collection with the desired documents.
    Please let me know if the below query works for you:

    [
  {
    $match: {
      order_id: 100
    }
  },
  {
    $unwind: "$products"
  },
  {
    $match: {
      "products.name": "Pant"
    }
  },
  {
    $group: {
      _id: {
        order_id: "$order_id",
        productName: "$products.name"
      },
      hasDefects: {
        $push: "$products.hasDefect"
      }
    }
  },
  {
    $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>"
        }
      }
    }
  },
  {
    $project: {
      order_id: "$_id.order_id",
      productName: "$_id.productName",
      hasDefect: "$hasDefect",
      lastModified: {
        $dateToString: {
          date: new Date(),
          timezone: "America/New_York"
        }
      }
    }
  },
  {
    $merge: {
      into: "order",
      whenMatched: [
        {
          $addFields: {
            products: {
              $map: {
                input: "$$ROOT.products",
                as: "product",
                in: {
                  $cond: {
                    if: {
                      $eq: ["$$product.name", "$productName"]
                    },
                    then: {
                      $mergeObjects: [
                        "$$product",
                        { hasDefect: "$hasDefect" }
                      ]
                    },
                    else: "$$product"
                  }
                }
              }
            }
          }
        }
      ],
      whenNotMatched: "insert"
    }
  }
]

    Best Regards
    Aasawari

    Login or Signup to reply.
  2. 0

    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"
    }
  }
])

    Demo @ Mongo Playground

    Login or Signup to reply.
  3. 0

    To update only specific parts of a document, such as a single product within an order, you can use the $merge stage with the whenMatched option set to a pipeline instead of "replace". This allows you to define more complex update logic.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search