Mongodb accurate $sum for $filter and $map or $unwind
I have a schema with several nested arrays. I want to get the sum of a particular item based on a specific query.
This is my query.
UPDATE
If anyone is asking why this structure?. I needed a system where updating a point can be done once without having to search several places before an update is completed successfully without error. There may be several update operations and if all these arrays are converted to tiny entities then updating an item may become a very serious, tedious and problematic task.
db.getCollection('cuisine').aggregate([
{$match: {
first_level:{
$elemMatch:{
"first_item":"United kingdom",
"second_item":"Great Britian",
"third_item":"England",
second_level:{
$elemMatch:{
second_lev_one:"London",
third_level:{
$elemMatch:{
third_lev_one:"city of london",
fourth_level:{
$elemMatch:{
fourth_lev_one:"crab donut",
fourth_lev_two:"Chillout place"
}
}
}
}
}
}
}
}
}},
{"$addFields":{
"first_level":{
"$filter":{
"input":{
"$map":{
"input":"$first_level",
"as":"firstlev",
"in":{
"_id":"$$firstlev._id",
"second_level":{
"$filter":{
"input":{
"$map":{
"input":"$$firstlev.second_level",
"as":"sec_lev",
"in":{
"_id":"$$sec_lev._id",
"second_lev_one":"$$sec_lev.second_lev_one",
"third_level":{
"$filter":{
"input":{
"$map":{
"input":"$$sec_lev.third_level",
"as":"third_lev",
"in":{
"_id":"$$third_lev._id",
"third_lev_one":"$$third_lev.third_lev_one",
"fourth_level":{
"filter":{
"input":"$$third_lev.fourth_level",
"as":"fourth_lev",
"cond":{
"$and": [
{ "$eq": [ "$$fourth_lev.fourth_lev_one", "crab donut"] },
{ "$eq": [ "$$fourth_lev.fourth_lev_two","Chillout place" ] }
]
}
}
}
}
}
},
"as":"third_lev",
"cond":{
"$and": [
{ "$eq": [ "$$third_lev.third_lev_one", "city of london"] },
{ "$ne": [ "$$third_lev.fourth_level", ] }
]
}
}
}
}
}
},
"as":"sec_lev",
"cond":{
"$and": [
{ "$eq": [ "$$sec_lev.second_lev_one", "London" ] },
{ "$ne": [ "$$sec_lev.third_level", ] }
]
}
}
}
}
}
},
"as":"class",
"cond": {
"$and": [
{ "$eq": [ "$$class.first_item", "United kingdom" ] },
{ "$eq": [ "$$class.second_item", "Great Britian" ] },
{ "$eq": [ "$$class.third_item", "England" ] },
{ "$ne": [ "$$class.second_level", ] }
]
}
}
}
}},
{"$group":{
"_id":{
"first_item":"$first_level.first_item",
"third_item":"$first_level.third_item",
"second_lev_one":"$first_level.second_level.second_lev_one",
"third_lev_one": "$first_level.second_level.third_level.third_lev_one",
"fourth_lev_two":"$first_level.second_level.third_level.fourth_level.fourth_lev_two",
"fourth_lev_one":"$first_level.second_level.third_level.fourth_level.fourth_lev_one",
"total_price":"$first_level.second_level.third_level.fourth_level.price"
},
"Totalprice":{
"$sum": "$first_level.second_level.third_level.fourth_level.price"
}
}}
]);
this is the output that I am recieving (when i use $filter $map)
{ "_id" :
{ "first_item" : [ "United kingdom" ], "third_item" : [ "England" ], "second_lev_one" : [ [ "London" ] ],
"third_lev_one" : [ [ [ "city of london" ] ] ], "fourth_lev_two" : [ [ [ [ "Chillout place", "Chillout place" ] ] ] ],
"fourth_lev_one" : [ [ [ [ "crab donut", "crab donut" ] ] ] ], "total_price" : [ [ [ [ 20, 20 ] ] ] ] },
"Totalprice" : 0 }
it is not summing the total price which is inside an array. I expected it to be 40 but it is bringing 0. Aside from that I do not
know why it is putting the result inside array.
I make use of $filter $map. I did not use $unwind because this is not giving me accurate data.
Please how can i get the sum and also show the main result instead of it showing inside an array
Any help would be appreciated
but when i use $unwind
{ "_id" : { "first_item" : "United kingdom", "third_item" : "England", "second_lev_one" : "London", "third_lev_one" : "city of london", "fourth_lev_two" : "Chillout place", "fourth_lev_one" : "crab donut", "total_price" : 20 }, "Totalprice" : 40 },
{ "_id" : { "first_item" : "United kingdom", "third_item" : "England", "second_lev_one" : "London", "third_lev_one" : "city of london", "fourth_lev_two" : "Chillout place", "fourth_lev_one" : "fried chicken", "total_price" : 20 }, "Totalprice" : 20 }
Some of the things I want to achieve are
1. count the number of items that was found in total_price(in the result above there are two items)
get the total sum of the items in total price which would be displayed in Totalprice
perform some arithemthic operations such as subtracting some results from another field e.g.
If another field is produced such as individual price with items such as [[[[15,10]]]] then subtract the individual items from
the other field as in this case [[[[20,20]]]] and produce the result[[[[5,10]]]] and also get the full total of 15perform some operations on a field before moving on to the cross field operations e.g. [[[[15,10]]]] * 1.5 the result to
be subtracted from [[[[20,20]]]].
To cut it short perform some operation on the individual items and then performing full operation on another field
All these I have studied going through mongodb site but I have not been able to find a way out. I hope that I would soon
be able to get this things done without having to bother anyone.
I am not sure if this request is off topic but please what material e.g. book can be useful for advance monogodb operations,
all the ones I have come across are jut explaining simple stuffs which is not differen from what some sites hav explained.
Data section
{ "_id" : ObjectId("5bbf975759a97c1094a200b2"),
"first_level" : [
{ "_id" : ObjectId("5bbf975759a97c1094a200b3"),
"staff_id" : ObjectId("5b8b5abb518a1a0a601a18d0"),
"first_item" : "United kingdom",
"second_item" : "Great Britian",
"third_item" : "England",
"second_level" : [
{ "_id" : ObjectId("5bc786bea967e812cc986a79"), "second_lev_one" : "Yorkshire and the Humber"},
{ "_id" : ObjectId("5bc786dda967e812cc986a7d"), "second_lev_one" : "East of England" },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b85"), "second_lev_one" : "London", "third_level" : [
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b88"), "third_lev_one" : "city of london", "fourth_level" : [
{ "_id" : ObjectId("5bd8060a8ef0fc173c8aa597"), "price" : 20, "sales_date" : ISODate("2018-10-30T00:00:00Z"), "fourth_lev_one" : "crab donut", "fourth_lev_two" : "Chillout place" },
{ "_id" : ObjectId("5bd806128ef0fc173c8aa599"), "price" : 20, "sales_date" : ISODate("2018-10-30T00:00:00Z"), "fourth_lev_one" : "crab donut", "fourth_lev_two" : "Chillout place" },
{ "_id" : ObjectId("5be266b60cecc912681f7c48"), "price" : 20, "sales_date" : ISODate("2018-11-06T00:00:00Z"), "fourth_lev_one" : "fried chicken", "fourth_lev_two" : "Chillout center"} ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8a"), "third_lev_one" : "City of Westminster", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8c"), "third_lev_one" : "Kensington and Chelsea", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8e"), "third_lev_one" : "Hammersmith and Fulham", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b90"), "third_lev_one" : "Wandsworth", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b92"), "third_lev_one" : "Lambeth", "fourth_level" : [ ] }
] }
]}
] }
mongodb sum
asked Nov 12 at 19:31
S Mev
236
add a comment |
I have a schema with several nested arrays. I want to get the sum of a particular item based on a specific query.
This is my query.
UPDATE
If anyone is asking why this structure?. I needed a system where updating a point can be done once without having to search several places before an update is completed successfully without error. There may be several update operations and if all these arrays are converted to tiny entities then updating an item may become a very serious, tedious and problematic task.
db.getCollection('cuisine').aggregate([
{$match: {
first_level:{
$elemMatch:{
"first_item":"United kingdom",
"second_item":"Great Britian",
"third_item":"England",
second_level:{
$elemMatch:{
second_lev_one:"London",
third_level:{
$elemMatch:{
third_lev_one:"city of london",
fourth_level:{
$elemMatch:{
fourth_lev_one:"crab donut",
fourth_lev_two:"Chillout place"
}
}
}
}
}
}
}
}
}},
{"$addFields":{
"first_level":{
"$filter":{
"input":{
"$map":{
"input":"$first_level",
"as":"firstlev",
"in":{
"_id":"$$firstlev._id",
"second_level":{
"$filter":{
"input":{
"$map":{
"input":"$$firstlev.second_level",
"as":"sec_lev",
"in":{
"_id":"$$sec_lev._id",
"second_lev_one":"$$sec_lev.second_lev_one",
"third_level":{
"$filter":{
"input":{
"$map":{
"input":"$$sec_lev.third_level",
"as":"third_lev",
"in":{
"_id":"$$third_lev._id",
"third_lev_one":"$$third_lev.third_lev_one",
"fourth_level":{
"filter":{
"input":"$$third_lev.fourth_level",
"as":"fourth_lev",
"cond":{
"$and": [
{ "$eq": [ "$$fourth_lev.fourth_lev_one", "crab donut"] },
{ "$eq": [ "$$fourth_lev.fourth_lev_two","Chillout place" ] }
]
}
}
}
}
}
},
"as":"third_lev",
"cond":{
"$and": [
{ "$eq": [ "$$third_lev.third_lev_one", "city of london"] },
{ "$ne": [ "$$third_lev.fourth_level", ] }
]
}
}
}
}
}
},
"as":"sec_lev",
"cond":{
"$and": [
{ "$eq": [ "$$sec_lev.second_lev_one", "London" ] },
{ "$ne": [ "$$sec_lev.third_level", ] }
]
}
}
}
}
}
},
"as":"class",
"cond": {
"$and": [
{ "$eq": [ "$$class.first_item", "United kingdom" ] },
{ "$eq": [ "$$class.second_item", "Great Britian" ] },
{ "$eq": [ "$$class.third_item", "England" ] },
{ "$ne": [ "$$class.second_level", ] }
]
}
}
}
}},
{"$group":{
"_id":{
"first_item":"$first_level.first_item",
"third_item":"$first_level.third_item",
"second_lev_one":"$first_level.second_level.second_lev_one",
"third_lev_one": "$first_level.second_level.third_level.third_lev_one",
"fourth_lev_two":"$first_level.second_level.third_level.fourth_level.fourth_lev_two",
"fourth_lev_one":"$first_level.second_level.third_level.fourth_level.fourth_lev_one",
"total_price":"$first_level.second_level.third_level.fourth_level.price"
},
"Totalprice":{
"$sum": "$first_level.second_level.third_level.fourth_level.price"
}
}}
]);
this is the output that I am recieving (when i use $filter $map)
{ "_id" :
{ "first_item" : [ "United kingdom" ], "third_item" : [ "England" ], "second_lev_one" : [ [ "London" ] ],
"third_lev_one" : [ [ [ "city of london" ] ] ], "fourth_lev_two" : [ [ [ [ "Chillout place", "Chillout place" ] ] ] ],
"fourth_lev_one" : [ [ [ [ "crab donut", "crab donut" ] ] ] ], "total_price" : [ [ [ [ 20, 20 ] ] ] ] },
"Totalprice" : 0 }
it is not summing the total price which is inside an array. I expected it to be 40 but it is bringing 0. Aside from that I do not
know why it is putting the result inside array.
I make use of $filter $map. I did not use $unwind because this is not giving me accurate data.
Please how can i get the sum and also show the main result instead of it showing inside an array
Any help would be appreciated
but when i use $unwind
{ "_id" : { "first_item" : "United kingdom", "third_item" : "England", "second_lev_one" : "London", "third_lev_one" : "city of london", "fourth_lev_two" : "Chillout place", "fourth_lev_one" : "crab donut", "total_price" : 20 }, "Totalprice" : 40 },
{ "_id" : { "first_item" : "United kingdom", "third_item" : "England", "second_lev_one" : "London", "third_lev_one" : "city of london", "fourth_lev_two" : "Chillout place", "fourth_lev_one" : "fried chicken", "total_price" : 20 }, "Totalprice" : 20 }
Some of the things I want to achieve are
1. count the number of items that was found in total_price(in the result above there are two items)
get the total sum of the items in total price which would be displayed in Totalprice
perform some arithemthic operations such as subtracting some results from another field e.g.
If another field is produced such as individual price with items such as [[[[15,10]]]] then subtract the individual items from
the other field as in this case [[[[20,20]]]] and produce the result[[[[5,10]]]] and also get the full total of 15perform some operations on a field before moving on to the cross field operations e.g. [[[[15,10]]]] * 1.5 the result to
be subtracted from [[[[20,20]]]].
To cut it short perform some operation on the individual items and then performing full operation on another field
All these I have studied going through mongodb site but I have not been able to find a way out. I hope that I would soon
be able to get this things done without having to bother anyone.
I am not sure if this request is off topic but please what material e.g. book can be useful for advance monogodb operations,
all the ones I have come across are jut explaining simple stuffs which is not differen from what some sites hav explained.
Data section
{ "_id" : ObjectId("5bbf975759a97c1094a200b2"),
"first_level" : [
{ "_id" : ObjectId("5bbf975759a97c1094a200b3"),
"staff_id" : ObjectId("5b8b5abb518a1a0a601a18d0"),
"first_item" : "United kingdom",
"second_item" : "Great Britian",
"third_item" : "England",
"second_level" : [
{ "_id" : ObjectId("5bc786bea967e812cc986a79"), "second_lev_one" : "Yorkshire and the Humber"},
{ "_id" : ObjectId("5bc786dda967e812cc986a7d"), "second_lev_one" : "East of England" },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b85"), "second_lev_one" : "London", "third_level" : [
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b88"), "third_lev_one" : "city of london", "fourth_level" : [
{ "_id" : ObjectId("5bd8060a8ef0fc173c8aa597"), "price" : 20, "sales_date" : ISODate("2018-10-30T00:00:00Z"), "fourth_lev_one" : "crab donut", "fourth_lev_two" : "Chillout place" },
{ "_id" : ObjectId("5bd806128ef0fc173c8aa599"), "price" : 20, "sales_date" : ISODate("2018-10-30T00:00:00Z"), "fourth_lev_one" : "crab donut", "fourth_lev_two" : "Chillout place" },
{ "_id" : ObjectId("5be266b60cecc912681f7c48"), "price" : 20, "sales_date" : ISODate("2018-11-06T00:00:00Z"), "fourth_lev_one" : "fried chicken", "fourth_lev_two" : "Chillout center"} ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8a"), "third_lev_one" : "City of Westminster", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8c"), "third_lev_one" : "Kensington and Chelsea", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8e"), "third_lev_one" : "Hammersmith and Fulham", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b90"), "third_lev_one" : "Wandsworth", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b92"), "third_lev_one" : "Lambeth", "fourth_level" : [ ] }
] }
]}
] }
mongodb sum
asked Nov 12 at 19:31
S Mev
236
"I have a schema with several nested arrays" -- Well aside from this being a very bad point of design, you have actually omitted any sample document(s) needed to reproduce your "expected result" from the question. In order to allow someone to "reproduce" and then work to a desired solution. We need 1, Your "full" code and not just single line statements about what you tell us you have done. 2 Actual data capable of reproducing the current and expected results. See How to create a Minimal, Complete, and Verifiable example
– Neil Lunn
Nov 12 at 20:38
@Neil Lunn. Very grateful in pointing it out. how can i break the structure up and still achieve the crud operation effectively? I am seriously open to any help that can help improve my knowledge in this regard. i'm coming from sql background.
– S Mev
Nov 13 at 21:22
Whilst some code is an improvement, you are still not providing any "data" in the question in order to "reproduce the results" as in what you presently get and what you expect to get. An MCVE really needs to provide a small data sample that others can work with in order to solve your problem. We are not sitting at your computer and don't have access to data you do not give us within the content of your question.
– Neil Lunn
Nov 13 at 21:43
Sample data added
– S Mev
Nov 14 at 21:58
So you do realize you actually making usage of knowledge you gained from an answer here without actually acknowleging that source (yes that's me) by accepting the answer. You really should not be doing that here if you expect ongoing help. You also seem to have missed the point of what that answer was actually telling you.
– Neil Lunn
Nov 15 at 1:05
add a comment |
I have a schema with several nested arrays. I want to get the sum of a particular item based on a specific query.
This is my query.
UPDATE
If anyone is asking why this structure?. I needed a system where updating a point can be done once without having to search several places before an update is completed successfully without error. There may be several update operations and if all these arrays are converted to tiny entities then updating an item may become a very serious, tedious and problematic task.
db.getCollection('cuisine').aggregate([
{$match: {
first_level:{
$elemMatch:{
"first_item":"United kingdom",
"second_item":"Great Britian",
"third_item":"England",
second_level:{
$elemMatch:{
second_lev_one:"London",
third_level:{
$elemMatch:{
third_lev_one:"city of london",
fourth_level:{
$elemMatch:{
fourth_lev_one:"crab donut",
fourth_lev_two:"Chillout place"
}
}
}
}
}
}
}
}
}},
{"$addFields":{
"first_level":{
"$filter":{
"input":{
"$map":{
"input":"$first_level",
"as":"firstlev",
"in":{
"_id":"$$firstlev._id",
"second_level":{
"$filter":{
"input":{
"$map":{
"input":"$$firstlev.second_level",
"as":"sec_lev",
"in":{
"_id":"$$sec_lev._id",
"second_lev_one":"$$sec_lev.second_lev_one",
"third_level":{
"$filter":{
"input":{
"$map":{
"input":"$$sec_lev.third_level",
"as":"third_lev",
"in":{
"_id":"$$third_lev._id",
"third_lev_one":"$$third_lev.third_lev_one",
"fourth_level":{
"filter":{
"input":"$$third_lev.fourth_level",
"as":"fourth_lev",
"cond":{
"$and": [
{ "$eq": [ "$$fourth_lev.fourth_lev_one", "crab donut"] },
{ "$eq": [ "$$fourth_lev.fourth_lev_two","Chillout place" ] }
]
}
}
}
}
}
},
"as":"third_lev",
"cond":{
"$and": [
{ "$eq": [ "$$third_lev.third_lev_one", "city of london"] },
{ "$ne": [ "$$third_lev.fourth_level", ] }
]
}
}
}
}
}
},
"as":"sec_lev",
"cond":{
"$and": [
{ "$eq": [ "$$sec_lev.second_lev_one", "London" ] },
{ "$ne": [ "$$sec_lev.third_level", ] }
]
}
}
}
}
}
},
"as":"class",
"cond": {
"$and": [
{ "$eq": [ "$$class.first_item", "United kingdom" ] },
{ "$eq": [ "$$class.second_item", "Great Britian" ] },
{ "$eq": [ "$$class.third_item", "England" ] },
{ "$ne": [ "$$class.second_level", ] }
]
}
}
}
}},
{"$group":{
"_id":{
"first_item":"$first_level.first_item",
"third_item":"$first_level.third_item",
"second_lev_one":"$first_level.second_level.second_lev_one",
"third_lev_one": "$first_level.second_level.third_level.third_lev_one",
"fourth_lev_two":"$first_level.second_level.third_level.fourth_level.fourth_lev_two",
"fourth_lev_one":"$first_level.second_level.third_level.fourth_level.fourth_lev_one",
"total_price":"$first_level.second_level.third_level.fourth_level.price"
},
"Totalprice":{
"$sum": "$first_level.second_level.third_level.fourth_level.price"
}
}}
]);
this is the output that I am recieving (when i use $filter $map)
{ "_id" :
{ "first_item" : [ "United kingdom" ], "third_item" : [ "England" ], "second_lev_one" : [ [ "London" ] ],
"third_lev_one" : [ [ [ "city of london" ] ] ], "fourth_lev_two" : [ [ [ [ "Chillout place", "Chillout place" ] ] ] ],
"fourth_lev_one" : [ [ [ [ "crab donut", "crab donut" ] ] ] ], "total_price" : [ [ [ [ 20, 20 ] ] ] ] },
"Totalprice" : 0 }
it is not summing the total price which is inside an array. I expected it to be 40 but it is bringing 0. Aside from that I do not
know why it is putting the result inside array.
I make use of $filter $map. I did not use $unwind because this is not giving me accurate data.
Please how can i get the sum and also show the main result instead of it showing inside an array
Any help would be appreciated
but when i use $unwind
{ "_id" : { "first_item" : "United kingdom", "third_item" : "England", "second_lev_one" : "London", "third_lev_one" : "city of london", "fourth_lev_two" : "Chillout place", "fourth_lev_one" : "crab donut", "total_price" : 20 }, "Totalprice" : 40 },
{ "_id" : { "first_item" : "United kingdom", "third_item" : "England", "second_lev_one" : "London", "third_lev_one" : "city of london", "fourth_lev_two" : "Chillout place", "fourth_lev_one" : "fried chicken", "total_price" : 20 }, "Totalprice" : 20 }
Some of the things I want to achieve are
1. count the number of items that was found in total_price(in the result above there are two items)
get the total sum of the items in total price which would be displayed in Totalprice
perform some arithemthic operations such as subtracting some results from another field e.g.
If another field is produced such as individual price with items such as [[[[15,10]]]] then subtract the individual items from
the other field as in this case [[[[20,20]]]] and produce the result[[[[5,10]]]] and also get the full total of 15perform some operations on a field before moving on to the cross field operations e.g. [[[[15,10]]]] * 1.5 the result to
be subtracted from [[[[20,20]]]].
To cut it short perform some operation on the individual items and then performing full operation on another field
All these I have studied going through mongodb site but I have not been able to find a way out. I hope that I would soon
be able to get this things done without having to bother anyone.
I am not sure if this request is off topic but please what material e.g. book can be useful for advance monogodb operations,
all the ones I have come across are jut explaining simple stuffs which is not differen from what some sites hav explained.
Data section
{ "_id" : ObjectId("5bbf975759a97c1094a200b2"),
"first_level" : [
{ "_id" : ObjectId("5bbf975759a97c1094a200b3"),
"staff_id" : ObjectId("5b8b5abb518a1a0a601a18d0"),
"first_item" : "United kingdom",
"second_item" : "Great Britian",
"third_item" : "England",
"second_level" : [
{ "_id" : ObjectId("5bc786bea967e812cc986a79"), "second_lev_one" : "Yorkshire and the Humber"},
{ "_id" : ObjectId("5bc786dda967e812cc986a7d"), "second_lev_one" : "East of England" },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b85"), "second_lev_one" : "London", "third_level" : [
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b88"), "third_lev_one" : "city of london", "fourth_level" : [
{ "_id" : ObjectId("5bd8060a8ef0fc173c8aa597"), "price" : 20, "sales_date" : ISODate("2018-10-30T00:00:00Z"), "fourth_lev_one" : "crab donut", "fourth_lev_two" : "Chillout place" },
{ "_id" : ObjectId("5bd806128ef0fc173c8aa599"), "price" : 20, "sales_date" : ISODate("2018-10-30T00:00:00Z"), "fourth_lev_one" : "crab donut", "fourth_lev_two" : "Chillout place" },
{ "_id" : ObjectId("5be266b60cecc912681f7c48"), "price" : 20, "sales_date" : ISODate("2018-11-06T00:00:00Z"), "fourth_lev_one" : "fried chicken", "fourth_lev_two" : "Chillout center"} ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8a"), "third_lev_one" : "City of Westminster", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8c"), "third_lev_one" : "Kensington and Chelsea", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8e"), "third_lev_one" : "Hammersmith and Fulham", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b90"), "third_lev_one" : "Wandsworth", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b92"), "third_lev_one" : "Lambeth", "fourth_level" : [ ] }
] }
]}
] }
mongodb sum
asked Nov 12 at 19:31
S Mev
236
I have a schema with several nested arrays. I want to get the sum of a particular item based on a specific query.
This is my query.
UPDATE
If anyone is asking why this structure?. I needed a system where updating a point can be done once without having to search several places before an update is completed successfully without error. There may be several update operations and if all these arrays are converted to tiny entities then updating an item may become a very serious, tedious and problematic task.
db.getCollection('cuisine').aggregate([
{$match: {
first_level:{
$elemMatch:{
"first_item":"United kingdom",
"second_item":"Great Britian",
"third_item":"England",
second_level:{
$elemMatch:{
second_lev_one:"London",
third_level:{
$elemMatch:{
third_lev_one:"city of london",
fourth_level:{
$elemMatch:{
fourth_lev_one:"crab donut",
fourth_lev_two:"Chillout place"
}
}
}
}
}
}
}
}
}},
{"$addFields":{
"first_level":{
"$filter":{
"input":{
"$map":{
"input":"$first_level",
"as":"firstlev",
"in":{
"_id":"$$firstlev._id",
"second_level":{
"$filter":{
"input":{
"$map":{
"input":"$$firstlev.second_level",
"as":"sec_lev",
"in":{
"_id":"$$sec_lev._id",
"second_lev_one":"$$sec_lev.second_lev_one",
"third_level":{
"$filter":{
"input":{
"$map":{
"input":"$$sec_lev.third_level",
"as":"third_lev",
"in":{
"_id":"$$third_lev._id",
"third_lev_one":"$$third_lev.third_lev_one",
"fourth_level":{
"filter":{
"input":"$$third_lev.fourth_level",
"as":"fourth_lev",
"cond":{
"$and": [
{ "$eq": [ "$$fourth_lev.fourth_lev_one", "crab donut"] },
{ "$eq": [ "$$fourth_lev.fourth_lev_two","Chillout place" ] }
]
}
}
}
}
}
},
"as":"third_lev",
"cond":{
"$and": [
{ "$eq": [ "$$third_lev.third_lev_one", "city of london"] },
{ "$ne": [ "$$third_lev.fourth_level", ] }
]
}
}
}
}
}
},
"as":"sec_lev",
"cond":{
"$and": [
{ "$eq": [ "$$sec_lev.second_lev_one", "London" ] },
{ "$ne": [ "$$sec_lev.third_level", ] }
]
}
}
}
}
}
},
"as":"class",
"cond": {
"$and": [
{ "$eq": [ "$$class.first_item", "United kingdom" ] },
{ "$eq": [ "$$class.second_item", "Great Britian" ] },
{ "$eq": [ "$$class.third_item", "England" ] },
{ "$ne": [ "$$class.second_level", ] }
]
}
}
}
}},
{"$group":{
"_id":{
"first_item":"$first_level.first_item",
"third_item":"$first_level.third_item",
"second_lev_one":"$first_level.second_level.second_lev_one",
"third_lev_one": "$first_level.second_level.third_level.third_lev_one",
"fourth_lev_two":"$first_level.second_level.third_level.fourth_level.fourth_lev_two",
"fourth_lev_one":"$first_level.second_level.third_level.fourth_level.fourth_lev_one",
"total_price":"$first_level.second_level.third_level.fourth_level.price"
},
"Totalprice":{
"$sum": "$first_level.second_level.third_level.fourth_level.price"
}
}}
]);
this is the output that I am recieving (when i use $filter $map)
{ "_id" :
{ "first_item" : [ "United kingdom" ], "third_item" : [ "England" ], "second_lev_one" : [ [ "London" ] ],
"third_lev_one" : [ [ [ "city of london" ] ] ], "fourth_lev_two" : [ [ [ [ "Chillout place", "Chillout place" ] ] ] ],
"fourth_lev_one" : [ [ [ [ "crab donut", "crab donut" ] ] ] ], "total_price" : [ [ [ [ 20, 20 ] ] ] ] },
"Totalprice" : 0 }
it is not summing the total price which is inside an array. I expected it to be 40 but it is bringing 0. Aside from that I do not
know why it is putting the result inside array.
I make use of $filter $map. I did not use $unwind because this is not giving me accurate data.
Please how can i get the sum and also show the main result instead of it showing inside an array
Any help would be appreciated
but when i use $unwind
{ "_id" : { "first_item" : "United kingdom", "third_item" : "England", "second_lev_one" : "London", "third_lev_one" : "city of london", "fourth_lev_two" : "Chillout place", "fourth_lev_one" : "crab donut", "total_price" : 20 }, "Totalprice" : 40 },
{ "_id" : { "first_item" : "United kingdom", "third_item" : "England", "second_lev_one" : "London", "third_lev_one" : "city of london", "fourth_lev_two" : "Chillout place", "fourth_lev_one" : "fried chicken", "total_price" : 20 }, "Totalprice" : 20 }
Some of the things I want to achieve are
1. count the number of items that was found in total_price(in the result above there are two items)
get the total sum of the items in total price which would be displayed in Totalprice
perform some arithemthic operations such as subtracting some results from another field e.g.
If another field is produced such as individual price with items such as [[[[15,10]]]] then subtract the individual items from
the other field as in this case [[[[20,20]]]] and produce the result[[[[5,10]]]] and also get the full total of 15perform some operations on a field before moving on to the cross field operations e.g. [[[[15,10]]]] * 1.5 the result to
be subtracted from [[[[20,20]]]].
To cut it short perform some operation on the individual items and then performing full operation on another field
All these I have studied going through mongodb site but I have not been able to find a way out. I hope that I would soon
be able to get this things done without having to bother anyone.
I am not sure if this request is off topic but please what material e.g. book can be useful for advance monogodb operations,
all the ones I have come across are jut explaining simple stuffs which is not differen from what some sites hav explained.
Data section
{ "_id" : ObjectId("5bbf975759a97c1094a200b2"),
"first_level" : [
{ "_id" : ObjectId("5bbf975759a97c1094a200b3"),
"staff_id" : ObjectId("5b8b5abb518a1a0a601a18d0"),
"first_item" : "United kingdom",
"second_item" : "Great Britian",
"third_item" : "England",
"second_level" : [
{ "_id" : ObjectId("5bc786bea967e812cc986a79"), "second_lev_one" : "Yorkshire and the Humber"},
{ "_id" : ObjectId("5bc786dda967e812cc986a7d"), "second_lev_one" : "East of England" },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b85"), "second_lev_one" : "London", "third_level" : [
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b88"), "third_lev_one" : "city of london", "fourth_level" : [
{ "_id" : ObjectId("5bd8060a8ef0fc173c8aa597"), "price" : 20, "sales_date" : ISODate("2018-10-30T00:00:00Z"), "fourth_lev_one" : "crab donut", "fourth_lev_two" : "Chillout place" },
{ "_id" : ObjectId("5bd806128ef0fc173c8aa599"), "price" : 20, "sales_date" : ISODate("2018-10-30T00:00:00Z"), "fourth_lev_one" : "crab donut", "fourth_lev_two" : "Chillout place" },
{ "_id" : ObjectId("5be266b60cecc912681f7c48"), "price" : 20, "sales_date" : ISODate("2018-11-06T00:00:00Z"), "fourth_lev_one" : "fried chicken", "fourth_lev_two" : "Chillout center"} ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8a"), "third_lev_one" : "City of Westminster", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8c"), "third_lev_one" : "Kensington and Chelsea", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b8e"), "third_lev_one" : "Hammersmith and Fulham", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b90"), "third_lev_one" : "Wandsworth", "fourth_level" : [ ] },
{ "_id" : ObjectId("5bd7ef5eca682d1b34ff6b92"), "third_lev_one" : "Lambeth", "fourth_level" : [ ] }
] }
]}
] }
mongodb sum
mongodb sum
asked Nov 12 at 19:31
S Mev
236
asked Nov 12 at 19:31
S Mev
236
edited Nov 14 at 21:58
asked Nov 12 at 19:31
S Mev
236
asked Nov 12 at 19:31
S Mev
236
asked Nov 12 at 19:31
S Mev
236
236
"I have a schema with several nested arrays" -- Well aside from this being a very bad point of design, you have actually omitted any sample document(s) needed to reproduce your "expected result" from the question. In order to allow someone to "reproduce" and then work to a desired solution. We need 1, Your "full" code and not just single line statements about what you tell us you have done. 2 Actual data capable of reproducing the current and expected results. See How to create a Minimal, Complete, and Verifiable example
– Neil Lunn
Nov 12 at 20:38
@Neil Lunn. Very grateful in pointing it out. how can i break the structure up and still achieve the crud operation effectively? I am seriously open to any help that can help improve my knowledge in this regard. i'm coming from sql background.
– S Mev
Nov 13 at 21:22
Whilst some code is an improvement, you are still not providing any "data" in the question in order to "reproduce the results" as in what you presently get and what you expect to get. An MCVE really needs to provide a small data sample that others can work with in order to solve your problem. We are not sitting at your computer and don't have access to data you do not give us within the content of your question.
– Neil Lunn
Nov 13 at 21:43
Sample data added
– S Mev
Nov 14 at 21:58
So you do realize you actually making usage of knowledge you gained from an answer here without actually acknowleging that source (yes that's me) by accepting the answer. You really should not be doing that here if you expect ongoing help. You also seem to have missed the point of what that answer was actually telling you.
– Neil Lunn
Nov 15 at 1:05
add a comment |
"I have a schema with several nested arrays" -- Well aside from this being a very bad point of design, you have actually omitted any sample document(s) needed to reproduce your "expected result" from the question. In order to allow someone to "reproduce" and then work to a desired solution. We need 1, Your "full" code and not just single line statements about what you tell us you have done. 2 Actual data capable of reproducing the current and expected results. See How to create a Minimal, Complete, and Verifiable example
– Neil Lunn
Nov 12 at 20:38
@Neil Lunn. Very grateful in pointing it out. how can i break the structure up and still achieve the crud operation effectively? I am seriously open to any help that can help improve my knowledge in this regard. i'm coming from sql background.
– S Mev
Nov 13 at 21:22
Whilst some code is an improvement, you are still not providing any "data" in the question in order to "reproduce the results" as in what you presently get and what you expect to get. An MCVE really needs to provide a small data sample that others can work with in order to solve your problem. We are not sitting at your computer and don't have access to data you do not give us within the content of your question.
– Neil Lunn
Nov 13 at 21:43
Sample data added
– S Mev
Nov 14 at 21:58
So you do realize you actually making usage of knowledge you gained from an answer here without actually acknowleging that source (yes that's me) by accepting the answer. You really should not be doing that here if you expect ongoing help. You also seem to have missed the point of what that answer was actually telling you.
– Neil Lunn
Nov 15 at 1:05
"I have a schema with several nested arrays" -- Well aside from this being a very bad point of design, you have actually omitted any sample document(s) needed to reproduce your "expected result" from the question. In order to allow someone to "reproduce" and then work to a desired solution. We need 1, Your "full" code and not just single line statements about what you tell us you have done. 2 Actual data capable of reproducing the current and expected results. See How to create a Minimal, Complete, and Verifiable example
– Neil Lunn
Nov 12 at 20:38
"I have a schema with several nested arrays" -- Well aside from this being a very bad point of design, you have actually omitted any sample document(s) needed to reproduce your "expected result" from the question. In order to allow someone to "reproduce" and then work to a desired solution. We need 1, Your "full" code and not just single line statements about what you tell us you have done. 2 Actual data capable of reproducing the current and expected results. See How to create a Minimal, Complete, and Verifiable example
– Neil Lunn
Nov 12 at 20:38
@Neil Lunn. Very grateful in pointing it out. how can i break the structure up and still achieve the crud operation effectively? I am seriously open to any help that can help improve my knowledge in this regard. i'm coming from sql background.
– S Mev
Nov 13 at 21:22
@Neil Lunn. Very grateful in pointing it out. how can i break the structure up and still achieve the crud operation effectively? I am seriously open to any help that can help improve my knowledge in this regard. i'm coming from sql background.
– S Mev
Nov 13 at 21:22
Whilst some code is an improvement, you are still not providing any "data" in the question in order to "reproduce the results" as in what you presently get and what you expect to get. An MCVE really needs to provide a small data sample that others can work with in order to solve your problem. We are not sitting at your computer and don't have access to data you do not give us within the content of your question.
– Neil Lunn
Nov 13 at 21:43
Whilst some code is an improvement, you are still not providing any "data" in the question in order to "reproduce the results" as in what you presently get and what you expect to get. An MCVE really needs to provide a small data sample that others can work with in order to solve your problem. We are not sitting at your computer and don't have access to data you do not give us within the content of your question.
– Neil Lunn
Nov 13 at 21:43
Sample data added
– S Mev
Nov 14 at 21:58
Sample data added
– S Mev
Nov 14 at 21:58
So you do realize you actually making usage of knowledge you gained from an answer here without actually acknowleging that source (yes that's me) by accepting the answer. You really should not be doing that here if you expect ongoing help. You also seem to have missed the point of what that answer was actually telling you.
– Neil Lunn
Nov 15 at 1:05
So you do realize you actually making usage of knowledge you gained from an answer here without actually acknowleging that source (yes that's me) by accepting the answer. You really should not be doing that here if you expect ongoing help. You also seem to have missed the point of what that answer was actually telling you.
– Neil Lunn
Nov 15 at 1:05
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53268889%2fmongodb-accurate-sum-for-filter-and-map-or-unwind%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53268889%2fmongodb-accurate-sum-for-filter-and-map-or-unwind%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
"I have a schema with several nested arrays" -- Well aside from this being a very bad point of design, you have actually omitted any sample document(s) needed to reproduce your "expected result" from the question. In order to allow someone to "reproduce" and then work to a desired solution. We need 1, Your "full" code and not just single line statements about what you tell us you have done. 2 Actual data capable of reproducing the current and expected results. See How to create a Minimal, Complete, and Verifiable example
– Neil Lunn
Nov 12 at 20:38
@Neil Lunn. Very grateful in pointing it out. how can i break the structure up and still achieve the crud operation effectively? I am seriously open to any help that can help improve my knowledge in this regard. i'm coming from sql background.
– S Mev
Nov 13 at 21:22
Whilst some code is an improvement, you are still not providing any "data" in the question in order to "reproduce the results" as in what you presently get and what you expect to get. An MCVE really needs to provide a small data sample that others can work with in order to solve your problem. We are not sitting at your computer and don't have access to data you do not give us within the content of your question.
– Neil Lunn
Nov 13 at 21:43
Sample data added
– S Mev
Nov 14 at 21:58
So you do realize you actually making usage of knowledge you gained from an answer here without actually acknowleging that source (yes that's me) by accepting the answer. You really should not be doing that here if you expect ongoing help. You also seem to have missed the point of what that answer was actually telling you.
– Neil Lunn
Nov 15 at 1:05