Transforming Array values in MongoDB

Question

I am new to MongoDB, here i am trying to transforming the data inside each documents with the given data. Please help me to complete this task.

getconvaggregate1 collection:(existing)

 {
            "_id" : ObjectId("603faf38150786680421b52b"),
            "conversationMetricName" : [
                    {
                            "metric" : "nConnected",
                            "stats" : {
                                    "count" : 3
                            }
                    },
                    {
                            "metric" : "tAbandon",
                            "stats" : {
                                    "max" : 319012,
                                    "min" : 7372,
                                    "count" : 1,
                                    "sum" : 326384
                            }
                    },
                    {
                            "metric" : "tAcd",
                            "stats" : {
                                    "max" : 319012,
                                    "min" : 7372,
                                    "count" : 1,
                                    "sum" : 326384
                            }
                    }
            ],
            "conversationInterval" : "2021-02-17T18:30:00.000Z/2021-02-18T18:30:00.000Z",
            "__v" : 0
    }
{
        "_id" : ObjectId("603faf38150786680421b52b"),
        "conversationMetricName" : [
                {
                        "metric" : "nConnected",
                        "stats" : {
                                "count" : 19
                        }
                },
                {
                        "metric" : "tAbandon",
                        "stats" : {
                                "max" : 319012,
                                "min" : 7372,
                                "count" : 2,
                                "sum" : 326384
                        }
                }
                
        ],
        "conversationInterval" : "2021-02-18T18:30:00.000Z/2021-02-19T18:30:00.000Z",
        "__v" : 0
}

Required Collection Data :

Here i need to compare with value of "metric" and need to pick the "Count" correspondingly. If suppose if certain metric not present , i need to map that with '0' value.

{
        "_id" : ObjectId("60468a01010e8f08b0f794d6"),
        "conversationInterval" : "2021-02-17T18:30:00.000Z/2021-02-18T18:30:00.000Z",
        "nConnected":3
        "tAbandon":1,
        "tAcd":1
        "__v" : 0
}      
{
        "_id" : ObjectId("60468a01010e8f08b0f794d7"),
        "conversationInterval" : "2021-02-18T18:30:00.000Z/2021-02-19T18:30:00.000Z",
        "nConnected":19
        "tAbandon":2,
        "tAcd":0
        "__v" : 0
}

AlexisG · Accepted Answer · 2021-03-18 11:09:07Z

0

Here is a solution with an aggregate using $arrayToObject and $replaceRoot

db.collection.aggregate([
  {
    "$project": {
      "_id": 1,
      "__v": 1,
      "conversationInterval": 1,
      "metrics": {
        "$arrayToObject": {
          "$map": {
            "input": "$conversationMetricName",
            "as": "el",
            "in": {
              "k": "$$el.metric",
              "v": "$$el.stats.count"
            }
          }
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$metrics",
          "$$ROOT"
        ]
      }
    }
  },
  {
    "$project": {
      "metrics": 0
    }
  }
])

Try it here

answered Mar 18, 2021 at 11:09

AlexisG

2,4843 gold badges13 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hari Over a year ago

Thank you very much for your help. I have only one suggestion required , if suppose the metric -tAcd need to be shown as 0 , currently "nConnected":19,"tAbandon":2, but missing "tAcd":0. Is it possible to show the missing values as 0 , according to the data present.

Collectives™ on Stack Overflow

Transforming Array values in MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related