Selecting rows where json value is equal to MySQL php | w3schools | Page 1

celest lodge Feb 25, 2024, 7:49 PM

#

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;

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}

mighty dagger Feb 25, 2024, 8:15 PM

#

celest lodge Trying to select rows where a field in a json value is equal to whatever, and so...

can u try this?

SELECT * 
FROM `baskets` 
WHERE JSON_CONTAINS(basket->"$.ielts_exams[*].ielts_exam_type_id", '3');

celest lodge Feb 25, 2024, 8:23 PM

#

mighty dagger can u try this? ``` SELECT * FROM `baskets` WHERE JSON_CONTAINS(basket->"$.ie...

Unforntuanely a blank result, just updated the json though that's more of an encoding thing so wouldn't think that has an effect

mighty dagger Feb 25, 2024, 8:24 PM

#

Oh, wait

#

Need to pretify this first

#

@celest lodge I know your issue

#

#

See?

#

ielts_exams is string

#

you need to parse it to json

#

then you should be able to find

#

But also it is an array of objects, not json object

celest lodge Feb 25, 2024, 8:27 PM

#

Ahhh i see, looking at where I save the data to the database, i missed off one json_encode (had two), I'll take a look at what I can do there as well. Not possible to parse it to json in the sql is it?

mighty dagger Feb 25, 2024, 8:27 PM

#

Maybe it is, you will have to google that

#

I don't do PHP that much, I just noticed your JSON is wrong to begin with

#

But if it isn't then you will have to do it in php

celest lodge Feb 25, 2024, 8:30 PM

#

Got it working, it was that single json_encode that stopped the search working, thank you very much for seeing the ielts_exams was a string not json

celest lodge Feb 25, 2024, 8:39 PM

#

mighty dagger I don't do PHP that much, I just noticed your JSON is wrong to begin with

Yeah all fixed now solved the overall issue with the json being invalid when saved, can finally get to work figuring out json functions properly!

#Selecting rows where json value is equal to MySQL php