numberDecimal in Table displaying as JSON by default and 0 as Number

[toddat]

I'm pulling some basic sales data from a MongoDB using the following aggregate query:

[
{
"$match": {
"Vertical Name": { "$eq": "Consumer"},
"Region": { "$eq": "USA" },
"Items.SKU Type Name": { "$eq": "Bundle"},
"Items.Title ID": { "$eq": "APL" }
}
},
{
$group:
{
"_id": { $month: "$Order Date" },
"totalAmount": { $sum: "$Sale" },
"count": { $sum: 1 }
}
},
{
$project: {
"_id": 0,
"Year": "$_id",
"Total Sales": "$totalAmount",
"count": 1,
"Avg Sales": { "$divide": [ "$totalAmount", "$count" ] }
}
},
{
$sort: { Year: 1}
}
]

The Total Sales and Avg Sales are calculated from Decimal128 fields.

When the data displays in the table it looks like this:

It defaults to displaying as JSON. When changed to display as Number it displays as 0.

I feel like I encountered this before and fixed it by changing the field to double in the database but there must be a better solution.

Any other ideas?

[GuilhermeSilva]

Hello @toddat,

Have you tried modifying the value directly on the coulmn of the table?
Something like this:

Change the source to the total sales.

You can also try to manipulate the data using a transformer or a variable to convert the Decimal128 to a number

Let me know if this helps or if you have already tried something like this.

[toddat]

Thanks for your idea. I gave that a try to no avail and instead, just converted the fields in $project to $toDouble and that solved the issue. Might be something for the Retool team to consider as a fix in the future. Having the table component to natively read Decimal128 and numberDecimal formats from a MongoDB query.

[
{
"$match": {
"Vertical Name": { "$eq": "Consumer"},
"Region": { "$eq": "USA" },
"Items.SKU Type Name": { "$eq": "Bundle"},
"Items.Title ID": { "$eq": "{{ GetTitlesListID.value}}" }
}
},
{
$group:
{
"_id": { $month: "$Order Date" },
"totalAmount": { $sum: "$Sale" },
"count": { $sum: 1 }
}
},
{
$project: {
"_id": 0,
"Year": "$_id",
"Total Sales": { $toDouble: "$totalAmount"},
"count": 1,
"Avg Sales": { $toDouble: { "$divide": [ "$totalAmount", "$count" ] } }
}
},
{
"$sort": { "Year": 1}
}
]

[Jack_T]

Hello @toddat!

Glad you were able to solve the issue by converting the field.

I can create a feature request to have the table component to natively read Decimal128 and numberDecimal formats from a MongoDB query!

Will keep you posted in this thread on any updates from the eng team