MySQL query from JSON datatype column that has backslash character

user_9444495 0
Viewed 460

I have a difficulty to query element in JSON column in MySQL

This is my data : enter image description here

example of whats in the column : {\order\":[{\"product_id\":3,\"quantity\":5,\"product_name\":\"Ikan salmon bakar \"}]}"

I'm using this sql syntax :

SELECT JSON_EXTRACT(order_list, '$.order[*].product_id') FROM orders_copy WHERE id = 3;

If I use this query to the column that contain backslash, then it will produce null.

enter image description here

Expected result (eg:) :

enter image description here

I'm not sure if I should get rid of the backslash so that the query will produce result or is there any effective way solving it ?.

Thanks a lot

mysql json
2 Answers
user_94444950

You can remove the backslash using REPLACE() and make the main query to become a sub-query before the JSON_EXTRACT function take place. Try this query:

SELECT JSON_EXTRACT(od, '$.order[*].product_id') 
FROM 
(SELECT *, REPLACE(order_list,'\\','') AS od FROM orders_copy WHERE id = 3) A;

And here's an example fiddle: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=6f52caffa6c97bbcfcc18bb5fb713348

user_109106920
Related