Mongo aggregation to fetch indexed values from string

Question

Here is the sample document

{   
  "pId":12345,    
     "charges": {
         "key1": "10.0,11.0,12.0,13.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0,10.0",
         "key2": "22.5%,12.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%,22.5%",
         "key3": "271.95,371.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,271.95,394.45,271.95,394.45",
         ...
    }
} 

Charge keys are dynamic. I wanted to fetch the requested index values from charges

Ex: input index is (1, 2) the expected result is.

{   
  "pId":12345,    
     "charges": {
         "key1": "11.0,12.0",
         "key2": "12.5%,22.5%",
         "key3": "371.95,271.95",
         ...
    }
} 

I tried with this projection

Stage1:

{ "$project" : { "pId" : 1, "charges" : { "key1" : { "$split" : ["$$charges.key1", ","]}}}},

Stage2:

{ "$project" : { "pId" : 1, "charges" : "$concat" : [{ "$arrayElemAt" : ["$$charges.key1", 1]}, ",", { "$arrayElemAt" : ["$$charges.key1", 2]}]}}}]}

For this projection, I need to know the key names and need to mention all keys in the projection. Is there any other way to dynamically split and get indexed values for all keys from the charges map?

Answer 1

MongoDb playground link : https://mongoplayground.net/p/kToLhxCN93f

$concat with $cond & $gt used to add comma(,) in string if $$value is not empty.

reduce: traverses array (can apply some operations) and returns string instead of array.

db.collection.aggregate([
  {
    "$project": {
      pId: 1,
      "charges": {
        "$map": {
          "input": {
            "$objectToArray": "$charges"
          },
          in: {
            K: "$$this.k",
            v: {
              "$split": [
                "$$this.v",
                ","
              ]
            }
          }
        }
      }
    }
  },
  {
    "$project": {
      pId: 1,
      charges: {
        "$arrayToObject": {
          $map: {
            input: "$charges",
            in: {
              k: "$$this.K",
              v: {
                "$reduce": {
                  "input": {
                    "$map": {
                      //input array of index to fetch values
                      "input": [
                        0,
                        2
                      ],
                      "as": "index",
                      "in": {
                        "$arrayElemAt": [
                          "$$this.v",
                          "$$index"
                        ]
                      }
                    }
                  },
                  "initialValue": "",
                  "in": {
                    "$concat": [
                      "$$value",
                      {
                        "$cond": [
                          {
                            "$gt": [
                              "$$value",
                              ""
                            ]
                          },
                          ",",
                          ""
                        ]
                      },
                      "$$this"
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])