SELECT Query through JSON value in SQL

Databases
1

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…

2

Is JSON.parse() even part of SQL? It’s not something I’ve tried, but as far as I can tell it’s a javascript function.
There are various JSON functions for the types of SQL
Though it begs the question: why store the data this way? It may not have been your choice, but one value per column makes life a lot easier.

1 Like
3

You can use JSON_EXTRACT in mySQL

SELECT * 
FROM table 
WHERE DATE_FORMAT(JSON_EXTRACT(log_in, '$.in_dt'), '%y-%m-%d) BETWEEN '2020-01-01' AND '2020-01-05
4

no any output comes

5

I See in the column is an json array. So are you sure this array always contains only one element? then you can use

JSON_EXTRACT(log_in -> '?.in_dt' , '$[0]')
6

it still not working sir…

7

create a fiddle at

and I can help. Everything else is guessing around.

8

You are support if you use left join too…

CREATE TABLE `table1` (
  `ID` INT, `f_name` VARCHAR(100), `f_code` VARCHAR(100)
);
INSERT INTO `table1` (`ID`, `f_name`, `f_code`) VALUES (1, 123, 'Mango');
INSERT INTO `table1` (`ID`, `f_name`, `f_code`) VALUES (2, 124, 'Apple');

CREATE TABLE `table2` (ID` INT, `e_name` VARCHAR(100), `log_in` VARCHAR(100));
INSERT INTO `table2` VALUES 
(  1 , 123, '[{"in_dt":"2020-01-01","in_by":"a"}]'),                   
(  2 , 123, '[{"in_dt":"2020-01-01","in_by":"b"}]'),                   
(  3 , 124, '[{"in_dt":"2020-01-02","in_by":"c"}]'),                   
(  4 , 124, '[{"in_dt":"2020-01-05","in_by":"d"}]')
9

How did this json get produced? It is a json array containing a json object, requiring you to reference the zero’th array element and the in_dt property.

json_extract returns a json quoted value. all the attempts using date_format on that value should have been producing warnings about incorrect datetime values.

To get this to ‘work’ you must use json_unquote -

SELECT * 
 FROM `table`
 WHERE json_unquote(json_extract(log_in,'$[0].in_dt')) BETWEEN '2020-01-01' AND '2020-01-05';

Given that none of this can use an index, and you must perform operations on it in every query to use the values, you need to do what @SamA74 has already recommended and just store this data in two separate columns.

1 Like
10

Its working but can we use DATE_FORMAT like:
CASE 1. DATE_FORMAT(json_unquote(json_extract(log_in,'$[0].in_dt')), '%Y-%m-%d')
but this is not working. this is the first query I never used before and it’s really very interesting for me to learn something new.

11

The values are already in the proper format. There’s no need to apply date_format() to them, unless you are not actually showing us the real values that are stored? Are they actually date time values?

Using this in the WHERE clause in the query that has been the subject in this thread works for me. If something doesn’t work for you, must post the entire query you are attempting and post what result you did get. Also, how are you executing these queries? Directly against the database using a tool like phpmyadmin or are you using php code?

12

I am storing the date and time in real database like YYYY-MM-DD H:i:s

13

If the information you have been showing is incomplete or adulterated, the solutions may not work because they don’t take into account all the extents and limits of the problem. Programming is an exact science.

15

Please help me this case last that what I will do if join with two different database.
like:
SELECT * FROM db1.table a JOIN db2.table b ON b.col = JSON_UNQUOTE(JSON_EXTRACT(a.col,'$[0].in_dt'));

this is not working… please help… and guild

16

Without some sample data, what result you got, and what result you expected from that data, no one can help with ‘this is not working’.

1 Like
17

I am trying to explain how your code will work when I use two different database.
without JSON_UNQUOTE(JSON_EXTRACT()) I can use this documentation here but in this json case what I will do. and how I will join the two different database.

18

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.