Sum MongoDB column using aggregate - result has no property for the sum

Question

I'm fairly new to MongoDB and Node and I came upon a problem developing a REST API.

The problem comes with the "Aggregate" function not properly returning results. After receiving the HTTP instruction, my API goes to run the specified command, the function that I'm using for this is:

const getDataAsync = async function getDataAsync(data) {


if (enabled !== true) { return {type: "Error", message: "Database not connected"}}
  const operand = data.operand;
  delete data.operand;

if (operand === "sum"){
      clientHandler.collection("Sales2").aggregate([
          {"_id": null, "total": {$sum: "Sale Gross Value"}}],
        function (err, result) {console.log(result)});
    }
}

But "result", gives out some weird data without the property "total". So far, based on other answers, I have tried:

Trying to read result[0] (Returns undefined)
Switching to (...).ToArray() instead of a second argument callback function.

Also, something important to note is that I replaced this line for a findOne (just to check that it is accessing the DB at this point) and returns OK.

Any help is appreciated, I'll dump all the output of log(result) in case someone knows how to interpret it.

PD: I know my code is crappy for an API because it is deeply hardcoded, it's just that I want it to work for this specific case before I try to open it up for a more parametric approach

EDIT: Answering some of the question comments:

I'm using _id: null because I saw it on a tutorial, I tried the query on mongo shell and it works.
I'll add some sample document at the end.

console.log(results):

    AggregationCursor {
  _readableState: ReadableState {
    objectMode: true,
    highWaterMark: 16,
    buffer: BufferList { head: null, tail: null, length: 0 },
    length: 0,
    pipes: [],
    flowing: null,
    ended: false,
    endEmitted: false,
    reading: false,
    sync: true,
    needReadable: false,
    emittedReadable: false,
    readableListening: false,
    resumeScheduled: false,
    errorEmitted: false,
    emitClose: true,
    autoDestroy: true,
    destroyed: false,
    errored: null,
    closed: false,
    closeEmitted: false,
    defaultEncoding: 'utf8',
    awaitDrainWriters: null,
    multiAwaitDrain: false,
    readingMore: false,
    decoder: null,
    encoding: null,
    [Symbol(kPaused)]: null
  },
  _events: [Object: null prototype] {},
  _eventsCount: 0,
  _maxListeners: undefined,
  operation: AggregateOperation {
    options: { readPreference: [ReadPreference] },
    ns: MongoDBNamespace { db: 'Sales', collection: '$cmd' },
    readPreference: ReadPreference {
      mode: 'primary',
      tags: undefined,
      hedge: undefined
    },
    readConcern: undefined,
    writeConcern: WriteConcern { w: 'majority' },
    explain: false,
    fullResponse: true,
    target: 'Sales2',
    pipeline: [ [Object] ],
    hasWriteStage: false,
    cursorState: {
      cursorId: null,
      cmd: {},
      documents: [],
      cursorIndex: 0,
      dead: false,
      killed: false,
      init: false,
      notified: false,
      limit: 0,
      skip: 0,
      batchSize: 1000,
      currentLimit: 0,
      transforms: undefined,
      raw: undefined,
      reconnect: true
    }
  },
  pool: null,
  server: null,
  disconnectHandler: undefined,
  bson: undefined,
  ns: 'Sales.$cmd',
  namespace: MongoDBNamespace { db: 'Sales', collection: '$cmd' },
  cmd: {},
  options: {
    readPreference: ReadPreference {
      mode: 'primary',
      tags: undefined,
      hedge: undefined
    }
  },
  topology: Server {
    _events: [Object: null prototype] {
      commandStarted: [Function (anonymous)],
      commandSucceeded: [Function (anonymous)],
      commandFailed: [Function (anonymous)],
      serverOpening: [Function (anonymous)],
      serverClosed: [Function (anonymous)],
      serverDescriptionChanged: [Function (anonymous)],
      serverHeartbeatStarted: [Function (anonymous)],
      serverHeartbeatSucceeded: [Function (anonymous)],
      serverHeartbeatFailed: [Function (anonymous)],
      topologyOpening: [Function (anonymous)],
      topologyClosed: [Function (anonymous)],
      topologyDescriptionChanged: [Function (anonymous)],
      joined: [Function (anonymous)],
      left: [Function (anonymous)],
      ping: [Function (anonymous)],
      ha: [Function (anonymous)],
      connectionPoolCreated: [Function (anonymous)],
      connectionPoolClosed: [Function (anonymous)],
      connectionCreated: [Function (anonymous)],
      connectionReady: [Function (anonymous)],
      connectionClosed: [Function (anonymous)],
      connectionCheckOutStarted: [Function (anonymous)],
      connectionCheckOutFailed: [Function (anonymous)],
      connectionCheckedOut: [Function (anonymous)],
      connectionCheckedIn: [Function (anonymous)],
      connectionPoolCleared: [Function (anonymous)],
      authenticated: [Function (anonymous)],
      error: [Array],
      timeout: [Array],
      close: [Array],
      parseError: [Array],
      open: [Array],
      fullsetup: [Array],
      all: [Array],
      reconnect: [Array]
    },
    _eventsCount: 35,
    _maxListeners: Infinity,
    s: {
      coreTopology: [Server],
      sCapabilities: null,
      clonedOptions: [Object],
      reconnect: true,
      emitError: true,
      poolSize: 20,
      storeOptions: [Object],
      store: [Store],
      host: '127.0.0.1',
      port: 27017,
      options: [Object],
      sessionPool: [ServerSessionPool],
      sessions: Set(0) {},
      promiseLibrary: [Function: Promise]
    },
    [Symbol(kCapture)]: false
  },
  cursorState: {
    cursorId: null,
    cmd: {},
    documents: [],
    cursorIndex: 0,
    dead: false,
    killed: false,
    init: false,
    notified: false,
    limit: 0,
    skip: 0,
    batchSize: 1000,
    currentLimit: 0,
    transforms: undefined,
    raw: undefined,
    reconnect: true
  },
  logger: Logger { className: 'Cursor' },
  s: {
    numberOfRetries: 5,
    tailableRetryInterval: 500,
    currentNumberOfRetries: 5,
    state: 0,
    promiseLibrary: [Function: Promise],
    explicitlyIgnoreSession: false
  },
  [Symbol(kCapture)]: false
}

Sample document:

{

   "_id": {
        "$oid": "5f88525a475fc997297fb93f"
    },
    "Date": {
        "$date": "2018-01-01T00:01:00.000Z"
    },
    "Code": "54",
    "Category": "ART._HYGIENIC",
    "Name": "BELLA PODPASKI NOVA MAXI 10",
    "Quantity": {
        "$numberDecimal": "1"
    },
    "Unit Net Cost": {
        "$numberDecimal": "2.66"
    },
    "Sale Net Cost": {
        "$numberDecimal": "2.66"
    },
    "Unit Net Price": {
        "$numberDecimal": "1.85"
    },
    "Sale Net Price": {
        "$numberDecimal": "1.85"
    },
    "Unit Gross Value": {
        "$numberDecimal": "2"
    },
    "Sale Gross Value": {
        "$numberDecimal": "2"
    },
    "Share in Sales %": {
        "$numberDecimal": "0"
    },
    "Margin %": {
        "$numberDecimal": "-43.78"
    },
    "Unit Margin %": {
        "$numberDecimal": "-0.81"
    },
    "Sell Margin %": {
        "$numberDecimal": "-0.81"
    },
    "Share in Margin %": {
        "$numberDecimal": "-0.01"
    }
}

It will help if you add clean and complete JSON sample data. Mongo DB query writers who are not familiar with mongoose can chip in for a solution/Mongo DB query. — Mallik
– Mallik, Commented Oct 16, 2020 at 14:25
@wak786 saw it on a tutorial, I tried it on mongo shell and it works. — Sebastian Araneda
– Sebastian Araneda, Commented Oct 16, 2020 at 14:34
okay. So what i understand is you want get the sum of Sale Gross Value for all the documents? — wak786
– wak786, Commented Oct 16, 2020 at 14:44
@wak786 Exactly, it's a pretty straightforward query. Once I get this one going I think I'll be able to play around with more complex functionality — Sebastian Araneda
– Sebastian Araneda, Commented Oct 16, 2020 at 14:52

wak786 · Accepted Answer · 2020-10-16 18:30:14Z

1

So I came up with this query.

db.collection.aggregate([
  {
    "$group": {
      "_id": null,
      "total": {
        "$sum": "$Sale Gross Value"
      }
    }
  }
])

I have created a mongo playground for this here.

https://mongoplayground.net/p/6nSiotXR960

Disclaimer :not an expert in nodejs :-P

Here is query in nodejs :

        clientHandler.collection("Sales2").aggregate([
        {'$group': {'_id':null, 'total':{'$sum': "$Sale Gross Value"}}}],
        function (err, result) {console.log(result)});

Edit:-

Try this-

clientHandler.collection("Sales2").aggregate([
{'$group': {'_id':null, 'total':{'$sum': "$Sale Gross Value"}}}],
function (err, result) {result.forEach(doc => console.log(doc.total))});

OR this

var aggCursor = clientHandler.collection("Sales2").aggregate([
{'$group': {'_id':null, 'total':{'$sum': "$Sale Gross Value"}}}]);

await aggCursor.forEach(doc => console.log(doc.total))

edited Oct 16, 2020 at 18:30

answered Oct 16, 2020 at 14:58

wak786

1,6351 gold badge10 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sebastian Araneda Over a year ago

I'll give it a try!

Sebastian Araneda Over a year ago

Ok I got the same result (The long output for console.log(result)) and 'undefined' for result.total

wak786 Over a year ago

You are getting the long output and undefined because result is cursor. You will have to iterate over it to fetch the values. I have edited the answer accordingly.

Sebastian Araneda Over a year ago

I tried the new approach but got an error. With the first method I got the error: (node:36716) UnhandledPromiseRejectionWarning: TypeError: result.foreach is not a function The second one I was told it's not recommended because Node is asychronous, so the forEach line is going to run before the clientHandler has finished. Also, one of the things I tried before is accessing results[0], to no avail.

wak786 Over a year ago

Use await before it. Like this await aggCursor.forEach(doc => console.log(doc.total))

|

Collectives™ on Stack Overflow

Sum MongoDB column using aggregate - result has no property for the sum

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related