This is my first question ever here so super excited to learn and apologies if the syntax is not up to mark, I will improve with time.

1. "item" is an Array ( this doc has only one element)
2. "adjudication" is a nested Array with a variable number of elements(same structure)
3. I want to create keys out of "adjudication.category.coding.code" without hardcoding as the values will be different with each document but will have the same string length
4. I tried using "$map" to apply the same logic to each array element but failed

    "item": [
      {
        "adjudication": [
          {
            "amount": {"code": "USD", "system": "4217", "value": 22.51},
            "category": {
              "coding": [
                {
                  "code": "bb.org/paid_amt",
                  "system": "bb.org/adjudication"
                }
              ]
            },
            "reason": {
              "coding": [
                {
                  "code": "C",
                  "system": "bb.org/cvrg_status"
                }
              ]
            }
          },
          {
            "amount": {"code": "USD", "system": "4217", "value": 0},
            "category": {
              "coding": [
                {
                  "code": "bb.org/discount_amt",
                  "system": "bb.org/adjudication"
                }
              ]
            }
          }
        ]
      }
    ]

Output desired

adjudication: {paid_amt: 22.51, discount_amt: 0}

CodePudding user response：

Welcome to SO. I hope the following code will solve your exception. Sometime you may modify based on you wish.

• $unwind to deconstruct the array
• $map to loop / modify through the array, and $arrayElementAt to. get the first object from the array
• $let to find the last potion by $spliting for "paid_amt" and "discount_amt". So this will be an array of object. But you need objects. So the next part I make it as k:v pair.
• $arrayToObject to make array to object by using above k:v pair. ("k" and "v" names are must. Can't replace by any other names)
• $group to reconstruct the array that we did in 1st step

Here is the code,

db.collection.aggregate([
  { "$unwind": "$item" },
  {
    $project: {
      "item.adjudication": {
        "$map": {
          "input": "$item.adjudication",
          "in": {
            amount: "$$this.amount.value",
            code: {
              "$arrayElemAt": [ "$$this.category.coding", 0 ]
            }
          }
        }
      }
    }
  },
  {
    $project: {
      "item.adjudication": {
        "$map": {
          "input": "$item.adjudication",
          "in": {
            v: "$$this.amount",
            k: {
              "$let": {
                "vars": {
                  "code": {
                    "$split": [ "$$this.code.code", "/" ]
                  }
                },
                "in": { "$arrayElemAt": [ "$$code",  -1 ]  }
              }
            }
          }
        }
      }
    }
  },
  {
    $project: {
      "item.adjudication": {
        "$arrayToObject": "$item.adjudication"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "item": {  "$push": "$item" }
    }
  }
])

Working Mongo playground

CodePudding user response：

try this playground

db.collection1.aggregate(
[{
    $unwind: {
        path: '$item'
    }
}, {
    $unwind: {
        path: '$item.adjudication'
    }
}, {
    $unwind: {
        path: '$item.adjudication.category'
    }
}, {
    $unwind: {
        path: '$item.adjudication.category.coding'
    }
}, {
    $group: {
        _id: null,
        data: {
            $push: {
                k: '$item.adjudication.category.coding.code',
                v: '$item.adjudication.amount.value'
            }
        }
    }
}, {
    $project: {
        _id:0,
        adjudication: {$arrayToObject: '$data'}
    }
}]
)