CODEWITHSUNDEEP
mysqljson
John
John

Reputation: 147

mysql JSON LEFT JOIN

TABLE streams;

+----+----------+-------------------------------------------------------------+-------------+-------------+
| id | name     | stream                                                      | server      | bouquet     | 
+----+----------+-------------------------------------------------------------+-------------+-------------+
|  1 | HOME     | ["http://ur11.com/", "http://ur12.com/"]                    | ["1", "3"]  | ["3","2"]   |
+----+----------+-------------------------------------------------------------+-------------+-------------+

TABLE servers;

+----+-------------------+
| id | server            |
+----+-------------------+
|  1 | Main              |
|  2 | Server #1         |
|  3 | Server #2         |
|  4 | Server #3         |
|  5 | Server #4         |
+----+-------------------+

I need to run LEFT JOIN on table streams and to retrieve using first index in streams.server value of servers.server:

For Example i need left join to get this result:

+----+----------+-------------------------------------------------------------+-------------+-------------+
| id | name     | stream                                                      | server      | bouquet     | 
+----+----------+-------------------------------------------------------------+-------------+-------------+
|  1 | HOME     | ["http://ur11.com/", "http://ur12.com/"]                    | Main        | ["3","2"]   |
+----+----------+-------------------------------------------------------------+-------------+-------------+

So the point is to get first index from streams.server witch is 1 and then LEFT JOIN on servers.id to get servers.server name witch is in this case Main

I installed mysql 5.7 version witch includes JSON support but i did not get clue how to use LEFT JOIN on JSON field type?

Upvotes: 0

Views: 1799

Answers (3)

John
John

Reputation: 147

I got it workijng using your above solutions:

select s.*, se.server
from streams s left join
servers se
on se.id = CAST(json_extract(s.server, '$[0]') AS UNSIGNED);

Thanks.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think you want:

select s.*, se.server
from streams s join
     servers se
     on se.id = json_extract(s.server, '$[0]');

Depending on the exact version of MySQL you installed, you can also use s.server->'$[0]'.

Upvotes: 1

user3863196
user3863196

Reputation:

example:

SELECT *
FROM foo 
CROSS JOIN LATERAL json_array_elements (foo.bars :: json) foo_bars 
LEFT OUTER JOIN bar ON (foo_bars ->> 'id') :: BIGINT = bar.ID;

Link

Upvotes: 0

Related Questions