質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.50%

  • MySQL

    5858questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • JSON

    1155questions

    JSON(JavaScript Object Notation)は軽量なデータ記述言語の1つである。構文はJavaScriptをベースとしていますが、JavaScriptに限定されたものではなく、様々なソフトウェアやプログラミング言語間におけるデータの受け渡しが行えるように設計されています。

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 227

BlueBits

score 41

中間テーブルの作成が適切という回答をいただいております。構成自体が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’さん

上記の2テーブルを結合して最終的に以下の出力がしたいと考えています。
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文についてご教示いただきたくご質問いたしました。
何卒お願い致します。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

0

こんな感じ

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するフィールドには向かないと思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/10 01:03

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

    キャンセル

  • 2018/06/10 01:04

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

    キャンセル

  • 2018/06/10 01:17

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

    キャンセル

  • 2018/06/10 01:25

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

    キャンセル

  • 2018/06/10 01:34

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

    キャンセル

  • 2018/06/10 01:42

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

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

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

    キャンセル

  • 2018/06/10 02:13

    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;

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

    キャンセル

  • 2018/06/10 02:15

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

    キャンセル

  • 2018/06/10 02:19

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/10 00:24

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

    キャンセル

  • 2018/06/10 00:34

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

    キャンセル

  • 2018/06/10 00:50

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

    キャンセル

0

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))

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.50%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • MySQL

    5858questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • JSON

    1155questions

    JSON(JavaScript Object Notation)は軽量なデータ記述言語の1つである。構文はJavaScriptをベースとしていますが、JavaScriptに限定されたものではなく、様々なソフトウェアやプログラミング言語間におけるデータの受け渡しが行えるように設計されています。