Does it taste good?

「そんなことも知らないの？」って言わないでほしい。

PostgresqlでJSONと少し戦う

postgresql JSON

やりたいこと

"[{"id":"hoge", "content":"hogehoge",...}]"

というtext形式の列（列名sections（仮））の中のcontent部分を抽出したい。 あんまり実践的な資料がなかったので書いときます。

抽出方針

  1. 一旦この文字列をJSONに変換
  2. その中のcontent要素を抽出する

結論

色々詰まったけども

with

cont as (
  select
    sections::json -> 0 ->> 'content' as content
  from
    contents_table
  where
    created_at between '2018-04-01' and '2018-04-07'
)

select
  *
from
  cont
;

こんな感じでいける。

  1. sections::jsonでsections列をまるっとJSONに変換
  2. -> 0で0番目（最初）の要素を抽出する
  3. ->> 'content'でcontentオブジェクトのフィールドをtextとして抽出

詰まったポイントは

  • 最初からJSONで入ってる列じゃなかったから一旦text→JSONの変換が必要
  • JSON変換のところでrow_to_jsonなど他の関数を使ったほうがいいのかと思って色々試す
  • JSON変換してcontentの中身取得すればいいわけではなく、一旦-> 0で0番目（最初）の要素を抽出する、という工程が必要だった

でした。

参考文献