Hi all,
Im completely new to Mongo and trying to understand how it stores data but specifically its capability to aggregate data.
The reason I am asking is that I am currently looking for to replace a relational system with billions or records of orderdata.
effectively there are about 150 million orders in the system but through all the lines, transactions and services this results in billons of records across multiple tables.
I understand that Mongo stores its data in Json/bson formats but I am not clear on how it would perform aggregating this
Please correct me if I am wrong but my thinking would be that in Mongo it would be possible to store a document (Json/bson) that contains for example an order and some (maybe all) of its lines/transactions
Something like this:
{
"_id": 1,
"reference : "ORDERXYZ",
"orderdate" : "2020-01-01",
"orderlines" : [
{
"position" : 1,
"description" : "blue box",
"quantity" : 1,
"price" : 1.99
}, {
"position" : 2,
"description" : "red box",
"quantity" : 2,
"price" : 3.99
}
]
}
So in my example the idea would be to have a 150 million documents stored like this … instead of 800 million rows accross multiple tables:
My question is how would Mongo perform if I were to query and aggregate this data? For example in Classic SQL assuming I have a order and orderline table I could use the following query to give me a list of orders containing “red boxes” and the total value they make up in each order:
SELECT o.reference, SUM(l.price * l.quantity) AS summedvalue
FROM order o
INNER JOIN lines l
ON o.id = l.order_id
WHERE l.description = ‘red box’
GROUP BY o.reference
I am sure this is possible in Mongo but was wondering if someone could help me out on how the qery performance would compare to “classic” relational databases and also whether my understanding of how Mongo works is correct.
Many thanks
j