[MySQL][ruby] json配列のkeyの数だけ列を生成する方法

Question

分からないこと

以下のようなデータを持つカラムがある場合にどのようにkeyの数分だけ列を生成するのか
わかりません。

テーブル名：member
カラム名：question（JSON）

[{"key": "今日のご飯", "value": "パン"}, {"key": "好きな食べ物", "value": "りんご"}]

• 質問の数は人によって違うので、**json_length(question)**は人によって変動してきます。

ある人はjson_length(question = 3)となる

[{"key": "今日のご飯", "value": "パン"}, {"key": "好きな食べ物", "value": "りんご"}, {"key": "嫌いな食べ物", "value": "なす"}]

知りたいこと

• memberテーブルに設定されているquestionの key を全て取得したい
• 取得した key の一覧から列情報を生成したい
• mysqlだけではできない場合にはrubyを使用すれば取得できるかも、と考えています。
• 最終的には以下のように生成したい

|メンバーID|今日のご飯|好きな食べ物|嫌いな食べ物|
|:--|:--:|--:|
|1|パン|りんご|null|
|2|パン|りんご|なす|

このような複雑な場合は、rubyを駆使して
抽出できるようにするのがベストなのでしょうか。

Answer 1

他の方が回答されているように正規化されたテーブルを持つのが良いと思います。

[
{"id": "1", "key": "今日のご飯", "value": "パン"},
{"id": "1", "key": "好きな食べ物", "value": "りんご"},
{"id": "2", "key": "今日のご飯", "value": "パン"},
{"id": "2", "key": "好きな食べ物", "value": "りんご"},
{"id": "2", "key": "嫌いな食べ物", "value": "なす"},
]

こういうデータが取り出せるテーブルをつくるということです。
もし、これができないなら力技になりそうです。

data = []
Member.select(:id, :questions).all.each do |line|
  member_id = line.id
  questions = JSON.parse(line.questions)
  questions.each do |hash|
    hash["id"] = member_id
    data << hash
  end
end

上記のようなハッシュの配列ができたら、あとはそれをPivotテーブルにすればよいと思います。

参考ソース：
https://github.com/gambaldia/rubypivot

Comments

[saider_san]

皆様、ご回答ありがとうございました！
参考にさせていただきます????‍♂️
大変助かりました。

Answer 2

ちゃんと集計をしたいなら、JSON型ではなくきちんと正規化したほうがよいでしょう

Answer 3

https://qiita.com/ka215/items/96d65dea4dcf8415ef71

の

select
  id,name,email,
  meta->'$.birthday' as birthday,
  meta->'$.prefecture' as prefecture,
  meta->'$.gender' as gender
from users;

のような感じですが、ご提示の例の場合同じ "key" が配列で並んでいるので、
行と列の変換が必要な気がします。

おそらくですが、MySQL だと列の数だけゴリゴリ書く必要があるのではないかと思います。
https://ja.stackoverflow.com/questions/22106/mysql%E3%81%AE%E8%A1%8C%E5%88%97%E5%A4%89%E6%8F%9B%E3%81%A7%E8%A4%87%E6%95%B0%E6%9D%A1%E4%BB%B6%E6%8C%87%E5%AE%9A

それをするくらいなら、JSON 文字列として取得し、Ruby 側で JSON パースして解析した方が簡単かもしれません。