0

I have following table:

 +----+-----------------------------------------+----------+
 | id | sort                                    |  channel |
 +----+-----------------------------------------+----------+
 | m2 | ["m2","1","2","11","m4","m3","m5","m1"] | E        |
 | 2  | ["m2","1","2","11","m4","m3","m5","m1"] | H2       |
 | 1  | ["m2","1","2","11","m4","m3","m5","m1"] | H1       |
 | 11 | ["m2","1","2","11","m4","m3","m5","m1"] | H3       |   
 | m4 | ["m2","1","2","11","m4","m3","m5","m1"] | S1       |
 | m3 | ["m2","1","2","11","m4","m3","m5","m1"] | C3       |
 | m5 | ["m2","1","2","11","m4","m3","m5","m1"] | M4       |
 | m1 | ["m2","1","2","11","m4","m3","m5","m1"] | G6       |
 +----+-----------------------------------------+----------+

Using MySQL Query:

 SELECT id, sort, channel FROM (SELECT s.id, c.sort, s.channel FROM streams s, channels c WHERE JSON_SEARCH(s.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', s.id) IS NOT NULL UNION SELECT m.marker, c.sort, m.channel FROM markers m, channels c, streams s WHERE JSON_SEARCH(m.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', m.marker) IS NOT NULL) ch ORDER BY LOCATE(id, CONCAT("'", sort, "'"));

I got above table result and i need to sort id using sort field...so output needs to be:

| id |
------
  m2
  1
  2
  11
  m4
  m3
  m5
  m1

I think the problem is in the: ORDER BY LOCATE(id, CONCAT("'", sort, "'")) but i try to modify query without desired output success...what i need to do to get above correct output?

forpas
  • 160,666
  • 10
  • 38
  • 76
John
  • 147
  • 1
  • 13
  • 1
    i would suggest upgrading to MySQL 8 (if possible) because of JSON_TABLE() which makes this alot more easy.. It should be possible in older version as well but you would need to be creative with a SQL number generator to dynamically parse out the JSON array. – Raymond Nijland May 14 '19 at 09:58
  • a [example (post off me)](https://stackoverflow.com/questions/55744311/how-to-remove-multiple-values-from-mysql-json/55744876#55744876) of a SQL number generator to parse out a JSON array which i was talking about.. I believe the example off mine can be marked for a duplicate for this question because it also uses a JSON array. – Raymond Nijland May 14 '19 at 10:02
  • Thanks for pointing me out for MySQL8 but do you have any site example how it is done in this new version before i upgrade...and if there is any performance improvements in MySQL8? – John May 14 '19 at 10:07
  • *"Thanks for pointing me out for MySQL8 but do you have any site example how it is done in this new version before i upgrade"* see mine second comment? it has a example for MySQL 8.0 and older MySQL versions. *"and if there is any performance improvements in MySQL8?"* Pretty sure of that as MySQL 8.0 has a much better optimizer then MySQL 5 had. – Raymond Nijland May 14 '19 at 10:09

1 Answers1

1

Use this:

ORDER BY LOCATE(CONCAT('"', id, '"'), sort)

the values contain double quotes so the concatenation must enclose them in double quotes not single quotes.
See a simplified demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks...this works like charm...i spend few hours to get these output but i never will get to this simple order..thanks again..you saved me time :) – John May 14 '19 at 10:30