【PostgreSQL】JSONデータを格納する方法

・JSONデータを保持する場合、テキスト型の列を使ってデータを保持する方法もある。

・しかし、JSON型を使うことで、オブジェクトを意識した検索を行ったり、という利便性を手に入れることができる。

・JSON型の列には、JSONとJSONBがあるが、JSONB（バイナリ保存）を使う方が高速に処理を行えるようになる。ただし、オブジェクト内の要素は順不同になるため、配列に対するインデックスアクセスはできなくなる。

・基本はjsonbを使えば良い。（保存するデータで配列を使わないようにするか、配列で保存しても良いが要素番号でアクセスしないようにする必要がある）

・以下に例を示す。

サンプルデータ

・検証用に3行格納する

{
    "items": {
        "design_tool_item1": {
            "type": "text",
            "style": "font-size: 88.4313px;",
            "content": "内祝"
        },
        "design_tool_item2": {
            "type": "text",
            "style": "font-size: 88.4313px; letter-spacing: 10px;",
            "content": "宮本太郎"
        }
    },
    "template": "noshi_1"
}

{
    "items": {
        "design_tool_item1": {
            "type": "text",
            "style": "font-size: 88.4313px;",
            "content": "御歳暮"
        },
        "design_tool_item2": {
            "type": "text",
            "style": "font-size: 88.4313px; letter-spacing: 10px;",
            "content": "北村太郎"
        }
    },
    "template": "noshi_1"
}

{
    "items": {
        "design_tool_item1": {
            "type": "text",
            "style": "font-size: 88.4313px;",
            "content": "御礼"
        },
        "design_tool_item2": {
            "type": "text",
            "style": "font-size: 88.4313px; letter-spacing: 10px;",
            "content": "中村太郎"
        }
    },
    "template": "noshi_1"
}

Json列に対するSELECT

・JSONB型の列にselectをかけてみる。

-- 指定列全データが出力される。text列同様。
SELECT json_1 FROM sample_json;

-- itemsオブジェクト内のデータが出力される。
SELECT json_1->'items' FROM sample_json;

-- itemsオブジェクト内のdesign_tool_item内のデータが出力される。
SELECT json_1->'items'->'design_tool_item1'->'content' FROM sample_json;

-- フィルタとして使用。-->は値をテキストにして返す。
SELECT * FROM sample_json WHERE json_1->'items'->'design_tool_item1' ->>'content' LIKE '%歳暮%';

-- フィルタとして使用。->はネストした要素にアクセスするために使用する。
SELECT * FROM sample_json WHERE (json_1->'items'->'design_tool_item1' ->'content')::text LIKE '%歳暮%';

参考

https://blog.serverworks.co.jp/postgres_jsonb_search

・公式
https://www.postgresql.jp/document/9.6/html/datatype-json.html

https://qiita.com/ma2shita/items/d491fed37d5ffe616198