search between two iso date in JSON mysql

Question

Below is my JSON which is save in response column of mysql table

{
    "data": {
        "submit": false,
        "lastName": "abc",
        "firstName": "xys"
    },
    "metadata": {},
    "createDate": "2021-04-23T00:00:00+00:00"
}

Now I want to query between condition on that for that I try to write the below query

SELECT * FROM form_response WHERE DATE_FORMAT(response->'$.data.createDate', '%Y-%m-%dT%TZ') BETWEEN "2021-04-22T00:00:00+00:00" AND "2021-04-23T00:00:00+00:00"

but I am getting blank response.

Any help appreciated

Answer 1

SELECT * FROM form_response WHERE DATE_FORMAT(SUBSTR(response->'$.createDate',2,CHAR_LENGTH(response->'$.createDate')-2), '%Y-%m-%dT%TZ') BETWEEN "2021-04-22T00:00:00+00:00" AND "2021-04-23T00:00:00+00:00"

Answer 2

response->'$.data.createDate' returns null already so nothing else matters after that. According to your data structure what you want is response->'$.createDate'. Also, DATE_FORMAT() converts from date to string. I think you want STR_TO_DATE(). Last but not least, the date format identifier you provide doesn't match the format used by data.

Don't treat your code as a black box. You can debug each processing step:

SELECT
    response->'$.createDate' AS string,
    STR_TO_DATE(response->'$.createDate', '"%Y-%m-%dT%T+00:00"') AS data
FROM form_response;

+-----------------------------+---------------------+
| string                      | date                |
+-----------------------------+---------------------+
| "2021-04-23T00:00:00+00:00" | 2021-04-23 00:00:00 |
+-----------------------------+---------------------+

In your case, you can simplify it further since you're using a standard date format that's recognised by MySQL:

SELECT
    response->'$.createDate' AS string,
    CAST(response->'$.createDate' AS DATETIME) AS date
FROM form_response;