【PostgreSQL】text 型のカラムに json 型としてクエリする

PostgreSQLのtext型のカラムにjson型としてクエリする

以下のデータからJSONデータを抽出したテーブルを表示する。
ここでsettingカラムの型はtextとする。

# select * from settings;
 id |           setting
----+------------------------------
  1 | {"foo": "bar", "baz": "qux"}
  2 | {"foo": "aaa", "baz": "bbb"}

settingカラムのJSONデータの、“foo”, “baz"キーをカラム名とした値を取得しテーブルとして表示するには例えば以下のようなSQLを実行する。

# select id, setting::json->>'foo' as foo, setting::json->>'baz' as baz from settings;
 id | foo | baz
----+-----+-----
  1 | bar | qux
  2 | aaa | bbb

JSONデータ型

文字列をJSONデータ型にキャスト

PostgreSQLにはJSONデータ型があり、文字列::jsonとすると文字列をjsonデータ型としてキャストできる。

# select '{"foo": "bar"}'::json;
      json
----------------
 {"foo": "bar"}

≫ JSONデータ型

JSON演算子

JSONデータ型の値に対してJSON演算子が用意されており、->>演算子を使用すると指定したキーの値が取得できる。

以下の形で記述する。

JSONデータ型の値->>キー

{"foo": "bar"}というJSONからfooというキーを指定して値を取得するには以下のように記述する。

# select '{"foo": "bar"}'::json->>'foo';
 ?column?
----------
 bar

≫ JSON関数と演算子