mysql date comparison using BETWEEN in MySQL JSON data

Question

I'm struggling to compare dates which are stored in JSON data in MySQL.

The format of the JSON would be something like

{"0": {"end_date": "2018/06/30", "start_date": "2018/06/01"}, "1": {"end_date": "2018/06/30", "start_date": "2018/06/01"}}

So, now I need to write a MySQL query to fetch the row only if the date in the JSON is BETWEEN '2018/06/01' and '2018/06/05'.

Any alternate suggestions instead of storing in JSON also welcome.

Answer 1

I would be tempted to use generated columns on start data and end date to make the SQL much easier.