MySQL JSON型の検索・変換（valueを置き換えたい）

Question

中間テーブルの作成が適切という回答をいただいております。構成自体がJSON型に適切ではありませんでしたのでご注意ください

MySQLのJSON型の扱いについて質問があります。
もしかしたらJSON以前の問題かもしれませんのでご了承ください。

テーブル：groups

id	name	join_user
1	Aグループ	["1","2","4"]
2	Bグループ	["1","3","5"]
3	Cグループ	["1","4","3"]
4	Dグループ	["2","4","5"]

テーブル：users

id	name
1	A’さん
2	B’さん
3	C’さん
4	D’さん
5	E’さん

上記の２テーブルを結合して最終的に以下の出力がしたいと考えています。
groups.join_userのJSONをusers.idとしてusers.nameに変換が目的です。

id	name	join_user	join_user_name
1	Aグループ	["1","2","4"]	A'さん,B'さん,D'さん
2	Bグループ	["1","3","5"]	A'さん,C'さん,E'さん
3	Cグループ	["1","4","3"]	A'さん,D'さん,C'さん
4	Dグループ	["2","4","5"]	A'さん,D'さん,E'さん

適切な方法があるとともうのですが
現在、手法としてはGROUP_CONCAT、REGEXP CONCATで作成しています。

SELECT G.id,G.name,G.join_user,
GROUP_CONCAT(U.name SEPARATOR '、') AS  join_user_name 
FROM `groups` AS G
LEFT JOIN `users` AS U ON G.join_user REGEXP CONCAT('"',U.id, '"')
GROUP BY G.id;

一応、目的の出力はできているのですが、皆様に適切なSQL文についてご教示いただきたくご質問いたしました。
何卒お願い致します。

Answer 1

Kosuke_Shibuya様より調べて頂いた解決方法です。
コメント内にあるので自己解決方法の場を借りて追記いたします。

*Ver 5.7 では動作しないというコメントも頂いております。

SELECT G.*,GROUP_CONCAT(U.name SEPARATOR '、') AS  join_user_name 
FROM `groups` AS G
INNER JOIN `users` AS U ON JSON_SEARCH(G.join_user,'one', U.id) IS NOT NULL
GROUP BY G.id;

MySQLバージョン（Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)）

Answer 2

こんな感じ

CREATE TABLE `Groups` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `UserGroup` (
  `user_id` int(11) unsigned NOT NULL,
  `group_id` int(11) unsigned NOT NULL,
  UNIQUE KEY `user_id` (`user_id`,`group_id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `usergroup_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `usergroup_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `Groups` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT 
	G.id
	, G.name
	, group_concat(U.id) AS join_user
	, group_concat(U.name) AS join_user_name 
FROM `Groups` G
LEFT JOIN `UserGroup` UG ON G.id = UG.group_id
LEFT JOIN `Users` U ON UG.user_id = U.id
GROUP BY G.id

JSON型はインデックスが効かないのでJOINするフィールドには向かないと思います。

Comments

[BlueBits]

回答ありがとうございます。この構成だと中間テーブル作ったほうがやっぱりいいですよね。
今後、JSON型を使う機会があり試作とおもったのですが使いどころ自体でよろしくなかったようです。
ただ、JSON型の場合、どのようなSQL文になるのか知りたいという状況です。

[退会済みユーザー]

JSON型を使うべきではないので提示のしようがありません。

[BlueBits]

その通りですね。
そもそもこの構成でJSON型というのが的外れですね。
またJSON型を使う機会で問題に当たった時にご質問する形にいたします。

[退会済みユーザー]

MySQLのJSON型って5.7で初めて実装されたものなので、インデックスが効かないとか、十分に関数が用意されていないとか、あまり出番のない方ではないかと思います。実際実運用で使ったことないです。もう少し、枯れてから使うと考えてよいかと思います。

[BlueBits]

その通りですね。今回JSON型をと思ったのもNoSQLを使う要素が出てきそう。 ただNoSQLは入れたくない。そういえばMySQLにJSON型実装された。NoSQLの代用、どんな感じにできるかというのが始点です。
いざ本番でJSON型を使用して？？？とつまづくよりは別解（中間テーブル）がある中で模索をとおもったのですがあまりにも適切ではなかったですね。

[退会済みユーザー]

JSON_SEARCH という関数があるようなので、以下のクエリで試してみました。

SELECT * FROM `Groups` G
INNER JOIN `Users` U ON JSON_SEARCH(G.json_user, 'one', U.id) IS NOT NULL;

これ、エラーにならないけど、結果が返っってこない。時期尚早なのでしょうね。

[BlueBits]

JSON_SEARCHについてありがとうございます。結果からお伝えいたしますと望んだ結果を得ることができました。（MySQL Ver 8.0.11）

SELECT G.*,GROUP_CONCAT(U.name SEPARATOR '、') AS join_user_name
FROM `groups` AS G
INNER JOIN `users` AS U ON JSON_SEARCH(G.join_user,'one', U.id) IS NOT NULL
GROUP BY G.id;

ただ頂いた回答も踏まえますと、堅実に設計したほうがいいですね。
望んだ結果はでてますが、不安しか残りません。
適切な設計ではないのに調べていただきありがとうございました。

[退会済みユーザー]

自分の環境5.7とは動きが異なるようですね〜

[BlueBits]

今回、実験用にVer 8を入れて試していましたが、当分は実用には無理ですね・・・
遅い時間まで付き合っていただきありがとうございました。
使える、使えないは別として望んだ回答も得られましたので締めさせていただきます。
ありがとうございました。

Answer 3

1:Nの関係なので、ユーザーテーブルにグループID列を定義するのが、RDBのテーブル設計としてのベストプラクティスです。

Comments

[sagami1991]

間違えました。N:Nなのですね。
だとしたら中間テーブルを作るのが普通だと思います。

[sagami1991]

この設計方法だと、例えばAさんの所属グループを調べたいときに、グループテーブルを全行問い合わせることになります。
N:Nのときは中間テーブル（グループID列, ユーザーID列）を作成するのがRDB設計としてのベストプラクティスです。

[BlueBits]

回答ありがとうございます。ご教示頂いた通り、この構成だと中間テーブルが適切なのだと思います。
JSON型といってもただ配列を入れてるようなものですし・・・
今回、今後JSON型を使う機会があり試作の段階で当たった課題という形です。