MongoDB conditional search with parent document and nested docs in array

Question

Let's say I have a mongodb "schema" like this:

offers: Schema = {
      name: String,
      validityStart: Date,
      validityEnd: Date,
      customized: [
         {
            validityStart: Date,
            validityEnd: Date,
            user: String
         }
     ]
}

By words: here we have a collection of offers, valid between the dates specified by validityStart and validityEnd. Each offer has a subarray which define that a user can have this offer "customized", i.e. having a longer time to use.

I can query all the offers available with

Offer.find({ $and: [{ validityStart: { $lte: today } }, { validityEnd: { $gte: today } }] })

but If "user" is provided, I need to create a query that show me all offers and show the "customized" ones by filtering via the user field.

An example to show what I want to achieve is this: imagine having some offers for December

[
    {
      name: "Soccer Game 2020",
      validityStart: "2019-12-1",
      validityEnd: "2019-12-31"
    },
    {
      name: "Golf Equipment",
      validityStart: "2019-12-1",
      validityEnd: "2019-12-31"
    }
]

but I let the user FOO to be an early bird of 1 month for the soccer game, I'll have:

[
    {
      name: "Soccer Game 2020",
      validityStart: "2019-12-1",
      validityEnd: "2019-12-31",
      customized:[
        {
           validityStart: "2019-11-1",
           validityEnd: "2019-11-30",
           user: "FOO"
        }
      ]
    },
    ---
]

How to return for all the user the "parent" dates but for some user, specified by the customized array, their respective dates? Like:

[
    {
      name: "Soccer Game 2020",
      validityStart: "2019-11-1",
      validityEnd: "2019-11-30"
    },
    {
      name: "Golf Equipment",
      validityStart: "2019-12-1",
      validityEnd: "2019-12-31"
    }
]

I dunno how to create a query like this.

I'm available to change the schema, I'm in the early stage of developments so it's not a problem.

So if a user is provided you wish to return the dates from customized field for that particular offer and rest of the offers would have standard validity dates? Can you update the question with what would a result in above example for user "FOO" would look like for better visibility. — ambianBeing
– ambianBeing, Commented Nov 7, 2019 at 16:55
@ambianBeing exactly as you stated. I edited the question accordingly — Valerio
– Valerio, Commented Nov 8, 2019 at 8:01
Have posted an answer taking a bigger use case. Try it out ..see if that helps. — ambianBeing
– ambianBeing, Commented Nov 8, 2019 at 13:37

ambianBeing · Accepted Answer · 2019-11-08 13:35:06Z

This turned out to be little more verbose than I would've liked for a solution, but gets the job done. Besides would only suggest to store the dates as date objects in the documents if they are currently strings.

Query:

db.collection.aggregate([
  {
    $addFields: {
      userCustomOffer: {
        $filter: {
          input: "$customized",
          as: "elem",
          cond: {
            $eq: ["$$elem.user", "FOO"]
          }
        }
      }
    }
  },
  {
    $project: {
      name: 1,
      validityStart: {
        $cond: {
          if: {
            $or: [
              {
                $eq: ["$userCustomOffer", []]
              },
              {
                $eq: ["$userCustomOffer", null]
              }
            ]
          },
          then: "$validityStart",
          else: {
            $arrayElemAt: ["$userCustomOffer.validityStart", 0]
          }
        }
      },
      validityEnd: {
        $cond: {
          if: {
            $or: [
              {
                $eq: ["$userCustomOffer", []]
              },
              {
                $eq: ["$userCustomOffer", null]
              }
            ]
          },
          then: "$validityEnd",
          else: {
            $arrayElemAt: ["$userCustomOffer.validityEnd", 0]
          }
        }
      }
    }
  }
]);

Sample docs taken as use case:

[
  {
    name: "Soccer Game 2020",
    validityStart: "2019-12-1",
    validityEnd: "2019-12-31",
    customized: [
      {
        validityStart: "2019-11-1",
        validityEnd: "2019-11-30",
        user: "FOO"
      },
      {
        validityStart: "2019-10-1",
        validityEnd: "2019-10-31",
        user: "BAZ"
      }
    ]
  },
  {
    name: "Golf Equipment",
    validityStart: "2019-12-1",
    validityEnd: "2019-12-31",
    customized: [
      {
        validityStart: "2019-11-1",
        validityEnd: "2019-11-30",
        user: "BAR"
      }
    ]
  },
  {
    name: "Hockey Kit",
    validityStart: "2019-01-1",
    validityEnd: "2019-02-28"
  }
]

Query Results:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "name": "Soccer Game 2020",
    "validityEnd": "2019-11-30",
    "validityStart": "2019-11-1"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "name": "Golf Equipment",
    "validityEnd": "2019-12-31",
    "validityStart": "2019-12-1"
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "name": "Hockey Kit",
    "validityEnd": "2019-02-28",
    "validityStart": "2019-01-1"
  }
]

this is perfect. Is so brilliant, I've tanned a little bit. Thanks!!!

Collectives™ on Stack Overflow

MongoDB conditional search with parent document and nested docs in array

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related