Querying 2 arrays based on specific indices in Mongo

user_11808204 0
Viewed 22

I am trying to pull data from 2 arrays (date and price time series) above a certain date cutoff oldest using pymongo.

date_price_collection.aggregate([
        { '$match': { 'ticker': ticker } },
        {
            '$project': {
                'dates': { '$gte': ['dates', oldest] },
                #prices to match these dates
            },
        },
    ],
);

The way data is organized is there are 2 arrays, one for dates and one for prices of the same length. How can I pull prices as well that correspond to dates > oldest?

Thank you very much for any advice!

pymongo mongodb python
1 Answers

What you could do is first $filter the date array, then use $slice to match the proper prices, like so:

const oldest = ...date;

db.collection.aggregate([
  {
    "$addFields": {
      "dates": {
        $filter: {
          input: "$dates",
          as: "date",
          cond: {
            $gte: [
              "$$date",
              oldest
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      dates: 1,
      prices: {
        $slice: [
          "$prices",
          {
            "$multiply": [
              -1,
              {
                $size: "$dates"
              }
            ]
          },
          2
        ]
      }
    }
  }
])

Mongo Playground

user_107703700
Related