CODEWITHSUNDEEP
mysqljoinleft-joininner-join
user6379404
user6379404

Reputation:

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.

Upvotes: 0

Views: 117

Answers (2)

Juxuny Wu
Juxuny Wu

Reputation: 120

You can use the built-in function JSON_EXTRACT

SELECT JSON_EXTRACT('{"key":"123","tag":"123456*****"}', '$.key') `key`

output:


|key    |
+-------+
|"123"  |

Upvotes: 0

Nick
Nick

Reputation: 147166

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

Upvotes: 1

Related Questions