Best model for hospitality availability + rate search

[titani0us (Wes M)]

Hello!

We're brand new to ES. We've quickly outgrown our MySQL implementation and are looking for a must faster and more extensive/robust search solution.

We've been trying to discern the right way to store the data after denormalization as well as the correct way to then query those documents.

Today we have 3 core data objects: The vacation property (id, name, location, photos, etc.), the availability calendar, and the nightly rates.

From what we've read it seems as though it would perhaps be best to store this data in a singular document in a format similar to:

    {
      property_id: 123456,
      name: "The best view ever",
      address: "123 Johnny Appleseed Way",
      ...
      availability: [ 
      {
        date: 2018-09-01,
        available: true
      },
      {
        date: 2018-09-02,
        available: true
      },
      {
        date: 2018-09-03,
        available: false
      },
    {
        date: 2018-09-04,
        available: true
      }
     ...
     ],
     rates: [
     { 
       date: 2018-09-01,
       rate: 125.00,
       type: "nightly"
     },
    { 
       date: 2018-09-02,
       rate: 125.00,
       type: "nightly"
     },
    { 
       date: 2018-09-03,
       rate: 125.00,
       type: "nightly"
     },
    ....
    ]}

Assuming this is an acceptable path to take, we then have the challenge of not understanding the correct approach/syntax to find properties that have all dates with availability = true between a date range as well as returning the average nightly rate for just the date range the traveler searched.

If we have 365 nights of rates, we only want to return the rate information for the nights the traveler searched for. In addition - it's often the average nightly rate we serve now. Is it possible to aggregate and average only the nightly rates between the date range the traveler searched?

Thanks in advance. From what we've read ES seems like it could be the future for our brand but experience/knowledge level is currently novice.

Thanks!