JSON フィールドに保存された ID に基づいて MySQL テーブルを結合するにはどうすればよいですか?

テーブルの結合に MySQL JSON フィールドを使用する

JSON フィールドにデータを保存すると柔軟性が得られますが、データ操作を実行するときに課題も生じます。この問い合わせの目的は、ID の配列を格納する JSON フィールドを使用してテーブルを結合することです。

問題ステートメント

ユーザーは 2 つのテーブルを作成しました。 user_group、および ID のリストを保存するユーザー テーブル内の架空の user_groups JSON フィールド。ユーザーは、この JSON フィールドで操作を実行し、それを SQL で使用したいと考えています。

具体的には、ユーザーは、ユーザー ID、ユーザー名、ユーザー グループ ID、およびグループ名をリストする結果を取得しようとしています。これらの結果は、JSON フィールドに保存されている ID に基づいて結合する必要があります。

ソリューション

これを実現するには、ユーザーは JSON_CONTAINS 関数を利用できます。この関数を使用すると、ユーザーは JSON ドキュメントに特定の値またはサブクエリが含まれているかどうかを確認できます。

コード

<code class="sql">SELECT 
       u.user_id, 
       u.user_name, 
       g.user_group_id,
       g.group_name
   FROM user u
   LEFT JOIN user_group g on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id as JSON), '$')</code>

説明

• JSON_CONTAINS 関数は、user_group テーブルの各行の user_groups JSON フィールドに、user_group テーブルの対応する user_group_id が含まれているかどうかを確認します。 $ を使用して、JSON ドキュメント内の最上位オブジェクトを示します。
• CAST 式は、user_group_id を JSON フィールドと比較する前に JSON 文字列に変換します。
• 比較の結果user テーブルと user_group テーブルの間で左結合を実行するために使用され、一致するグループがない場合でも、すべてのユーザー行が確実に含まれるようになります。
• その後、クエリは目的の列を選択します: user_id、user_name、user_group_id、

代替ソリューション

ユーザーが希望する場合は、JSON_TABLE 関数を使用して JSON フィールドから ID の配列を抽出し、仮想テーブル。このアプローチにより、データをより柔軟に操作できるようになります。

コード

<code class="sql">SELECT 
       u.user_id, 
       u.user_name, 
       uv.user_group_id
       (
           SELECT 
               g.group_name
           FROM user_group g
           WHERE g.user_group_id = uv.user_group_id
       ) AS group_name
   FROM user u
   CROSS JOIN JSON_TABLE(
       u.user_groups,
       '$[*]' COLUMNS (user_group_id INT PATH '$')
   ) uv</code>

説明

• JSON_TABLE 関数は、user_groups JSON フィールドから仮想テーブル uv を作成します。
• CROSS JOIN 句により、ユーザー テーブルの各行が仮想テーブルのすべての行に関連付けられます。
• ネストされたサブクエリUV 仮想テーブル内の各 user_group_id の group_name を取得します。

これらの手法を活用することで、ユーザーは JSON フィールドを効果的に利用してテーブルを結合し、MySQL で複雑なデータ操作を実行できます。

以上がJSON フィールドに保存された ID に基づいて MySQL テーブルを結合するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。