Performing Aggregate on Two Collections

Hey guys, have a challenge for the experienced Mongo guys out there.

Have two collections, A and B, each a time series. Following is the schema:

/** Document layout for BTM4208SD */
const document = {
  time: Date,
  channel1: Number || null, // Temperature
  channel2: Number || null,
  channel3: Number || null,
  channel4: Number || null,
  channel5: Number || null,
  channel6: Number || null,
  channel7: Number || null,
  channel8: Number || null,
  channel9: Number || null,
  channel10: Number || null,
  channel11: Number || null,
  channel12: Number || null,
};

We display this data on the client with dygraphs which requires the time series data to be in the following format:

[
  [ new Date("2009/07/12"), 20, 24, 23 ],
  [ new Date("2009/07/19"), 18, 16, 22 ]
]

I have an aggregate that will produce the data in the above format for 1 collection at a time. Works great. However, I need to combine data from both collections in the above format using one aggregate. For examples:

[
  [ 
    new Date("2009/07/12"), 
    20, // (A collection, channel 1) 
    24, // (B collection, channel 4) 
    .... 
  ],
  [ 
    new Date("2009/07/19"),  
    18, // (A collection, channel 1) 
    16, // (B collection, channel 4) 
    ... 
  ]
]

How can I do this? I believe I need a way to combine the two collections and do some sort of conditional statement like:

if (collection = A)
select { channel1 }

if (collection = B)
select { channel2 }

Any suggestions?
I can change the schema if needs be.

You want the aggregation stage called $lookup

1 Like

Thanks, Jack.

My localField and foreignField are timestamps recorded every 1 or 2 seconds, they may match.

Can I perform an aggregation on my from collection first in a $lookup ?

The aggregation on my from collection would group documents by the hour, adding a field hour. Then perform the same aggregation on destination collection. My localField and foreignField would be the new hour field.

I think to achieve your objectives you’ll need to work diligently through the $lookup documentation and especially the examples. MongoDB documentation is rather “thick” but all the information is there.

1 Like

All good. Thank you.