IN statement with mysql JSON column not working?

user_2237022 0
Viewed 180

I am trying to query MySQL table on json column data not working as expected

JSON_EXTRACT(data, \"$.STUDENT_NAME\") IN ('JASMINE','SAM')

where as this JSON_EXTRACT(data, \"$.STUDENT_NAME\") IN ('JASMINE') works with only one element but not with multiple elements in the array, any ideas?

complete query

SELECT `data_uploads`.* FROM `data_uploads` WHERE `data_uploads`.`product_id` = 96 AND (dlname = 'STUDENT' AND JSON_EXTRACT(data, \"$.STUDENT_NAME\") IN ('JASMINE', 'SAM'))

expected results

product_id: 96, dlname: "STUDENT", data: {"CLASS"=>"GRADE-I", "GRAD_IMAGE"=>"jasmine_grad.jpg", "SECTION"=>"A", "STUDENT_IMAGE"=>"jasmine.jpg", "STUDENT_NAME"=>"JASMINE"}, created_at: "2021-06-18 10:16:56", updated_at: "2021-06-18 10:16:56"
product_id: 96, dlname: "STUDENT", data: {"CLASS"=>"GRADE-I", "GRAD_IMAGE"=>"sam_grad.jpg", "SECTION"=>"A", "STUDENT_IMAGE"=>"sam.jpg", "STUDENT_NAME"=>"SAM"}, created_at: "2021-06-18 10:16:56", updated_at: "2021-06-18 10:16:56"

but the above query returns an empty array

Rails active record query:

DataUpload.where(product_id: 96).where("dlname = 'STUDENT' AND JSON_EXTRACT(data, '$.STUDENT_NAME') IN ('JASMINE','SAM')")
ruby-on-rails mysql json
1 Answers

Use JSON_UNQUOTE() to remove the quotes around the value before testing it.

SELECT *
FROM data_uploads
WHERE JSON_UNQUOTE(JSON_EXTRACT(data, "$.STUDENT_NAME")) IN ('JASMINE','SAM');

You can also use the ->> shorthand to extract and unquote at once.

SELECT *
FROM data_uploads
WHERE data->>"$.STUDENT_NAME" IN ('JASMINE','SAM');
user_14918950
Related