0

There's an external API that returns image similarities, based on an "image ID" and a "image version" (a same image can have multiple versions). The similarity ORDER is given by the API.

The API returns something like this:

+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|    236  |    1    |
|  37967  |    1    |
|   1413  |    2    |
+---------+---------+ 

Then I need to retrieve entries in a MySQL database (containing 2 tables), and keep the same ORDER as the one returned by the API.

That's where I'm having a problem. Here are the 2 tables:

"img" MySQL table:
+---------+-----------------------+
|   id    | lots of other columns |
+---------+-----------------------+
|    236  |         data          |
|   1413  |         data          |
|  37967  |         data          |
+---------+-----------------------+
"vers" MySQL table:
+---------+---------+
|   id    | version |
+---------+---------+
|    236  |    1    |
|   1413  |    1    |
|   1413  |    2    |
|  37967  |    1    |
|  37967  |    2    |
|  37967  |    3    |
+---------+---------+ 

the closest result I can get is by using ORDER BY FIELD, but it's still not the same ORDER as the one returned by the API.

My query:

SELECT i.id, v.version
FROM img i
LEFT JOIN vers v ON i.id=v.id 
WHERE ((i.id=37967 AND v.version=2) 
OR (i.id=236 AND v.version=1) 
OR (i.id=37967 AND v.version=1) 
OR (i.id=1413 AND v.version=2)) 
ORDER BY FIELD(i.id, 37967,236,37967,1413), FIELD(v.version,2,1,1,2) 
Results:
+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|  37967  |    1    |
|    236  |    1    |
|   1413  |    2    |
+---------+---------+

As you can see, the order is not exactly the one returned by the API :(

Any help would be appreciated, thank you all in advance.

  • As you can see, the order is not exactly the one returned by the API Does you mean the positional order of records in API output? MySQL cannot consider it. You must add some data/field to API output while transferring it to MySQL. ordering by this data must retrieve the physical order of data in API output. – Akina Dec 10 '19 at 12:52
  • Yes, i try to get the same positional order. I think there might be some kind of possibility using a combination of ORDER BY FIELD, or another type of ORDER BY, but i have really limited MySQL skills and i can't achieve this. – Daaaaa Dec 10 '19 at 13:00
  • API returns an array? collection? object? CSV? else? And what forn do you use for to transfer this data to MySQL? – Akina Dec 10 '19 at 13:10
  • API returns some JSON collection, containing id & version, ordered the way it should be (this is the first table in my question). Then i'm using the MySQL request that i gave in the question to retrieve data from the MySQL database (using PHP). But my request is bad, as i can't achieve the same order as the one provided by the API – Daaaaa Dec 10 '19 at 13:15
  • Can you process this JSON and add a component to each separate record with some name and integer value equal to the physical record position in this JSON? i.e. convert [{"id": 123, "version": 2},{"id": 456, "version": 1},{"id": 789, "version": 1}] to [{"id": 123, "version": 2, "position": 1},{"id": 456, "version": 1, "position": 2},{"id": 789, "version": 1, "position": 3}]. If it is possible then the task is easy - the final recordset will be ordered by this additional field on MySQL. – Akina Dec 10 '19 at 13:18
0

(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.

  • Hi Akina, thanks a lot for the explanations on how to create that extra ordering column! I guess a clever solution would be to update our MySQL server – Daaaaa Dec 11 '19 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.