Kevin Powell - Community•4mo ago

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": "test@test.com",
        "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": "test@test.com",
        "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•4mo ago

Solved via an incorrect encoding type on saving the data to the database

Gaming

Programming

selecting where json field in a json type column in MySQL