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.

What do you mean with "JSON is BETWEEN"? Since you have several dates in that JSON, do you mean that *all* four of them should be between the given dates, or only the `start_date`s should be or is just one match is enough (with either `start_date` or `end_date`)? It would be good if you could provide an example with several records (for different situations) and the expected outcomes for them. — trincot, Jul 09 '18 at 07:42

score 1 · Answer 1 · answered Jun 29 '18 at 12:24

1

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

answered Jun 29 '18 at 12:24

Dave Stokes

775
4
10

maio290 · Answer 2 · 2018-06-27T11:37:52.257

0

You could actually store the start_date as DATE and the end_date as DATE. So you'd have two columns for the dates.

Then you could simply SELECT * FROM yourTable WHERE start_date >= 2018-06-01 AND end_date <= 2018-06-05

edited Jun 27 '18 at 11:37

answered Jun 27 '18 at 11:31

maio290

6,440
1
21
38

He wrote: "Any alternate suggestions instead of storing in JSON also welcome." ... but OK, better downvote it ;) – maio290 Jun 27 '18 at 11:33
@maio290 Thankx bt that cannot be achieved as I have multiple dates to be stored per row – Tarun Parswani Jun 28 '18 at 04:44
Hm, my SQL is too limited to provide a solution for your problem, but you could use SUBSTRING, CHARINDEX, REPLACE and CAST to extract the values from the string, bring it into a SQL Date format, cast it into a DATE and then compare it with a static Date. However, I guess you have your reasons to store the data like that, but it's rather unintuitive. SQL isn't really made for evaluating JSON. – maio290 Jun 28 '18 at 09:24

score 0 · Answer 3 · answered Aug 08 '22 at 18:22

I know the topic is a bit old but I had the same problem so I decided to post my solution, hope it can help someone.

For that, I use the JSON_TABLE function to generate a table where I can use the fields inside a WHERE clause.

SELECT t.* FROM table t
    INNER JOIN JSON_TABLE(
        t.json_field,
        '$[*]'
        COLUMNS(
            `start_date` DATE PATH '$.start_date',
            `end_date` DATE PATH '$.end_date'
        )
    ) AS j
    ON <input_date> BETWEEN j.start_date AND j.end_date;

The only problem is that your JSON string has an object format and not an array, to make the code above work the string would be:

[
  { "end_date": "2018-06-30", "start_date": "2018-06-01" },
  { "end_date": "2018-06-30", "start_date": "2018-06-01" }
]

mysql date comparison using BETWEEN in MySQL JSON data

3 Answers3