Search code examples
mysqlmysql-json

How to convert a string value of json into dateTime and compare

SELECT JSON_EXTRACT(z2schedule,'$[*].start') as startDate from cpmdev_z2weekly_schedule

After running above code I am getting response as :- enter image description here

Now If I tried to compare each value to time value using below code but it is not working:-

SELECT JSON_EXTRACT(z2schedule,'$[*].start') as startDate from
cpmdev_z2weekly_schedule where
JSON_EXTRACT(z2schedule,CONVERT('$[*].start'),'TIME')>
'CONVERT('2022-11-02 13:10:00:000', TIME)

My requirement is only to compare each value with the time value and return only if the value is greater than given time. For Example in Table I have Data as:-

[{"start":"09:00:00.000","end":"17:00:00.000"}]
[{"start":"10:00:00.000","end":"17:00:00.000"}]
[{"start":"11:00:00.000","end":"17:00:00.000"}]

Now I want all the start Date which is greater then 10:00:00 In above case then it should return :

11:00:00.000
Solution

  • The JSON you show is an array of objects. When you use $[*].start, it returns a JSON array. This is not a single time. You can see the square brackets around the time value:

    mysql> set @j = '[{"start":"09:00:00.000","end":"17:00:00.000"}]';

mysql> select json_extract(@j, '$[*].start') as times;
+------------------+
| times            |
+------------------+
| ["09:00:00.000"] |
+------------------+

    The square brackets make it not valid as a time value.

    mysql> select convert(json_extract(@j, '$[*].start'), time) as times;
+-------+
| times |
+-------+
| NULL  |
+-------+

    Since your JSON array seems to have only one object in it, you could use $[0] to select the first object in the array. Then it returns a single string value and that is convertable to a time:

    mysql> select convert(json_extract(@j, '$[0].start'), time) as time;
+----------+
| time     |
+----------+
| 09:00:00 |
+----------+

    Note also that the data type named in the CONVERT() function is a keyword, not a quoted string. That is, 'time' is incorrect, just use time.

    If your JSON array may have more than one object, and you need to test all of them, then you should use the JSON_TABLE() function.

    By the way, all these issues would be avoided if you stored your start and end times in normal rows and columns. Using JSON makes many queries more difficult to develop and optimize. You should consider normalizing your data, and not using JSON.