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

Mongodb – Mongo aggregation to fetch indexed values from string

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?

Tags:

2

Answers


  1. 0

    1. $set – Set the charges field by converting the key-value pair for the charges object into an array for easier iteration.

    2. $set – Set the charges field.

      2.1. $map – Iterate each element of the charges array and replace the v field by splitting the string into an array.

    3. $set – Set the charges field.

      3.1. $map – Iterate each element of the charges array and return a new array.

      3.1.1. $trim – Trim the input string to remove the (extra) front and extra comma character.

      3.1.1.1. $reduce – Iterate each element of the array and transform to return a string.

      3.1.1.1.1. input – With the $map operator to iterate each value of your array indexes and get the value by index via $arrayElemAt.

      3.1.1.1.2. initialValue – Empty string

      3.1.1.1.3. in – Combine the accumulated string value with the current iterate string value with "," via $concat.

    4. $set – Set the charges field by converting the array into a key-value pair.

    db.collection.aggregate([
  {
    "$set": {
      "charges": {
        $objectToArray: "$charges"
      }
    }
  },
  {
    "$set": {
      "charges": {
        $map: {
          input: "$charges",
          in: {
            k: "$$this.k",
            v: {
              $split: [
                "$$this.v",
                ","
              ]
            }
          }
        }
      }
    }
  },
  {
    $set: {
      charges: {
        $map: {
          input: "$charges",
          in: {
            k: "$$this.k",
            v: {
              "$trim": {
                "input": {
                  $reduce: {
                    input: {
                      $map: {
                        input: /* Your array of index(s) */,
                        as: "i",
                        in: {
                          $arrayElemAt: [
                            "$$this.v",
                            "$$i"
                          ]
                        }
                      }
                    },
                    initialValue: "",
                    in: {
                      $concat: [
                        "$$value",
                        ",",
                        "$$this"
                      ]
                    }
                  }
                },
                "chars": ","
              }
            }
          }
        }
      }
    }
  },
  {
    $set: {
      charges: {
        $arrayToObject: "$charges"
      }
    }
  }
])

    Demo @ Mongo Playground

    Login or Signup to reply.
  2. 0

    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"
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search