select query through with json data in SQL

Question

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..

I think namehere is just a representation but not a real value as not being quoted, isn't it? — Barbaros Özhan
– Barbaros Özhan, Commented Aug 20, 2022 at 9:21
yes, this is for representation and the value being in quotes. sorry for the typos — user19676440
– user19676440, Commented Aug 20, 2022 at 9:36
Btw, What's the data type of the log_in column, VARCHAR or JSON ? — Barbaros Özhan
– Barbaros Özhan, Commented Aug 20, 2022 at 9:38

Barbaros Özhan · Accepted Answer · 2022-08-20 09:37:03Z

1

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

answered Aug 20, 2022 at 9:37

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user19676440 Over a year ago

no output comes

Barbaros Özhan Over a year ago

Hi @Satyam . Can you please explain what have you tried and got within a fiddle(with some sample data of course), and share link of the fiddle as the comment ...

user19676440 Over a year ago

it is not working on my myphpadmin but it working in online fiddle or SQL IDE

Barbaros Özhan Over a year ago

What error(s) do you get @Satyam ?

user19676440 Over a year ago

no any error comes only empty row get..

Collectives™ on Stack Overflow

select query through with json data in SQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related