CODEWITHSUNDEEP
mysqljson
John
John

Reputation: 1751

MySQL LEFT JOIN ON JSON to get values

Streams:

+----+-----------+
| id |   server  |
+----+-----------+
|  1 | ["1","15"]|
+----+-----------+

Servers:

+----+-------------------+------------------+
| id | server            |      download    |
+----+-------------------+------------------+
|  1 | Server #1         |      2.05        |
|  2 | Server #2         |      23.96       |
|  3 | Server #3         |      8.08        |
| 15 | Server #15        |      3.03        |
+----+-------------------+------------------+

I need to get thus for result:

+----+-----------+--------------+
| id |   server  |  download    |
+----+-----------+--------------+
|  1 | Server #1 |     2.05     |
+----+-----------+--------------+

So basically i need to get from streams table server field and that field need to read from servers table what server have lower download and return that server like reult table posted above.

SELECT servers.server FROM servers
LEFT JOIN streams ON servers.id=CAST(json_extract(streams.server, '$[0]') AS 
UNSIGNED)
WHERE streams.id=1;

So i get only first server from result not two...so that i can then compare what have lover download...i know that problem is '$[0]' but i don't know how to left join or how to read all values from streams server (1 and 15) and then to add witch is lover from that two...what i im missing?

Upvotes: 2

Views: 2621

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

I guess you should also extract the second JSON value and LEFT JOIN it again like this.

SELECT servers.server FROM servers

LEFT JOIN streams ON servers.id = CAST(json_extract(streams.server, '$[0]') AS 
UNSIGNED)    
LEFT JOIN streams ON servers.id = CAST(json_extract(streams.server, '$[1]') AS 
UNSIGNED)

WHERE streams.id = 1;

Upvotes: 1

Related Questions