MySQLでJSONカラム内の値を使ってJOINする
必要な気がしてめっちゃ調べたけど結局使わなかったのでここに供養しておく
やりたいこと
こういうテーブルから、
CREATE TABLE users (
id INT,
name TEXT,
data JSON
);
CREATE TABLE posts (
id INT,
name TEXT
);
INSERT INTO users (id, name, data) VALUES (1, 'first user', '[{"post_id": 1}, {"post_id": 2}]');
INSERT INTO users (id, name, data) VALUES (2, 'second user', '[{"post_id": 3}]');
INSERT INTO posts (id, name) VALUES (1, 'first post');
INSERT INTO posts (id, name) VALUES (2, 'second post');
INSERT INTO posts (id, name) VALUES (3, 'third post');こんな感じのデータを取得したい。
+---------+-------------+---------+-------------+
| user_id | user_name | post_id | post_name |
+---------+-------------+---------+-------------+
| 1 | first user | 1 | first post |
| 1 | first user | 2 | second post |
| 2 | second user | 3 | third post |
+---------+-------------+---------+-------------+どうすればいいか
SELECT
users.id AS user_id,
users.name AS user_name,
posts.id AS post_id,
posts.name AS post_name
FROM users
JOIN posts ON JSON_CONTAINS(users.data->'$[*].post_id', CAST(posts.id AS CHAR), '$');ややこしいのはJSON_CONTAINS関数の使い方で、第2引数はJSONとして有効な文字列じゃないとだめ（数値型はNG）らしく、文字列型にキャストする必要がある。
あと、第3引数にワイルドカード（*, **）を使うことができないので、使いたい場合は第1引数に書く。
ちなみにこのやり方だと（たぶん）インデックスが貼れないので、可能ならばスキーマを見直した方がいい。