Hi,
I have a follow up question regarding my last topic (here) that was brilliantly answered by @John_Sewell.
How can I forward the ‘sold’ and ‘bought’ group so that they are available in the final result?
You’ll want to carry the down down when it’s all grouped up, something like this…I just threw this together but it should give you can idea:
db.getCollection("token_balances").aggregate([
{
$match:{
$or:[
{"token_bought_address":"0xBB"},
{"token_sold_address":"0xBB"}
]
}
},
{
$facet:{
sold:[
{
$match:{
"token_sold_address":"0xBB"
}
},
{
$group:{
_id:'$maker',
total:{$sum:{$multiply:[-1, '$token_sold_amount']}}
}
}
],
bought:[
{
$match:{
"token_bought_address":"0xBB"
}
},
{
$group:{
_id:'$taker',
total:{$sum:'$token_bought_amount'}
}
}
],
}
},
{
$project:{
allItem:{
$setUnion:["$sold","$bought"]
},
soldData:"$sold",
boughtData:"$bought"
}
},
{
$unwind:"$allItem"
},
{
$group:{
_id:'$allItem._id',
total:{$sum:"$allItem.total"},
soldData:{$first:'$soldData'},
boughtData:{$first:'$boughtData'}
}
},
])
Thank you John, but unfortunately it’s not working
await mongoose.model('Trades').aggregate([
{
$match: {
$or: [
{ 'tokenOut.address': address },
{ 'tokenIn.address': address }
]
}
},
{
$facet: {
sold: [
{
$match: {
'tokenIn.address': address
}
},
{
$group: {
_id: '$taker',
total: { $sum: { $multiply: [-1, '$amountIn'] } }
}
}
],
bought: [
{
$match: {
'tokenOut.address': address
}
},
{
$group: {
_id: '$taker',
total: { $sum: '$amountOut' }
}
}
],
}
},
{
$project: {
allItem: {
$setUnion: ['$sold', '$bought']
},
sold: '$sold',
bought: '$bought'
}
},
{
$unwind: '$allItem'
},
{
$replaceRoot: {
newRoot: '$allItem'
}
},
{
$group: {
_id: '$_id',
total: { $sum: '$total' },
sold: { $first: '$sold' },
bought: { $first: '$bought' }
}
},
{
$project: {
_id: 0,
wallet: '$_id',
amount: '$total',
sold: '$sold',
bought: '$bought'
}
}
]);
The result looks like this
[
{
"wallet":"0xb5c86bbda44ece35d2dc8824050a2b217c45a3a4",
"amount":207099169.0671463,
"sold":null,
"bought":null
},
{
"wallet":"0xcf53addc53cce46de839c9c05c05466a8d2249d9",
"amount":4811109993.772441,
"sold":null,
"bought":null
},
{
"wallet":"0xe9ed3ad8e68b3925a33cab867a29c73e8357cfc4",
"amount":198403983902.09695,
"sold":null,
"bought":null
}
]
Thie fields ‘sold’ and ‘bought’ are always empty
The replace root is killing the data that’s not in the child element allItem, you need to not use that, if you note in the above code I left out this stage and referred to the elements under that element instead.
When debugging this kind of thing I REALLY recommend using something like Studio3T in the script window and you can quickly comment out stages of the aggregation pipeline to see where data is and when it disappears.
This goes for debugging things like this as well as performance, where you can work out which stage is causing performance issues (as well as using .explain()!)
Thanks, I will have a look into Studio3T.
I change the code like you recommended, but now the query seems to hang forever and never returns.
As soon as I comment out
sold: { $first: '$sold' },
bought: { $first: '$bought' }
It works perfectly like before. Any idea?
No, that’s a bit weird, can you paste the exact query your using, how much data is flowing down into that stage, i.e. if if you comment out from the group down, how much data is in there?
You could try running an explain() before and after that change to see the difference in the execution plans to pick out anything of interest.
The aggregation has to process about 250.000 data entries.
I can only run explain() before the changes. Afterwards it never returns and even crashes mongod.
Furthermore I have to activate the “allowDiskUse” option, otherwise the aggregation won’t run.
data example
{
_id: new ObjectId("64c6cd10dfdcef2a02effe98"),
eventIndex: 147,
hash: '0xa29142b106b96b68e480d903af13b4b6523b17e3fafb855a14bb8d99e13c3496',
amountIn: 0.056138649184,
amountOut: 56335975.66790488,
blockNumber: 17261504,
blockTime: 1684108799000,
chain: { id: 1, name: 'ethereum' },
contract: '0xa43fe16908251ee70ef74718545e4fe6c5ccec9f',
maker: '0x3001f6f2187d875a1bc24b10fe9616ebcaf4fb45',
project: 'uniswap',
taker: '0x3001f6f2187d875a1bc24b10fe9616ebcaf4fb45',
tokenIn: {
address: '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2',
symbol: 'WETH',
decimals: 18
},
tokenOut: {
address: '0x6982508145454ce325ddbe47a25d4ec3d2311933',
symbol: 'PEPE',
decimals: 18
},
version: 2
}
query
[
{
$match:{
$or:[
{
'tokenOut.address': '0x6982508145454ce325ddbe47a25d4ec3d2311933'
},
{
'tokenIn.address': '0x6982508145454ce325ddbe47a25d4ec3d2311933'
}
]
}
},
{
$facet:{
sold:[
{
$match:{
'tokenIn.address': '0x6982508145454ce325ddbe47a25d4ec3d2311933'
}
},
{
$group:{
_id:'$taker',
total:{
$sum:{
$multiply:[
-1,
'$amountIn'
]
}
}
}
}
],
bought:[
{
$match:{
'tokenOut.address': '0x6982508145454ce325ddbe47a25d4ec3d2311933'
}
},
{
$group:{
_id:'$taker',
total:{
$sum:'$amountOut'
}
}
}
],
}
},
{
$project:{
allItem:{
$setUnion:[
'$sold',
'$bought'
]
},
sold:'$sold',
bought:'$bought'
}
},
{
$unwind:'$allItem'
},
{
$group:{
_id:'$allItem._id',
total:{
$sum:'$allItem.total'
},
sold:{
$first:'$sold'
},
bought:{
$first:'$bought'
}
}
},
{
$project:{
_id:0,
wallet:'$_id',
amount:'$total',
sold:'$sold',
bought:'$bought'
}
}
]
explain (before changes)
{
"explainVersion" : "1",
"stages" : [
{
"$cursor" : {
"queryPlanner" : {
"namespace" : "dex_trades.trades",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"tokenIn.address" : {
"$eq" : "0x6982508145454ce325ddbe47a25d4ec3d2311933"
}
},
{
"tokenOut.address" : {
"$eq" : "0x6982508145454ce325ddbe47a25d4ec3d2311933"
}
}
]
},
"queryHash" : "D2428EAE",
"planCacheKey" : "D9A6C586",
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"winningPlan" : {
"stage" : "SUBPLAN",
"inputStage" : {
"stage" : "PROJECTION_DEFAULT",
"transformBy" : {
"amountIn" : NumberInt(1),
"amountOut" : NumberInt(1),
"taker" : NumberInt(1),
"tokenIn.address" : NumberInt(1),
"tokenOut.address" : NumberInt(1),
"_id" : NumberInt(0)
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "OR",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"tokenIn.address" : NumberInt(1)
},
"indexName" : "tokenIn.address_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"tokenIn.address" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(2),
"direction" : "forward",
"indexBounds" : {
"tokenIn.address" : [
"[\"0x6982508145454ce325ddbe47a25d4ec3d2311933\", \"0x6982508145454ce325ddbe47a25d4ec3d2311933\"]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"tokenOut.address" : NumberInt(1)
},
"indexName" : "tokenOut.address_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"tokenOut.address" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(2),
"direction" : "forward",
"indexBounds" : {
"tokenOut.address" : [
"[\"0x6982508145454ce325ddbe47a25d4ec3d2311933\", \"0x6982508145454ce325ddbe47a25d4ec3d2311933\"]"
]
}
}
]
}
}
}
},
"rejectedPlans" : [
]
}
}
},
{
"$facet" : {
"sold" : [
{
"$internalFacetTeeConsumer" : {
}
},
{
"$match" : {
"tokenIn.address" : {
"$eq" : "0x6982508145454ce325ddbe47a25d4ec3d2311933"
}
}
},
{
"$group" : {
"_id" : "$taker",
"total" : {
"$sum" : {
"$multiply" : [
"$amountIn",
{
"$const" : NumberInt(-1)
}
]
}
}
}
}
],
"bought" : [
{
"$internalFacetTeeConsumer" : {
}
},
{
"$match" : {
"tokenOut.address" : {
"$eq" : "0x6982508145454ce325ddbe47a25d4ec3d2311933"
}
}
},
{
"$group" : {
"_id" : "$taker",
"total" : {
"$sum" : "$amountOut"
}
}
}
]
}
},
{
"$project" : {
"_id" : true,
"allItem" : {
"$setUnion" : [
"$sold",
"$bought"
]
},
"sold" : "$sold",
"bought" : "$bought"
}
},
{
"$unwind" : {
"path" : "$allItem"
}
},
{
"$group" : {
"_id" : "$allItem._id",
"total" : {
"$sum" : "$allItem.total"
}
}
},
{
"$project" : {
"wallet" : "$_id",
"amount" : "$total",
"sold" : "$sold",
"bought" : "$bought",
"_id" : false
}
}
],
"serverInfo" : {
"host" : "DeFiHub",
"port" : NumberInt(27017),
"version" : "6.0.8",
"gitVersion" : "3d84c0dd4e5d99be0d69003652313e7eaf4cdd74"
},
"serverParameters" : {
"internalQueryFacetBufferSizeBytes" : NumberInt(104857600),
"internalQueryFacetMaxOutputDocSizeBytes" : NumberInt(104857600),
"internalLookupStageIntermediateDocumentMaxSizeBytes" : NumberInt(104857600),
"internalDocumentSourceGroupMaxMemoryBytes" : NumberInt(104857600),
"internalQueryMaxBlockingSortMemoryUsageBytes" : NumberInt(104857600),
"internalQueryProhibitBlockingMergeOnMongoS" : NumberInt(0),
"internalQueryMaxAddToSetBytes" : NumberInt(104857600),
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : NumberInt(104857600)
},
"command" : {
"aggregate" : "trades",
"pipeline" : [
{
"$match" : {
"$or" : [
{
"tokenOut.address" : "0x6982508145454ce325ddbe47a25d4ec3d2311933"
},
{
"tokenIn.address" : "0x6982508145454ce325ddbe47a25d4ec3d2311933"
}
]
}
},
{
"$facet" : {
"sold" : [
{
"$match" : {
"tokenIn.address" : "0x6982508145454ce325ddbe47a25d4ec3d2311933"
}
},
{
"$group" : {
"_id" : "$taker",
"total" : {
"$sum" : {
"$multiply" : [
NumberInt(-1),
"$amountIn"
]
}
}
}
}
],
"bought" : [
{
"$match" : {
"tokenOut.address" : "0x6982508145454ce325ddbe47a25d4ec3d2311933"
}
},
{
"$group" : {
"_id" : "$taker",
"total" : {
"$sum" : "$amountOut"
}
}
}
]
}
},
{
"$project" : {
"allItem" : {
"$setUnion" : [
"$sold",
"$bought"
]
},
"sold" : "$sold",
"bought" : "$bought"
}
},
{
"$unwind" : "$allItem"
},
{
"$group" : {
"_id" : "$allItem._id",
"total" : {
"$sum" : "$allItem.total"
}
}
},
{
"$project" : {
"_id" : NumberInt(0),
"wallet" : "$_id",
"amount" : "$total",
"sold" : "$sold",
"bought" : "$bought"
}
}
],
"allowDiskUse" : true,
"maxTimeMS" : NumberLong(0),
"cursor" : {
},
"$db" : "dex_trades"
},
"ok" : 1.0
}
Nobody has an idea what could be wrong?
Do you have an anonymised sample dataset I can play with? That represents what the data looks like?
Sure. It’s not allowed to upload zip files therefore I used WeTransfer
The dataset contains about 10.000 items
Sorry Florian, I’ve been tied up with work the last few days, I did have a play with the data yesterday but didnt get far.
There is a fair amount of data flowing through, one thought I had was if you need to get this data at the same time as the breakdown? If it’s for a drilldown then perhaps call a different query for the drilldown to show details as opposed to getting this for all records on every call?
It’s working now. I reworked the aggregation pipeline
[
{
$match: {
'tokenOut.address': '0x6982508145454ce325ddbe47a25d4ec3d2311933'
}
},
{
$project: {
_id: 0,
taker: 1,
bought: '$amountIn'
}
},
{
$unionWith: {
coll: 'trades',
pipeline: [
{
$match: {
'tokenIn.address': '0x6982508145454ce325ddbe47a25d4ec3d2311933'
}
},
{
$project: {
_id: 0,
taker: 1,
sold: '$amountOut'
}
},
]
}
},
{
$group: {
_id: '$taker',
bought: { $sum: '$bought' },
sold: { $sum: '$sold' }
},
},
{
$sort: {
bought: -1
}
},
{
$limit: 100
},
{
$project: {
_id: 0,
wallet: '$_id',
bought: 1,
sold: 1,
roi: {
$round: [
{
$multiply: [
{
$divide: [100, '$bought'],
},
{
$subtract: ['$sold', '$bought']
}
]
}
]
}
}
},
]
1 Like
Excellent, glad you got it working!
2 Likes
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.