search

stijnsanders / TMongoWire

Delphi MongoDB driver
MIT License
102 stars 37 forks source link

Run aggregate command #41

Closed scaleman114 closed 7 years ago

scaleman114 commented 7 years ago

I have tried to do this but to no avail! Could you give me an example of an 'aggregate' command please. This is the command in mongochef: db.invoice.aggregate( // Pipeline [ // Stage 1 { $group: { _id : "$customer.custNo", company: {$addToSet: "$customer.company"}, balance: { $sum: { $subtract:["$grandTotal","$paid"] } }, count: { $sum: 1 } } },

]

);

stijnsanders commented 7 years ago

Assuming your TMongoWire instance reference is called MongoWire1, try this:

MongoWire1.RunCommand(JSON([
  'aggregate':'invoice' //or a variable holding the collection name
  'pipeline',VarArrayOf([
    JSON(['$group{','_id','$customer.custNo',
      'company{','$addToSet','$customer.company','}',
      'balance{','$sum{','$subtract',VarArrayOf(['$grandTotal','$paid']),'}}',
      'count{','$sum',1,'}',
    '}}'
  ])
]))['result'] //assuming ['ok']=1
scaleman114 commented 7 years ago

Had to make a couple of changes (the colon after aggregate, missing brackets etc.) and came up with the code below. But still not working. I'm getting: 'Could not convert variant of type (Array Variant) into type (OleStr)'. Any ideas?

vDocument := Wire.RunCommand(BSON(['aggregate','iinvoice','pipeline', VarArrayOf([BSON(['$group{','_id','$customer.custNo','company{','$addToSet', '$customer.company','}','balance{','$sum{','$subtract', VarArrayOf(['$grandTotal','$paid']),'}}','count{','$sum',1,'}','}}'])])[1]]));

stijnsanders commented 7 years ago

If you haven't updated to the most recent version with jsonDoc, and are still using BSON, the embedded document syntax is different, to avoid confusion, you'd better do this:

vDocument := Wire.RunCommand(BSON(['aggregate','iinvoice','pipeline',
VarArrayOf([BSON(['$group',BSON(['_id','$customer.custNo','company',BSON(['$addToSet',
'$customer.company',]),'balance',BSON(['$sum',BSON(['$subtract',
VarArrayOf(['$grandTotal','$paid'])])]),'count',BSON(['$sum',1])])])]))['result'];
scaleman114 commented 7 years ago

Once again thanks for all your help, there were a couple of typos but all works fine. What would be the advantages of upgrading to the jsonDoc version?

stijnsanders commented 7 years ago

jsonDoc should perform a little better in general, and has a few more tricks to traverse through arrays with documents with using more of the same memory, which should also improve performance