Search code examples
mysqljoinleft-joininner-join

MySQL join on specific substring from a coulmn of type text

SHOPS
+----+---------------+
| id |   shop_tag    |
+----+---------------+
| 1  | 1234560000000 |
+----+---------------+

LOGS
+----+-------------------------------------+
| id |             request                 |
+----+-------------------------------------+
| 1  |  {"key":"123","tag":"123456*****"}  |
+----+-------------------------------------+

The column shop_tag is of int type and column request of logs in text type and contains a jSON string.

Now I want to join these 2 tables after extracting 123456 from both columns.

I am trying this but of no help

SELECT logs.id FROM logs INNER JOIN shops ON left(shops.shop_tag,6) = left(right(logs.request,6),26)

Please note that the length and structure of both shop_tag and request column are fixed.

Solution

  • If you're using MySQL 5.7.13+, you can use the JSON function ->> to extract the tag value from the request column and directly compare the left 6 characters:

    SELECT *
FROM SHOPS s
JOIN LOGS l ON BINARY LEFT(l.request->>'$.tag', 6) = BINARY LEFT(s.shop_tag, 6)

    Note dependent on the collation of your tables you may not need the BINARY in the condition.

    Demo on dbfiddle