locked
Is Decimal Supported in operations using $unwind $group $multiply? RRS feed

  • Question

  • I am using Azure CosmoDb APi 3.4 with aggregation enabled and I am trying to group my data for year-month and add a value in that group after multipying it by another value. When I run this on mongodb it works fine, but when I try to do it in Cosmos, I get this error:

    2019-11-13T12:31:52.565-0300 E  QUERY    [js] uncaught exception: Error: command failed: {
            "_t" : "OKMongoResponse",
            "ok" : 0,
            "code" : 115,
            "errmsg" : "'Modification of Decimal128 not supported' is not supported",
            "$err" : "'Modification of Decimal128 not supported' is not supported"
    } : aggregate failed :

    I don't understand why this is failing, and what work around I could use to achieve the same result. My data looks like this:

    > > db.myCollection.find().pretty() {
    >         "_id" : ObjectId("5dc9b30727880d660c12be2e"),
    >         "Created" : ISODate("2019-11-11T19:14:15.584Z"),
    >         "MyCollection" : [
    >                 {
    >                         "DateField1" : ISODate("2019-11-11T00:00:00Z"),
    >                         "DecimalValue1" : NumberDecimal("101")
    >                 },
    >                 {
    >                         "DateField1" : ISODate("2019-11-12T00:00:00Z"),
    >                         "DecimalValue1" : NumberDecimal("102")
    >                 },
    >                 {
    >                         "DateField1" : ISODate("2019-11-13T00:00:00Z"),
    >                         "DecimalValue1" : NumberDecimal("103")
    >                 }
    >         ] }

    This is the aggregation query I am using:

    db.myCollection.aggregate([
    { $unwind: "$MyCollection" },
    {$group: {
            _id: {month : {$month : "$MyCollection.DateField1"}, 
              year : {$year :  "$MyCollection.DateField1"}}, 
            calculation: { $sum: { $multiply: [ 0.05, "$MyCollection.DecimalValue1" ] } }
        }},
        { $project: { month: "$id.month", year : "$id.year", calculation: "$calculation"},
    ])

    I read the documentation and apparently, at least to my understanding, all the commands I am trying to use are supported by Cosmos. However if you remove "$MyCollection.DecimalValue1" from the equation, it works. Basically, if I use 0.05 instead of the field in the sum/multiply it doesn't fail.

    I tried asking on SO, but after some more digging, it kinda looks like this operation is not supported for decimal fields, which it is really baffling, because I can't not see anything on the documentation about it and it doesn't seem to have issues with other tipes of fields. Instead of using $sum and $multiply I tried to $push the values to a new array to later do the calculation either on the server or the $project, but it threw the same error:

    db.myCollection.aggregate([
    { $unwind: "$MyCollection" },
    {$group: {
            _id: {month : {$month : "$MyCollection.DateField1"}, 
              year : {$year :  "$MyCollection.DateField1"}}, 
            test: { $push: "$MyCollection.DecimalValue1" }
        }},
        { $project: { month: "$id.month", year : "$id.year", test: "$test"},
    ])
    

    Wednesday, November 13, 2019 3:38 PM

All replies

  • Hi Kzy23,

    Can you confirm with version of the Cosmos DB API for MongoDB you have deployed? You have an option of version 3.2 or 3.6. I see that NumberDecimal is supported with version 3.4 of the MongoDB shell but need to understand which version of the Cosmos DB API for MongoDB you have deployed. Thank you!

    Thursday, November 14, 2019 12:13 AM
  • Please let me know if you have any additional questions or need assistance with this topic.

    Regards,

    Mike

    Friday, November 15, 2019 12:34 AM
  • Sorry for the delay on my answer. I didn't deploy the instance, so I was trying to figure out what version we had. I asked the team and they are not sure, the instance was deployed before and we don't know where to see what version is being used. Is there a way to check that without reinstalling the instance?

    regards,

    Kzy

    Friday, November 15, 2019 4:45 PM
  • Hi Kzy23,

    There is currently no way to identify your current MongoDB API version unless you reach out to the product group. You are likely deployed with version 3.2, as 3.6 became available just recently. Version 3.4 of the driver was in preview but believe this was rolled into version 3.6.

    There are a couple options available. 

    1. Deploy a second instance as version 3.6 and run a proof of concept to see if the desired functionality is achieved.
    2. If #1 is a success, you can have the product group upgrade your existing 3.2 environment to 3.6 -or- you can migrate your 3.2 instance to a 3.6 instance.

    The CosmosDB product group is happy to assist with upgrading your existing 3.2 deployment. They can be reached by sending an email to AskCosmosMongoAPI and include your Cosmos DB instance name.

    Protocol Support by version:

    Azure Cosmos DB's API for MongoDB (3.2 version): supported features and syntax

    Azure Cosmos DB's API for MongoDB (3.6 version): supported features and syntax

    The Aggregation stages $group and $unwind are available in 3.2 and 3.6 but I am not seeing $multiply in either but is available as an Arithmetic expression. Version 3.6 does introduce a fix for Aggregation Pipeline where there is a 40mb limit of the result set in 3.2.

    Please ensure that you have the desired Mongo features enabled.

    Please let me know if you have any additional questions. Again, you can reach out to the product group directly if you wish to consider upgrading your current 3.2 environment. They also can help your understand what version your environment is currently deployed as.

    Regards,

    Mike

    Friday, November 15, 2019 11:15 PM