example use of CTEs in object-rewriting migration

remap_reviewer_ids.rb

def remap_reviewer_ids(table)
  connection.execute(<<~EOQ)
    WITH reviewer_map AS (
      SELECT reviewers.base_id, to_jsonb(array_agg(DISTINCT profiles.account_id))
      FROM profiles
      JOIN (SELECT jsonb_array_elements(options->'reviewer_ids')::text::int as reviewer_id, id AS base_id FROM #{table}) AS reviewers
      ON reviewers.reviewer_id = profiles.id
      GROUP BY base_id
    )
    UPDATE #{table}
    SET options = jsonb_set(options, '{reviewer_ids}', reviewer_map.to_jsonb)
    FROM reviewer_map
    WHERE #{table}.id = reviewer_map.base_id
  EOQ
end