タイトルを次のように書き換えます: JSON 配列を MySQL の行に変換する

Question

<p><strong>更新: </strong> これは、JSON_TABLE 関数経由で MySQL 8 で可能になりました: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions .html< /p> </p><p>MySQL 5.7 の新しい JSON 関数は気に入っていますが、JSON の値を通常のテーブル構造にマージしようとするときに問題が発生します。 </p> <p>JSON の取得、操作、配列の抽出などが簡単に行えます。 JSON_EXTRACT をずっと続けます。しかし、その逆、JSON 配列から行への場合はどうでしょうか?おそらく、既存の MySQL JSON 機能に興味があるのか​​もしれませんが、それを理解できませんでした。 </p> <p>たとえば、JSON 配列があり、配列内の各要素とその値の行を挿入するとします。私が見つけた唯一の方法は、一連の JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') などを記述して結合することです。 </p> <p>あるいは、JSON 配列があり、それを GROUP_CONCAT() して単一のカンマ区切り文字列にしたいとします。 </p> <p>言い換えれば、私はこれができることを知っています:</p> <pre class="brush:php;toolbar:false;">SET @j = '[1, 2, 3]'; SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val から ( SELECT 0 AS n 連合 n として 1 を選択 連合 SELECT 2 AS n 連合 3 を n として選択 連合 セレクト 4 AS n 連合 5 を n として選択 ） バツ WHERE x.n <p>でも目が痛いです。そして私の心。 </p> <p>このようなことをするにはどうすればよいですか:</p> <pre class="brush:php;toolbar:false;">SET @j = '[1, 2, 3]'; SELECT GROUP_CONCAT(JSON_EXTRACT(@j, '$[ * ]'))</pre> <p>...配列内の値を JSON 配列自体と連結しますか? </p> <p>ここで探しているのは、次のような JSON_SPLIT のようなものだと思います。 </p> <pre class="brush:php;toolbar:false;">SET @j = '[1, 2, 3]'; SELECT GROUP_CONCAT(val) から JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')</pre> <p>MySQL に適切な STRING_SPLIT(val, 'separator') テーブルを返す関数があれば、それをハッキングすることができますが (忌々しいエスケープ)、それも機能しません。 </p>

Answer 1

JSON に非正規化するのは良い考えではないのは事実ですが、場合によっては JSON データを処理する必要があり、JSON 配列をクエリ内の行に抽出する方法があります。

コツは、一時テーブルまたはインライン インデックス テーブルで結合を実行することです。これにより、JSON 配列内の null 以外の値ごとに行が得られます。つまり、値 0、1、および 2 を持つテーブルがあり、それを 2 つのエントリを含む JSON 配列「fish」に結合すると、Fish[0] は 0 と一致し、行が生成され、Fish になります。 1 は 1 に一致し、2 行目になりますが、fish[2] は null であるため、2 に一致せず、結合に行は生成されません。インデックス テーブルには、JSON データ内の配列の最大長と同じ数の数値が必要です。これはちょっとしたハックで、OP の例と同じくらい面倒ですが、非常に便利です。

例 (MySQL 5.7.8 以降が必要):

リーリー ＃＃＃消す：＃＃＃ リーリー

MySQL チームは、これを簡単にするために、MySQL 8 に

JSON_TABLE 関数を追加しているようです。 (http://mysqlserverteam.com/mysql-8-0-labs -json-aggregation-functions/) (MySQL チームは を追加しました ## JSON_TABLE 関数。)

Answer 2

MySQL 8 で JSON_TABLE を使用する方法は次のとおりです:

リーリー

また、区切り文字で区切られた文字列を取得して JSON 文字列に変換することで、MySQL にはない汎用の文字列分割関数 (PG の regexp_split_to_table や MSSQL の STRING_SPLIT と同様) として使用することもできます。 リーリー