I am trying to

1. get all the ids for which the period.startDate > sysdate
2. get all the ids for which the period.endDate < sysdate

from the JSON.

[
    {
        "id": 1,
        "period":
        [
            {
                "startDate": "2020-05-05",
                "endDate": "2020-05-06"
            },
            {
                "startDate": "2020-06-05",
                "endDate": "2020-06-06"
            }
        ]
    },
    {
        "id": 2,
        "period":
        [
            {
                "startDate": "2024-07-05",
                "endDate": "2024-07-06"
            },
            {
                "startDate": "2024-08-05",
                "endDate": "2024-08-06"
            }
        ]
    }
]

I have tried to go far as below aggregation:

[{
    $project: {
        _id: 0,
        sId: '$id',
        period: 1
    } }, {
    $unwind: {
        path: '$period',
        includeArrayIndex: 'index'
    } }, {
    $group: {
        _id: '$sId',
        minDate: {
            $min: '$periods.startDate'
        }
    } }, {
    $project: {
        storeId: '$_id',
        _id: 0,
        minDated: {
            $dateFromString: {
                dateString: '$minDate'
            }
        },
        today: ISODate('2022-08-03T11:37:03.954Z')
    } }]

CodePudding user response：

One option is using $reduce and $group:

db.collection.aggregate([
  {$project: {
      _id: 0,
      id: 1,
      minDate: {
        $dateFromString: {
          dateString: {
            $reduce: {
              input: "$period",
              initialValue: {$first: "$period.startDate"},
              in: {$min: ["$$value", "$$this.startDate"]}
            }
          }
        }
      },
      maxDate: {
        $dateFromString: {
          dateString: {
            $reduce: {
              input: "$period",
              initialValue: {$first: "$period.endDate"},
              in: {$max: ["$$value", "$$this.startDate"]}
            }
          }
        }
      }
    }
  },
  {$group: {
      _id: 0,
      startDateLargerIds: {
        $push: {
          $cond: [{$gt: ["$minDate", ISODate("2022-08-03T11:37:03.954Z")]}, 
                  "$id", "$$REMOVE"]}
      },
      endDateSmallerIds: {
        $push: {
          $cond: [{$lt: ["$maxDate", ISODate("2022-08-03T11:37:03.954Z")]},
                  "$id", "$$REMOVE"]}
      }
    }
  },
  {$unset: "_id"}
])

See how it works on the playground example