CODEWITHSUNDEEP
mysqlsqljson
user5405873
user5405873

Reputation:

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

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?

Upvotes: 0

Views: 172

Answers (1)

shA.t
shA.t

Reputation: 16958

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 "

Upvotes: 1

Related Questions