Sebastian Araneda
Sebastian Araneda

Reputation: 113

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

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:

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:

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"
    }
}

Upvotes: 0

Views: 794

Answers (1)

wak786
wak786

Reputation: 1615

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))

Upvotes: 1

Related Questions