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

Mongodb – How to flatten an object in mongo db and rename fields conditionally while saving in root?

I have a collection which contains balance object that needs to be flattened and the fields within that object need to renamed based on another flag in collection –

[
  {
    "id": "1234",
    "documentType": "IV",
    "balance": {
      "total": 100,
      "openBalance": 60,
      "paid": 40
    }
  },  
  {
    "id": "9012",
    "documentType": "CM",
    "balance": {
      "total": 50,
      "applied": 0,
      "available": 50
    }
  }
]

if documentType === "IV", then "balance.paid" would become "totalAmountPaid" in $$ROOT, whereas if it is "CM", then "balance.applied" would be renamed as appliedAmount and "balance.available" as availableAmount, so the final collection after flattening the balance object becomes –

[
  {
    "id": "1234",
    "documentType": "IV",
    "total": 100,
    "openBalance": 60,
    "totalAmountPaid": 40
  },  
  {
    "id": "9012",
    "documentType": "CM",
    "total": 50,
    "appliedAmount": 0,
    "availableAmount": 50
  }
]

I tried using $set and $cond like this but it doesn’t work and I am not very familiar with MongoDB commands –

db.collection.aggregate([
  {
    $set: {
      $cond: {
        if: {
          $eq: [
            "$documentType",
            "IV"
          ]
        },
        then: {
          "total": "$balance.total",
          "openBalance": "$balance.openBalance",
          "totalAmountPaid": "$balance.paid",
          "balance": "$$REMOVE"
        },
        else: {
          "$total": "$balance.total",
          "$availableAmount": "$balance.available",
          "$appliedAmount": "$balance.applied",
          "balance": "$$REMOVE"
        }
      }
    }
  }
])
Tags:

2

Answers


  1. 0

    The issue you’re facing in $set is because you’re trying to create multiple fields (the new object) within one, and you haven’t provided the name.

    Since you’re creating multiple new fields it would be easier to first create them as a sub-object within one field and then replace the original doc with that sub-object.

    (Also, in your else clause, you’re using $total as the field name instead of just total.)

    Changes needed:

    1. In the first part of your query, give the name of the field newdoc and that expression contains your previous code.
      • and you don’t need to have "balance": "$$REMOVE" since it will be replaced with the newdoc anyway
    2. Then merge the main doc’s id and documentType field with the newly created doc, since you’ve got additional fields in the main doc.
    3. And $unset the unneeded balance field and the child newdoc field.
    db.collection.aggregate([
  {
    $set: {
      newdoc: {
        $cond: {
          if: { $eq: ["$documentType", "IV"] },
          then: {
            "total": "$balance.total",
            "openBalance": "$balance.openBalance",
            "totalAmountPaid": "$balance.paid"
          },
          else: {
            "total": "$balance.total",
            "availableAmount": "$balance.available",
            "appliedAmount": "$balance.applied"
          }
        }
      }
    }
  },
  { $replaceWith: { $mergeObjects: ["$$ROOT", "$newdoc"] } },
  { $unset: ["balance", "newdoc"] }
])

    Mongo Playground

    (Previous Mongo Playground)

    Login or Signup to reply.
  2. 0

    My previous answer provides a solution with the fewest changes needed to your original aggregation pipeline.

    But if you had many document types (not just two), each with their own resulting "newdoc" with different fields, then they would need to be written as nested $cond‘s within the else clauses. That would become unreadable and hard to maintain.

    Example data:

    [
  { "id": "1234", "documentType": "IV", "balance": { "total": 100, "openBalance": 60, "paid": 40 } },
  { "id": "9012", "documentType": "CM", "balance": { "total": 50, "applied": 0, "available": 50 } },
  { "id": "5678", "documentType": "XYZ", "balance": { "total": 50, "applied": 0, "deducted": 50 } },
  { "id": "8765", "documentType": "QWERTY", "balance": { "total": 50, "applied": 0, "available": 50 } }
]

    For such cases, using $switch would be better/easier/cleaner.

    Here, the $switch replaces $cond and the if-else parts each become case-then‘s with an optional final default:

    db.collection.aggregate([
  {
    $set: {
      newdoc: {
        $switch: {
          branches: [
            {
              case: { $eq: ["$documentType", "IV"] },
              then: {
                "total": "$balance.total",
                "openBalance": "$balance.openBalance",
                "totalAmountPaid": "$balance.paid"
              }
            },
            {
              case: { $eq: ["$documentType", "CM"] },
              then: {
                "total": "$balance.total",
                "availableAmount": "$balance.available",
                "appliedAmount": "$balance.applied"
              }
            },
            {
              // new example docType
              case: { $eq: ["$documentType", "XYZ"] },
              then: {
                "total": "$balance.total",
                "deductedAmount": "$balance.deducted"
              }
            }
          ],
          // without a `default`, unknown types will be a pipeline error
          default: {
            "total": 0,
            "note": "unknown doc type"
          }
        }
      }
    }
  },
  { $replaceWith: { $mergeObjects: ["$$ROOT", "$newdoc"] } },
  { $unset: ["balance", "newdoc"] }
])

    Mongo Playground with additional types

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

Back To Top
Search