ORDER from 2 tables to achieve a specific order returned by an API
(copied from the chat)


  MySQL version is 5.6.40
  
  Max records is 100.




The method to parse JSON string to enumerated table (up to 100 records) applicable to version 5.6.40.

set @JSON='{"imgResults":[{"id":"37967","version":"2"},{"id":"236","version":"1"},{"id":"37967","version":"1"},{"id":"1413","version":"2"}]}'


This is example source JSON string. 

It may be transferred to server as query parameter to the above query which assigns the value to a variable or inserted immediately into the query text as string literal.

Also It may be transferred to server as query parameter to the below query which parses it, i.e. intermediate user-defined variable is optional.



Now we parse JSON to a table (emulation of JSON_TABLE function available since 8.0 version):

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"id":"', num+2), '"id":"', -1), '"', 1) id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(json, '"version":"', num+2), '"version":"', -1), '"', 1) version,
       MIN(num) idx
FROM (SELECT @JSON json) source,            -- source JSON string
( SELECT numbers1.num*10+numbers2.num num   -- create numbers table, 0..99
  FROM ( SELECT 0 num UNION 
         SELECT 1 UNION 
         SELECT 2 UNION 
         SELECT 3 UNION 
         SELECT 4 UNION 
         SELECT 5 UNION 
         SELECT 6 UNION 
         SELECT 7 UNION 
         SELECT 8 UNION 
         SELECT 9 ) numbers1,
       ( SELECT 0 num UNION
         SELECT 1 UNION
         SELECT 2 UNION 
         SELECT 3 UNION
         SELECT 4 UNION
         SELECT 5 UNION 
         SELECT 6 UNION 
         SELECT 7 UNION 
         SELECT 8 UNION 
         SELECT 9 ) numbers2 ) numbers
 GROUP BY id,version
 -- ORDER BY idx;


Output:



id    | version | idx
:---- | :------ | --:
37967 | 2       |   0
236   | 1       |   1
37967 | 1       |   2
1413  | 2       |   3





You may join this parser into your query as a source data subquery and use its idx field for ordering the final result in the same order in which the records are posessed in source JSON.