K-Pop_Fan_4Life
K-Pop_Fan_4Life
PostsComments
KPCKevin Powell - Community
Created by K-Pop_Fan_4Life on 2/25/2024 in #back-end
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
}
2 replies
KPCKevin Powell - Community
Created by K-Pop_Fan_4Life on 11/22/2023 in #ui-ux
Accessibility Concerns and Feedback
No description
2 replies