MySQL order by(`field`, param1, param2, param3 from JSON array

Question

I'm building Mysql query:

select * from `table` order by field(`column`, "param1", "param2", "param3")

where param1, param2, param3 comes from JSON Array ["param1", "param2", "param3"] and when I put the parameters hardcoded in the query everything is OK, but when I prepare it (because I don't know what count and order is coming):

set json_array='["param1", "param2", "param3"]';
select * from `table` order by field(`column`, replace(replace(json_array, '[', ''), ']', ''))

Its not working.

This is going to be a tough one to answer. You may want to reference where you found your original documentation on how to do this, even if its not complete enough. I've googled it and not found anything directly related. — G-Man
– G-Man, Commented Feb 12, 2020 at 18:04

Alberto Moro · Accepted Answer · 2020-02-13 08:06:17Z

1

You have to use Prepared Statements

SET @json_array='["param3", "param1", "param2"]';
SET @query = CONCAT('select * from `table` order by field(`column`, ',
                    replace(replace(@param, '[', ''), ']', ')'));
PREPARE stmt1 FROM @query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

DEMO

edited Feb 13, 2020 at 8:06

answered Feb 12, 2020 at 15:46

Alberto Moro

1,01314 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

iMarh Over a year ago

For me it doesn't work - sqlfiddle.com/#!9/0c738f/1 :-(

Alberto Moro Over a year ago

@iMarh I have found a solution for MySQL 5.7. Which version of MySQL do you use?

iMarh Over a year ago

My version is 5.5. I see that 5.5 also works. Thanks Alberto!

Alberto Moro Over a year ago

@iMarh Happy to help. If this answer or any other one solved your issue, please mark it as accepted :)

Collectives™ on Stack Overflow

MySQL order by(`field`, param1, param2, param3 from JSON array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related