[{
"_id": {
"$oid": "5fb92c5d256cb4fd463f0944"
},
"ppCode": "A0007",
"ppName": "ANGULLIA PARK OFF STREET",
"parkingSystem": "Electronic Parking System",
"vehCat": "Car",
"availableLot": 210,
"parkCapacity": 268,
"startTime": 700,
"endTime": 1100,
"rates": [
{
"weekdayMin": 30,
"weekdayRate": 1.3,
"satdayMin": 30,
"satdayRate": 1.3,
"sunPHMin": 30,
"sunPHRate": 0.7
}
],
"modified": "2333"
}, {
"_id": {
"$oid": "5fb92c65256cb4fd463f0ac3"
},
"ppCode": "Y0019",
"ppName": "YAN KIT ROAD / CANTONMENT ROAD OFF ST",
"parkingSystem": "Electronic Parking System",
"vehCat": "Motorcycle",
"availableLot": 0,
"parkCapacity": 8,
"startTime": 2230,
"endTime": 700,
"rates": [
{
"weekdayMin": 510,
"weekdayRate": 0.65,
"satdayMin": 510,
"satdayRate": 0.65,
"sunPHMin": 510,
"sunPHRate": 0.65
}
],
"modified": "2333"
}]
Above is my MongoDB document. I am trying to replicate what I have done in python using the aggregation. Below was what I tried with. But due to it being inside an array object, multiplying can't work when I use rates.0.weekdayRate and when I used the below query, I get a null value return instead.
Python Code:
result = carparkCollection.find_one({"ppName": ppname, "vehCat": mvtype})
for r in result["rates"]:
weekday = (parktime / r["weekdayMin"]) * r['weekdayRate']
db.carpark.aggregate(
[
{
$project:
{
"_id": 1,
"ppName": 1,
"weekdayMin": {
$multiply: [
{
$divide: [
{
$multiply: [9, 60]
},
"$rates[0].weekdayMin"
]
},
"$rates[0].weekdayRate"]
}
}
}
]
)
Please help me! thanks
If you only are interested in the first array element, use $unwind
at the start of the aggregation to convert the array to an object; then you can access the fields using the dot notation.
parktime = 10
results = db.carpark.aggregate([
{'$unwind':'$rates'},
{'$project': {
'_id': 1,
'ppName': 1,
'weekdayMin': {
'$multiply': [
{'$divide': [
parktime,
'$rates.weekdayMin'
]},
'$rates.weekdayRate'
]
}
}
}])
Worked example:
from pymongo import MongoClient
db = MongoClient()['mydatabase']
db.carpark.insert_many([{
'ppCode': 'A0007',
'ppName': 'ANGULLIA PARK OFF STREET',
'parkingSystem': 'Electronic Parking System',
'vehCat': 'Car',
'availableLot': 210,
'parkCapacity': 268,
'startTime': 700,
'endTime': 1100,
'rates': [
{
'weekdayMin': 30,
'weekdayRate': 1.3,
'satdayMin': 30,
'satdayRate': 1.3,
'sunPHMin': 30,
'sunPHRate': 0.7
}
],
'modified': '2333'
}, {
'ppCode': 'Y0019',
'ppName': 'YAN KIT ROAD / CANTONMENT ROAD OFF ST',
'parkingSystem': 'Electronic Parking System',
'vehCat': 'Motorcycle',
'availableLot': 0,
'parkCapacity': 8,
'startTime': 2230,
'endTime': 700,
'rates': [
{
'weekdayMin': 510,
'weekdayRate': 0.65,
'satdayMin': 510,
'satdayRate': 0.65,
'sunPHMin': 510,
'sunPHRate': 0.65
}
],
'modified': '2333'
}])
parktime = 10
results = db.carpark.aggregate([
{'$unwind':'$rates'},
{'$project': {
'_id': 1,
'ppName': 1,
'weekday': {
'$multiply': [
{'$divide': [
parktime,
'$rates.weekdayMin'
]},
'$rates.weekdayRate'
]
}
}
}])
print('Aggregate:')
for result in results:
print(result['weekday'])
print('Python:')
results = db.carpark.find()
for result in results:
for r in result["rates"]:
weekday = (parktime / r["weekdayMin"]) * r['weekdayRate']
print(weekday)
Gives:
Aggregate:
0.43333333333333335
0.012745098039215686
Python:
0.43333333333333335
0.012745098039215686
Wow! This is great! Sorry, I am still very new to NoSQL MongoDB. Thank you very much!
what if I have multiple items in the array, for example, I want to sum all the weekdayMin and avg it.
If you're not familiar with all the MongoDB operations, I would sincerely steer you towards just doing the calculations in python.