検証すること
- どのリレーショナルDBシステムを使うか決める
- 生データと、自分で使う用の表形式のDBの保存をどうやって行うか理解し、決める

postgresql で JSONBを使う

まず生データを取得して、それをissueごと&レビューされたPRごとに {...} (配列の中の要素一つずつ)で保存できるかやってみる

raw_issues (割り当てられたissue)

bin/rails g model raw_issues issue:jsonb

マイグレーションファイル

class CreateRawIssues < ActiveRecord::Migration[7.0]
  def change
    create_table :raw_issues do |t|
      t.jsonb :issue, null: false

      t.timestamps
    end
  end
end

bin/rails db:migrate
他のファイルとかはとくに作らずに、railsコンソールで保存できるかやってみた

require "json"
require "net/http"
require "uri"

# サインアップしたユーザーのリスト(自作アプリのDBに既に保存されていると仮定)
users = [
          { id: 58052292, username: "Saki-htr" },
          { id: 20497053, username: "masanarih0ri" },
          { id: 11376040, username: "SatoshiHaramura" },
          { id: 49633473, username: "Nabegon" }
        ]

users.each do |user|
  uri = URI.parse("<https://api.github.com/repos/fjordllc/bootcamp/issues?assignee=#{user[:username]}&state=all>")
  json = Net::HTTP.get(uri)
  array = JSON.parse(json, symbolize_names: true)
  array.each do |data|
    raw_issue = RawIssue.new
    json_data = data.to_json
    raw_issue.issue = json_data
    raw_issue.save
  end
end

保存したデータを一つ見てみる

> RawIssue.first
  RawIssue Load (4.4ms)  SELECT "raw_issues".* FROM "raw_issues" ORDER BY "raw_issues"."id" ASC LIMIT $1  [["LIMIT", 1]]
=>                                                                  
#<RawIssue:0x0000000110174ce0                                       
 id: 111,                                                           
 issue:   #JSONで{...}の形で保存できてそう                                                           
  "{\"url\":\"<https://api.github.com/repos/fjordllc/bootcamp/issues/5071\",\"repository_url\":\"https://api.github.com/repos/fjordllc/bootcamp\",\"labels_url\":\"https://api.github.com/repos/fjordllc/bootcamp/issues/5071/labels{/name}\",\"comments_url\":\"https://api.github.com/repos/fjordllc/bootcamp/issues/5071/comments\",\"events_url\":\"https://api.github.com/repos/fjordllc/bootcamp/issues/5071/events\",\"html_url\":\"https://github.com/fjordllc/bootcamp/pull/5071\",\"id\":1283095617,\"node_id\":\"PR_kwDOAE5q5846R3Kq\",\"number\":5071,\"title\":\"Release> 2022-06-30 04:04:17\",\"user\":{\"login\":\"github-actions[bot]\",\"id\":41898282,\"node_id\":\"MDM6Qm90NDE4OTgyODI=\",\"avatar_url\":\"<https://avatars.githubusercontent.com/in/15368?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/github-actions%5Bbot%5D\",\"html_url\":\"https://github.com/apps/github-actions\",\"followers_url\":\"https://api.github.com/users/github-actions%5Bbot%5D/followers\",\"following_url\":\"https://api.github.com/users/github-actions%5Bbot%5D/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/github-actions%5Bbot%5D/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/github-actions%5Bbot%5D/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/github-actions%5Bbot%5D/subscriptions\",\"organizations_url\":\"https://api.github.com/users/github-actions%5Bbot%5D/orgs\",\"repos_url\":\"https://api.github.com/users/github-actions%5Bbot%5D/repos\",\"events_url\":\"https://api.github.com/users/github-actions%5Bbot%5D/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/github-actions%5Bbot%5D/received_events\",\"type\":\"Bot\",\"site_admin\":false},\"labels\":[{\"id\":2733584529,\"node_id\":\"MDU6TGFiZWwyNzMzNTg0NTI5\",\"url\":\"https://api.github.com/repos/fjordllc/bootcamp/labels/release\",\"name\":\"release\",\"color\":\"ededed\",\"default\":false,\"description\":null}],\"state\":\"closed\",\"locked\":false,\"assignee\":{\"login\":\"machida\",\"id\":168265,\"node_id\":\"MDQ6VXNlcjE2ODI2NQ==\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/168265?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/machida\",\"html_url\":\"https://github.com/machida\",\"followers_url\":\"https://api.github.com/users/machida/followers\",\"following_url\":\"https://api.github.com/users/machida/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/machida/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/machida/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/machida/subscriptions\",\"organizations_url\":\"https://api.github.com/users/machida/orgs\",\"repos_url\":\"https://api.github.com/users/machida/repos\",\"events_url\":\"https://api.github.com/users/machida/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/machida/received_events\",\"type\":\"User\",\"site_admin\":false},\"assignees\":[{\"login\":\"machida\",\"id\":168265,\"node_id\":\"MDQ6VXNlcjE2ODI2NQ==\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/168265?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/machida\",\"html_url\":\"https://github.com/machida\",\"followers_url\":\"https://api.github.com/users/machida/followers\",\"following_url\":\"https://api.github.com/users/machida/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/machida/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/machida/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/machida/subscriptions\",\"organizations_url\":\"https://api.github.com/users/machida/orgs\",\"repos_url\":\"https://api.github.com/users/machida/repos\",\"events_url\":\"https://api.github.com/users/machida/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/machida/received_events\",\"type\":\"User\",\"site_admin\":false},{\"login\":\"SatoshiHaramura\",\"id\":11376040,\"node_id\":\"MDQ6VXNlcjExMzc2MDQw\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/11376040?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/SatoshiHaramura\",\"html_url\":\"https://github.com/SatoshiHaramura\",\"followers_url\":\"https://api.github.com/users/SatoshiHaramura/followers\",\"following_url\":\"https://api.github.com/users/SatoshiHaramura/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/SatoshiHaramura/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/SatoshiHaramura/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/SatoshiHaramura/subscriptions\",\"organizations_url\":\"https://api.github.com/users/SatoshiHaramura/orgs\",\"repos_url\":\"https://api.github.com/users/SatoshiHaramura/repos\",\"events_url\":\"https://api.github.com/users/SatoshiHaramura/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/SatoshiHaramura/received_events\",\"type\":\"User\",\"site_admin\":false},{\"login\":\"siso25\",\"id\":47073105,\"node_id\":\"MDQ6VXNlcjQ3MDczMTA1\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/47073105?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/siso25\",\"html_url\":\"https://github.com/siso25\",\"followers_url\":\"https://api.github.com/users/siso25/followers\",\"following_url\":\"https://api.github.com/users/siso25/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/siso25/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/siso25/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/siso25/subscriptions\",\"organizations_url\":\"https://api.github.com/users/siso25/orgs\",\"repos_url\":\"https://api.github.com/users/siso25/repos\",\"events_url\":\"https://api.github.com/users/siso25/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/siso25/received_events\",\"type\":\"User\",\"site_admin\":false},{\"login\":\"shibaaaa\",\"id\":56685224,\"node_id\":\"MDQ6VXNlcjU2Njg1MjI0\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/56685224?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/shibaaaa\",\"html_url\":\"https://github.com/shibaaaa\",\"followers_url\":\"https://api.github.com/users/shibaaaa/followers\",\"following_url\":\"https://api.github.com/users/shibaaaa/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/shibaaaa/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/shibaaaa/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/shibaaaa/subscriptions\",\"organizations_url\":\"https://api.github.com/users/shibaaaa/orgs\",\"repos_url\":\"https://api.github.com/users/shibaaaa/repos\",\"events_url\":\"https://api.github.com/users/shibaaaa/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/shibaaaa/received_events\",\"type\":\"User\",\"site_admin\":false},{\"login\":\"Saki-htr\",\"id\":58052292,\"node_id\":\"MDQ6VXNlcjU4MDUyMjky\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/58052292?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/Saki-htr\",\"html_url\":\"https://github.com/Saki-htr\",\"followers_url\":\"https://api.github.com/users/Saki-htr/followers\",\"following_url\":\"https://api.github.com/users/Saki-htr/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/Saki-htr/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/Saki-htr/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/Saki-htr/subscriptions\",\"organizations_url\":\"https://api.github.com/users/Saki-htr/orgs\",\"repos_url\":\"https://api.github.com/users/Saki-htr/repos\",\"events_url\":\"https://api.github.com/users/Saki-htr/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/Saki-htr/received_events\",\"type\":\"User\",\"site_admin\":false},{\"login\":\"niikz\",\"id\":60736158,\"node_id\":\"MDQ6VXNlcjYwNzM2MTU4\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/60736158?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/niikz\",\"html_url\":\"https://github.com/niikz\",\"followers_url\":\"https://api.github.com/users/niikz/followers\",\"following_url\":\"https://api.github.com/users/niikz/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/niikz/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/niikz/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/niikz/subscriptions\",\"organizations_url\":\"https://api.github.com/users/niikz/orgs\",\"repos_url\":\"https://api.github.com/users/niikz/repos\",\"events_url\":\"https://api.github.com/users/niikz/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/niikz/received_events\",\"type\":\"User\",\"site_admin\":false},{\"login\":\"rosh-1228\",\"id\":64620506,\"node_id\":\"MDQ6VXNlcjY0NjIwNTA2\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/64620506?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/rosh-1228\",\"html_url\":\"https://github.com/rosh-1228\",\"followers_url\":\"https://api.github.com/users/rosh-1228/followers\",\"following_url\":\"https://api.github.com/users/rosh-1228/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/rosh-1228/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/rosh-1228/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/rosh-1228/subscriptions\",\"organizations_url\":\"https://api.github.com/users/rosh-1228/orgs\",\"repos_url\":\"https://api.github.com/users/rosh-1228/repos\",\"events_url\":\"https://api.github.com/users/rosh-1228/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/rosh-1228/received_events\",\"type\":\"User\",\"site_admin\":false},{\"login\":\"ai-24\",\"id\":76685187,\"node_id\":\"MDQ6VXNlcjc2Njg1MTg3\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/76685187?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/ai-24\",\"html_url\":\"https://github.com/ai-24\",\"followers_url\":\"https://api.github.com/users/ai-24/followers\",\"following_url\":\"https://api.github.com/users/ai-24/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/ai-24/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/ai-24/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/ai-24/subscriptions\",\"organizations_url\":\"https://api.github.com/users/ai-24/orgs\",\"repos_url\":\"https://api.github.com/users/ai-24/repos\",\"events_url\":\"https://api.github.com/users/ai-24/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/ai-24/received_events\",\"type\":\"User\",\"site_admin\":false},{\"login\":\"tomonariha\",\"id\":96340764,\"node_id\":\"U_kgDOBb4LHA\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/96340764?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/tomonariha\",\"html_url\":\"https://github.com/tomonariha\",\"followers_url\":\"https://api.github.com/users/tomonariha/followers\",\"following_url\":\"https://api.github.com/users/tomonariha/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/tomonariha/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/tomonariha/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/tomonariha/subscriptions\",\"organizations_url\":\"https://api.github.com/users/tomonariha/orgs\",\"repos_url\":\"https://api.github.com/users/tomonariha/repos\",\"events_url\":\"https://api.github.com/users/tomonariha/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/tomonariha/received_events\",\"type\":\"User\",\"site_admin\":false},{\"login\":\"kazumi3858\",\"id\":97820517,\"node_id\":\"U_kgDOBdSfZQ\",\"avatar_url\":\"https://avatars.githubusercontent.com/u/97820517?v=4\",\"gravatar_id\":\"\",\"url\":\"https://api.github.com/users/kazumi3858\",\"html_url\":\"https://github.com/kazumi3858\",\"followers_url\":\"https://api.github.com/users/kazumi3858/followers\",\"following_url\":\"https://api.github.com/users/kazumi3858/following{/other_user}\",\"gists_url\":\"https://api.github.com/users/kazumi3858/gists{/gist_id}\",\"starred_url\":\"https://api.github.com/users/kazumi3858/starred{/owner}{/repo}\",\"subscriptions_url\":\"https://api.github.com/users/kazumi3858/subscriptions\",\"organizations_url\":\"https://api.github.com/users/kazumi3858/orgs\",\"repos_url\":\"https://api.github.com/users/kazumi3858/repos\",\"events_url\":\"https://api.github.com/users/kazumi3858/events{/privacy}\",\"received_events_url\":\"https://api.github.com/users/kazumi3858/received_events\",\"type\":\"User\",\"site_admin\":false}],\"milestone\":null,\"comments\":0,\"created_at\":\"2022-06-24T00:07:27Z\",\"updated_at\":\"2022-06-30T06:49:45Z\",\"closed_at\":\"2022-06-30T06:49:44Z\",\"author_association\":\"NONE\",\"active_lock_reason\":null,\"draft\":false,\"pull_request\":{\"url\":\"https://api.github.com/repos/fjordllc/bootcamp/pulls/5071\",\"html_url\":\"https://github.com/fjordllc/bootcamp/pull/5071\",\"diff_url\":\"https://github.com/fjordllc/bootcamp/pull/5071.diff\",\"patch_url\":\"https://github.com/fjordllc/bootcamp/pull/5071.patch\",\"merged_at\":\"2022-06-30T06:49:44Z\"},\"body\":\"-> [x] #4932 未ログイン時にユーザーアイコンメンションをブログに表示できるようにした @rosh-1228\\n- [x] #4954 未完了の提出物一覧を担当メンターで絞り込めるようにした @Saki-htr\\n- [x] #4981 Q\\u0026amp;Aを削除する際に表示する確認ダイアログの文言を変更 @ai-24\\n- [x] #4998 QAの回答投稿時にプレビューがクリアされるように修正 @siso25\\n- [x] #5004 ユーザー個別ページの日報一覧のtitleタグの文言を変更 @SatoshiHaramura\\n- [x] #5025 参考書籍ページで各書籍に紐付くプラクティスへのリンクを追加 @SatoshiHaramura\\n- [x] #5041 質問の投稿の通知をabstract_notifierに置き換えた @kazumi3858\\n- [x] #5049 現在のパラメーターを引き継ぐようにした @niikz\\n- [x] #5051 Q\\u0026amp;Aのパラメーターを変更 @niikz\\n- [x] #5054 検索結果がユーザーだった場合にユーザーアイコンが表示されるようにした @tomonariha\\n- [x] #5055 Q\\u0026amp;Aのプラクティス選択をvue-multiselectからChoices.jsへ置き換え @niikz\\n- [x] #5069 ブログのサムネイル画像アップロードUIを変更 @machida@niikz\\n- [x] #5073 初回日報通知（サイト内通知）をAbstract Notifierに置き換えました @ai-24\\n- [x] #5079 呼ばれなくなった変数を削除 @shibaaaa\\n- [x] #5080 Bump diffy from 3.4.0 to 3.4.1 @dependabot[bot]\\n- [x] #5086 thumbnail upload のUIのデザイン調整 @machida\\n- [x] #5098 typoを修正 @komagata\\n- [x] #5100 不要な文字設定を削除 @machida\\n- [x] #5101 GitHubアカウント連携のUIの調整 @machida\\n- [x] #5111 vue componentの置き場所をcomponents以下に変更 @komagata\\n- [x] #5112 hello componentをVueMounterに対応した @komagata\\n- [x] #5113 announcementsをVueMounterに対応 @komagata\\n\",\"reactions\":{\"url\":\"<https://api.github.com/repos/fjordllc/bootcamp/issues/5071/reactions\",\"total_count\":0,\"+1\":0,\"-1\":0,\"laugh\":0,\"hooray\":0,\"confused\":0,\"heart\":0,\"rocket\":0,\"eyes\":0},\"timeline_url\":\"https://api.github.com/repos/fjordllc/bootcamp/issues/5071/timeline\",\"performed_via_github_app\":null,\"state_reason\":null}">,
 created_at: Fri, 15 Jul 2022 06:26:44.440021000 UTC +00:00,
 updated_at: Fri, 15 Jul 2022 06:26:44.440021000 UTC +00:00>

review_requested_pull_requests

bin/rails g model review_requested_pull_requests pull_request:jsonb