selecting where json field in a json type column in MySQL

Trying to select rows where a field in a json value is equal to whatever, and somewhat struggling. query so far, and example of the data stored in the json field Trying to query a json field and struggling to quite get the results in MySQL. This is the query I have so far, and whilst there is a row in the database that has this specific ID, i can't get it right, and the docs aren't quite sinking in
SELECT * FROM `baskets` WHERE basket->"$.ielts_exams.ielts_exam_type_id" = 3;
SELECT * FROM `baskets` WHERE basket->"$.ielts_exams.ielts_exam_type_id" = 3;
field/json is formated as (just the relevant part) (generated using json_encode($data))
{
"total": "185",
"ielts_exams": "[{\"id\":5001,\"ielts_venue_id\":1,\"ielts_module_type_id\":1,\"ielts_exam_type_id\":3,\"date_of_exam\":\"2024-02-28\",\"time_of_exam\":\"08:30\",\"created\":\"2024-02-25T16:18:53+00:00\",\"modified\":\"2024-02-25T16:18:53+00:00\",\"ielts_exam_type\":{\"id\":3,\"name\":\"One Skill Retake\"},\"ielts_module_type\":{\"id\":1,\"name\":\"Academic\"},\"ielts_venue\":{\"id\":1,\"ielts_city_id\":1,\"venue_id\":1,\"is_paper_based\":true,\"is_computer_based\":true,\"is_life_in_uk\":false,\"price\":220,\"is_archived\":false,\"venue\":{\"id\":1,\"venue_address_id\":1,\"venue_detail_id\":1,\"venue_name\":\"MTS London\",\"is_open\":true,\"created\":\"2024-02-23T15:21:45+00:00\",\"modified\":\"2024-02-23T15:21:45+00:00\"}}}]",
"contact_detail": {
"email": "[email protected]",
"surname": "Testing",
"first_name": "Sam",
"phone_number": "0987654321"
},
"identification_number": "1234567",
"british_council_number": "00000000",
"identification_type_id": 2
}
{
"total": "185",
"ielts_exams": "[{\"id\":5001,\"ielts_venue_id\":1,\"ielts_module_type_id\":1,\"ielts_exam_type_id\":3,\"date_of_exam\":\"2024-02-28\",\"time_of_exam\":\"08:30\",\"created\":\"2024-02-25T16:18:53+00:00\",\"modified\":\"2024-02-25T16:18:53+00:00\",\"ielts_exam_type\":{\"id\":3,\"name\":\"One Skill Retake\"},\"ielts_module_type\":{\"id\":1,\"name\":\"Academic\"},\"ielts_venue\":{\"id\":1,\"ielts_city_id\":1,\"venue_id\":1,\"is_paper_based\":true,\"is_computer_based\":true,\"is_life_in_uk\":false,\"price\":220,\"is_archived\":false,\"venue\":{\"id\":1,\"venue_address_id\":1,\"venue_detail_id\":1,\"venue_name\":\"MTS London\",\"is_open\":true,\"created\":\"2024-02-23T15:21:45+00:00\",\"modified\":\"2024-02-23T15:21:45+00:00\"}}}]",
"contact_detail": {
"email": "[email protected]",
"surname": "Testing",
"first_name": "Sam",
"phone_number": "0987654321"
},
"identification_number": "1234567",
"british_council_number": "00000000",
"identification_type_id": 2
}
1 Reply
K-Pop_Fan_4Life
K-Pop_Fan_4Life9mo ago
Solved via an incorrect encoding type on saving the data to the database
Want results from more Discord servers?
Add your server