Warm tip: This article is reproduced from serverfault.com, please click

Using Aggregate function in MongoDB to Divide and Mutliply

发布于 2020-11-28 09:07:21
[{
  "_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

Questioner
der
Viewed
0
Belly Buster 2020-11-28 18:27:49

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