How to search for specific data inside a JSON data at table

user_17119202 0
Viewed 103

I have a table named carts which has this structure:

1   crt_id Primary  bigint(20)      
2   crt_mbr_id      bigint(20)          
3   crt_session_id  varchar(128)    
4   crt_content     text    
5   crt_send_type   int(11)         
6   crt_completed   tinyint(1)          
7   created_at      timestamp           
8   updated_at      timestamp

And crt_content data goes like this:

[{"id":"24","quantity":"1","price":3000,"discounted":3000,"coupon":0}]

Now I need to search in crt_content for the number 24. But I don't know what is the sql command for this.

So would you mind helping me out on how to search for specific data inside a JSON data which is stored at the table?

phpmyadmin mysql sql php json
1 Answers

You can use the JSON_EXTRACT function like shown below in your where clause:

JSON_EXTRACT(crt_content, "$.id") = "24"

Though, I'd like to warn you, that I would not recommend this for a solution, containing a large amount of data. If you wish to read more about JSON search functions, take a look here.

user_170158550
Related