Hey all, I’m a beginner with using MongoDB and I’m having trouble trying to write two aggregate queries performing the operations I need. I have a collection of documents formatted as follows (provided below is some made up data):
{'_id': 1234,
'name': 'name',
'difficulty': 2345,
'chart': [{'time': 0.0, 'step': '1010'},
{'time': 0.115, 'step': '0101'},
{'time': 0.346, 'step': '0011'},
{'time': 0.404, 'step': '1000'},
{'time': 0.462, 'step': '0100'},
{'time': 0.521, 'step': '0001'},
{'time': 0.579, 'step': '0010'},
{'time': 0.618, 'step': '1000'},
{'time': 0.657, 'step': '0001'},
{'time': 0.696, 'step': '0110'},
{'time': 0.813, 'step': '1101'},
{'time': 0.929, 'step': '1010'},
{'time': 0.968, 'step': '0100'},
{'time': 1.007, 'step': '0001'},
{'time': 1.046, 'step': '0110'},
{'time': 1.104, 'step': '1000'},
{'time': 1.163, 'step': '0001'},
{'time': 1.221, 'step': '0100'},
{'time': 1.28, 'step': '1011'},
{'time': 1.513, 'step': '0001'},
{'time': 1.571, 'step': '1000'},
{'time': 1.63, 'step': '0110'},
{'time': 1.688, 'step': '0001'},
{'time': 1.746, 'step': '1010'},
{'time': 1.863, 'step': '1010'},
{'time': 1.98, 'step': '1010'},
{'time': 2.097, 'step': '1010'},
{'time': 2.213, 'step': '0111'},
{'time': 2.447, 'step': '0110'},
{'time': 2.486, 'step': '0001'},
{'time': 2.525, 'step': '1000'},
{'time': 2.593, 'step': '0100'},
{'time': 2.641, 'step': '0001'},
{'time': 2.68, 'step': '1010'},
{'time': 2.797, 'step': '1011'},
{'time': 2.914, 'step': '1011'},
{'time': 3.03, 'step': '1011'},
{'time': 3.147, 'step': '0100'},
{'time': 3.206, 'step': '1000'},
{'time': 3.264, 'step': '0010'},
{'time': 3.322, 'step': '0001'},
{'time': 3.381, 'step': '1000'},
{'time': 3.42, 'step': '0010'},
{'time': 3.458, 'step': '0001'},
{'time': 3.497, 'step': '0100'},
{'time': 3.536, 'step': '1000'},
{'time': 3.575, 'step': '0001'},
{'time': 3.614, 'step': '0010'},
{'time': 3.673, 'step': '0100'},
{'time': 3.731, 'step': '1000'},
{'time': 3.789, 'step': '0010'},
{'time': 3.848, 'step': '0101'},
{'time': 3.906, 'step': '1000'},
{'time': 3.964, 'step': '0010'},
{'time': 4.018, 'step': '0100'},
{'time': 4.062, 'step': '1011'},
{'time': 4.296, 'step': '1100'},
{'time': 4.412, 'step': '1010'},
{'time': 4.471, 'step': '0101'},
{'time': 4.529, 'step': '1010'},
{'time': 4.646, 'step': '0001'},
{'time': 4.675, 'step': '0010'},
{'time': 4.704, 'step': '0100'},
{'time': 4.762, 'step': '1000'},
{'time': 4.792, 'step': '0010'},
{'time': 4.821, 'step': '0100'},
{'time': 4.879, 'step': '0001'},
{'time': 4.908, 'step': '0010'},
{'time': 4.938, 'step': '0100'},
{'time': 4.996, 'step': '1001'},
{'time': 5.229, 'step': '0100'},
{'time': 5.268, 'step': '0010'},
{'time': 5.307, 'step': '0001'},
{'time': 5.346, 'step': '1000'},
{'time': 5.385, 'step': '0100'},
{'time': 5.424, 'step': '0010'},
{'time': 5.463, 'step': '1001'},
{'time': 5.58, 'step': '0010'},
{'time': 5.696, 'step': '0100'},
{'time': 5.755, 'step': '0010'},
{'time': 5.813, 'step': '1001'},
{'time': 5.871, 'step': '0010'},
{'time': 5.93, 'step': '1100'},
{'time': 6.046, 'step': '1110'},
{'time': 6.267, 'step': '1101'},
{'time': 6.515, 'step': '1011'},
{'time': 6.737, 'step': '0111'},
{'time': 6.854, 'step': '1111'},
{'time': 7.087, 'step': '1000'},
{'time': 7.116, 'step': '0001'},
{'time': 7.145, 'step': '0100'},
{'time': 7.175, 'step': '0010'},
{'time': 7.204, 'step': '1000'},
{'time': 7.233, 'step': '0001'},
{'time': 7.262, 'step': '0100'},
{'time': 7.291, 'step': '0010'},
{'time': 7.32, 'step': '1000'}],
'index': 3456}
For the first aggregate query, my goal is to build out an output formatted as follows:
[0.404, 0.346, 0.347, 0.231, ...] # length = 144 (number of 1's in chart)
Explanation: Going into chart, we look at all “1” in step (from left to right), identify the next time where the “1” appears in the same index, and compute the difference of the two times.
First example: at time 0.0, ‘1’ appears in the first index. The next time “1” appears as the first index is at time 0.404, so the first element of the expected result is computed as 0.404 - 0.
Second example: at time 0.115, ‘1’ appears in the second index. The next time “1” appears as the second index is at time 0.462, so the third element of the expected result is computed as 0.462 - 0.115.
For the second aggregate query, using:
partition = np.array([-0.117, -0.083, -0.050, 0.050, 0.118])
weights = np.array([0.1, 0.5, 1, 0.5])
my goal is to build out an output formatted as follows:
[3, 3, 2.2, 2.2, ...] # length = 144 (number of 1's in chart)
Explanation: Going into chart, we look at all “1” in step (from left to right), put partition on the corresponding time to see which elements to consider in the computation. Count the number of “1” in the filtered result using weights.
First example: at time 0.0, ‘1’ appears in the first index. At time 0.0, we apply the partition as a mask so that all steps between times -0.117 and -0.083 will receive +0.1 points, -0.083 to -0.050 will receive +0.5 points, -0.050 to 0.050 will receive +1 points, and 0.050 to 0.118 will receive +0.5 points. In this case: 1+ 1 + 0.5 + 0.5 = 3.
Second example: at time 0.115, ‘1’ appears in the second index. At time 0.115, we apply the partition as a mask so that all steps between times -0.002 and 0.032 will receive +0.1 points, 0.032 to 0.065 will receive +0.5 points, 0.065 to 0.165 will receive +1 points, and 0.165 to 0.233 will receive +0.5 points. In this case: 0.1 + 0.1 + 1+ 1 = 2.2
Can MongoDB perform these advanced queries? I tried using windows but I haven’t been successful in doing so. Any help would be greatly appreciated 
Thanks!