-- 一時テーブル作成
-- JSON をデータディレクトリに置いて pg_read_file で読み込み
-- string_to_array で改行で分けて配列化、unnest で一行ずつに変換
-- 最後に JSONB にキャスト
--
CREATE TABLE hoge AS
SELECT  unnest(
    string_to_array(pg_read_file('20150530_example.json'), E'\n')
) :: jsonb;


-- GROUP BY クエリ用テーブル作成
-- JSONB のデータ部分をキー rows で取り出す. 結果は2次元配列
-- まず jsonb_array_elements で1次元（行）に変換
-- 次に ->> 演算子で列に変換、必要に応じてキャストや不要文字削除
-- 
CREATE TABLE test.ga_tb2 AS
SELECT
    ( ary->>0 ) :: date AS ymd,  -- 添字が0から（配列型は1から）
    ( ary->>1 ) :: int AS h24,
    ary->>2 AS country,
    ary->>3 AS os,
    ary->>4 AS browser,
    ary->>5 browser_version,
    replace (ary->>6, ' : 研究に使うポスグレ', '') page_title,
    ( ary->>7 ) :: int page_views
FROM (
    SELECT jsonb_array_elements(jsonb_dat -> 'rows') AS ary
    FROM hoge
) foo;