For anyone else that was wondering about this, you can find the solution below. Finally started to read the mongoDB aggregation documentation. The key conclusions are that any pipeline in a deeper nest (such as in lookup aggregation operators, relies on the parse-server
and default mongoDB
naming convention with _id
while for the first operators you need to comply with the Moralis API also make sure to explictly use $group
using the dollar sign, since it is not a key for a Moralis API compliant interface but an exact MongoDB query. (If using Typescript simply autocomplete or read the documentation about queries)
In short this query goes through the document collection āAā and filters by the field day, then unwinds the array of all daily accounts (pointers) and looksup each account in the document collection (B), where it applies the pipeline that is provided. The secondary pipeline unwinds the snapshots in B, goes through each tuple and finds the matching first element (corresponding to day) and then returns the object reformatted through a projection.
Iām sure this is going to safe some folks lotās of time since the data to find this was spread across hundreds of forum posts.
ps: Whenever you execute a aggregate query, check the Moralis dashboard logs to find the exact error message.
import Moralis from "moralis/node"
const testPipeline = async () => {
const pipeline = [
{
match: {
day: 2
// day: {$gt: 1, $lt: 4}
// day: {$gt: 195, $lt: 202}
},
},
{
unwind: "$accounts"
},
{
project: {
objectId: 0,
day: 1,
}
},
{
lookup: {
from: "B",
// Do not use localField or foreignField in combination with pipeline
// localField: "accounts.objectId", // Use original root document structure
// foreignField: "objectId",
as: "accountData",
let: {
day: "$day"
},
pipeline: [
{
$unwind: '$snapshots'
},
{
$match: {
$expr: {
$eq: [
{$arrayElemAt: ["$snapshots",0]},
"$$day"
]
}
}
},
{
$project: {
"day": {
$first: "$snapshots"
},
address: 1,
"snapshotData": {
$arrayElemAt: ["$snapshots", 1]
}
}
},
{
$project: {
// objectId: 0,
_id: 0,
address: 1,
buyVolume: "$snapshotData.buyVolume",
sellVolume: "$snapshotData.sellVolume",
buys: "$snapshotData.buys",
sells: "$snapshotData.sells",
}
},
{
$limit: 2
},
]
}
},
{
group: {
objectId: "$day",
// day: "$day",
accountData: {
$push: "$accountData"
}
}
},
{
// Access objectId through Moralis interface for "project" operator with objectId (but internal field name is _id)
project: {
objectId: 0,
day: "$_id",
// day: "$day",
accountData: 1
}
}
]
const result = (new Moralis.Query("A")).aggregate(pipeline, {useMasterKey: true})
}