select query through with json data in SQL

Admin 1
Viewed 49

I am trying to fetch the data from the database where the value is inserted in JSON format, I read the various threads available online but these all are not worked and match the query or bit tough to implement for me as a beginner. Please help is this possible to fetch the data if the saving this formate in DB though MySQL query?

I am trying to do this from last 2 days but all functions and login was not worked. Please help.

ID log_in
1 [{"in_dt":"2020-01-01","in_by":namehere}]
2 [{"in_dt":"2020-01-01","in_by":namehere}]
3 [{"in_dt":"2020-01-02","in_by":namehere}]
4 [{"in_dt":"2020-01-05","in_by":namehere}]

what I am trying..

SELECT * 
  FROM `table` 
 WHERE DATE_FORMAT(JSON.parse(`log_in`.`in_dt`,'%Y-%m-%d')) 
           BETWEEN '2020-01-01' AND '2020-01-05'

but it not working..

mysql sql parsing json
1 Answers

You can rather use JSON_EXTRACT() function after quoting the values of the log_in column such as

SELECT *
  FROM `table` 
 WHERE DATE_FORMAT(JSON_EXTRACT(log_in,'$[0].in_dt'),'%Y-%m-%d')
           BETWEEN '2020-01-01' AND '2020-01-05'

Demo

If the data type of the log_in column is JSON instead of VARCHAR, the use an explicit casting for the SELECT-list such as

SELECT ID, CAST(log_in AS JSON) AS log_in
user_58413060
Related