Best way to index and query total gas consumed by an address/contract

Hi,

I would like to know the best way of indexing data on moralis.

Example use case, I want to query the total amount of gas a contract/address has consumed. From traditional sql i would create a view query with columns address and gas that gets updated every hour.

My front end code will then query the data and display it

I wonder if that is possible with moralis sdk/api

you will have to get the list of transactions for that contract/address, and then to get the gas cost from every transaction

Understand, but I want it to be dynamic such that the user inputs the address and i return the amount of gas consumed. Wonder if thereโ€™s an efficient way to do that on moralis

there isnโ€™t an easy way to do that from what I know
also, it can depend on how many transactions did the wallet address, for example if it did 100k transactions then it will not be easy to iterate over 100k transactions.

1 Like

yeah, i was looking at the docs and it seems the only reasonable method is to query for all the txs (filter address) and process them (sum gas for each tx) individually. hence i was wondering if anyone has a better method to achieve this

Iโ€™m actually trying out project and sum but itโ€™s not working, are you able to help?

  const pipeline = [
    {
      project: {
        "from_address": "0x688e00e1b4dedf25d98b85e56c1d52692f60e6ba",        
      }
    },
    {
      group:
      {
        objectId: null,
        total: { 
          $sum: {"$toInt":"$gas"}
        }
      }
    }
  ]
  const EthTx = Moralis.Object.extend("EthTransactions");
  const query = new Moralis.Query(EthTx)
  const results = await query.aggregate(pipeline)
  console.log(results)

returns

[ { total: 0, objectId: null } ]

expected would be the sum of the gas from all transactions that example address has made

Answering my own question incase someone needs it

Project requires you to bring in the required field (would be useful if the docs specify)

correct pipeline should be

const pipeline = [
    {
      project: {
        "from_address": "0x688e00e1b4dedf25d98b85e56c1d52692f60e6ba",
        gas:1
      },
    },
    {
      group:
      {
        objectId: null,
        total: { 
          $sum: "$gas"
        }
      }
    }
  ]

where gas:1 tells the project statement to return the gas column for the group pipeline to sum