MySql Query to fetch rows where a column which contains json data is less than any figure

Question

i have a table which contains json key pair data in a column (charges) like {"1":"200","2":"100","3":"600","4":"400"}. and i want to fetch those rows who contains charges less than any figure like (300). so my query should fetch all the rows that contain charges less than 300 comparing all json values. how to make a query?

I tried it in PHPMyAdmin

Answer 1

If you are using MySQL you can use JSON_EXTRACT function to get the values and then compare them with the condition. Otherwise you can use a query like below (but this assumes all values have the same structure):

select column_name from table_name where
substring_index(substring_index(column_name, '"', 4), '"', -1) < 300 or
substring_index(substring_index(column_name, '"', 8), '"', -1) < 300 or
substring_index(substring_index(column_name, '"', 12), '"', -1) < 300 or
substring_index(substring_index(column_name, '"', 16), '"', -1) < 300;