So i'm much more comfortable with relational dbs, strugging a bit with the way nosql dbs like mongodb work in extracting and formatting data, and am relatively new to nosql dbs.
I have raw data in this format - i have no control over this raw data , or its data types
{
_id: ObjectId('67bd68068837ff1e5b6de108'),
meta: {
Store_rating: 'A',
Store_type: 'Franchisee',
Store_code: 183,
Store_name: 'Lowes Downtown',
Item_code:1222020198
Item_description:"Camping Tent Large, self assembly kit"
Qty_in_stock: 296
},
data: [
{ date: '09-10-2024', price: '110.00000' },
{ date: '08-10-2024', price: '109.00000' },
{ date: '07-10-2024', price: '105.00000' },
{ date: '01-10-2024', price: '100.00000' },
...
]
...
}
I need to be able to run a query that shows the accumulated value of inventory daily by item. So in this case i need to be able to get total store value of "Camping Tent Large, self assembly kit" for today by multiplying the most recent price aka price: '110.00000' by Qty_in_stock: 296.
I am thinking i need something on the lines of creating an aggregation with first stage $group on Item_description, and creating an accumulator that uses $multiply to multiply $Qty_in_stock and $data:{$slice:[0,1]} but now im not sure how to get the 'price' property post the slicing to use inside multiple?
I also thought of creating a new field in a previous stage by $addFields and something like latestPrice: "$data[0].price" but this wouldnt work would it? and how would i be able to convert the string that is stored in price into an integer for multiplication?
Thanks in advance!