How to search data on a json array of values in MySQL

Question

I have never used json data to store any values inside my database, now the situation prompted me to use that.

Below is my json format I'm storing that into MySQL database

{
    "wrestling_board": [{
        "Price": "400",
        "Quantity": "1",
        "Amount": "400"
    }],
    "sign_board": [{
        "Price": "200",
        "Quantity": "1",
        "Amount": "200"
    }],
    "total": [{
        "Price": null,
        "Quantity": null,
        "Amount": "600"   <-- total price
    }]
}

Here is MySQL Demo:

How to search all results where total.amount > 3000?

1. that's a horrible way to store data as you are finding out 2. you must select all of the data, loop through, decode it and test for > 3000 — AbraCadaver
– AbraCadaver, Commented Dec 31, 2017 at 4:41
What have you tried? We're not here to write your code for you. — Jeffrey
– Jeffrey, Commented Dec 31, 2017 at 4:41
If you are using mySQL 5.7 then you could use the JSON datatype to store the data and also retrieve it. See manual dev.mysql.com/doc/refman/5.7/en/json.html — user2417483
– user2417483, Commented Dec 31, 2017 at 4:44
You have invalid type for the column where you store JSON data. The column type should be JSON, not TEXT. Please rewrite your fiddle accordingly. — olegsv
– olegsv, Commented Jan 1, 2018 at 9:21

shA.t · Accepted Answer · 2018-01-01 10:24:55Z

Update: If your field is json, you can use a query like this:

select 
    customer_name,company_name,email,quotation_data
from 
    sent_quotation_data1
where 
    cast(                                             --> as you want to check numeric operator I use cast to unsigned
        (replace(                                     --> as your data is between " I use replace to remove them
            quotation_data->"$.total[0].Amount"       --> Here you can extract json data
        ,'"','')) as unsigned
    ) > 3000;

MySQL Fiddle Demo

I think a way is using REGEXP like this:

select 
   customer_name,company_name,email,quotation_data 
from 
   sent_quotation_data
where 
   quotation_data REGEXP '"total":.+"Amount":"(3[0-9][0-9][1-9]|[4-9][0-9]{3}|[1-9][0-9]{4,})"';

MySQL Fiddle Demo

"total":                     --> Finding total between " then :
.+                           --> Some characters
"Amount":"                   --> Finding Amount between " then : then "
(                            --> start a combination
    3[0-9][0-9][1-9]         --> accepting range of 3001-3999
    |                        --> or
    [4-9][0-9]{3}            --> accepting range of 4000-9999
    |                        --> or
    [1-9][0-9]{4,}           --> accepting range of 10000-...
)                            --> end of combination
"                            --> following by "

Collectives™ on Stack Overflow

How to search data on a json array of values in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related