MongoDB string to int

Question

I want to convert the string of a MongoDB value field to integers and then calculate the average.

This is my JSON:

"_id" : ObjectId("5c49f398fc0078178c76705b"),     // my json data
    "Time_Created" : ISODate("2019-01-24T17:19:20.205Z"),   // date
    "Test_ID" : "1",
    "data" : [
        {
            "Device_id" : "1",
            "Total_wires" : "5",
            "Cables" : [
                {
                    "TAG" : "4001",
                    "Value" : "24.3"
                },
                {
                    "TAG" : "4002",
                    "Value" : "21.3"
                },
                {
                    "TAG" : "4003",
                    "Value" : "21.3"
                },
                {
                    "TAG" : "4004",
                    "Value" : "21.3"
                },                                                            
                {
                    "TAG" : "4005",                                        
                    "Value" : "100.3"
                }
            ]
        }
    ]
}

I am using the following query to extract average values

1. Query:

   db.collection_name.aggregate( [{  '$project': { 'values': '$data.Cables.Value', }}, { '$unwind': '$values' }, { '$addFields': { 'avgValue': { '$avg': { $toInt: '$values' } }  } } ] )
   

   But I get this error:

   2019-01-29T11:06:05.642-0800 E QUERY [js] Error: command failed:
   {
   "ok" : 0,
   "errmsg" : "Unsupported conversion from array to int in $convert
   with no onError value",
   "code" : 241,
   "codeName" : "ConversionFailure"
   } : aggregate failed :
   _getErrorWithCode@src/mongo/shell/utils.js:25:13
   doassert@src/mongo/shell/assert.js:18:14
   _assertCommandWorked@src/mongo/shell/assert.js:534:17
   assert.commandWorked@src/mongo/shell/assert.js:618:16
   DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
   DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1062:12
   @(shell):1:1

2. I also tried the following:

   db.collection_name.aggregate( 
   [{  '$project': { 'values': '$data.Cables.Value', }}, 
     { '$unwind': '$values' }, 
     { '$addFields': { 'avgValue': { '$avg': { 'input': '$values', 'to': 
     'int' } }  } } 
   ] )
   

   But I'm also getting this output:

   { "_id" : ObjectId("5c509604fc007808c427edcb"), "values" :
   [ "24.3", "23.3", "25.3", "31.3", "90.3" ], "avgValue" : null }

The expected value should be the average of the values fields.
I need some kind of conversion for nested document values.

The incoming string value has to be converted into integers or decimals and then the average of it should be calculated.

This is then used in C# code to generate the pipeline

var pipeline = new[] 
{ 
  project, unwind, addfields 
};

Answer 1

Use toDouble instead of toInt because your values are not integers. Try the stages below.

[
    {
        '$project': {
            'values': '$data.Cables.Value'
        }
    }, {
        '$unwind': {
            'path': '$values'
        }
    }, {
        '$project': {
            'values': {
                '$map': {
                    'input': '$values', 
                    'as': 'value', 
                    'in': {
                        '$toDouble': '$$value'
                    }
                }
            }
        }
    }, {
        '$addFields': {
            'avgValue': {
                '$avg': '$values'
            }
        }
    }
]