PostgreSQLには、JSONデータを管理する型としてjson型とjsonb型があります。
これらは登録は簡単なのですが、検索して利用する際は少し癖があるので、解説しようとお思います。

PostgreSQL version: v12.6

json型とjsonb型の違い

ドキュメント から抜粋しました。
キー順序や重複を利用するような特殊ケースを除き、jsonb型を利用する方が良さそうです。

json型 jsonb型
入力テキストの正確なコピーを格納
（空白、キー順序、重複キーなども保持）		 入力テキストをパースした結果をバイナリ形式で格納
（空白、キー順序は保持しない。重複キー重複キーは最後の値だけを保持）
利用時に再解析する必要があるため、利用時に時間がかかる 格納時にオーバヘッドがあるが、利用時は高速に利用できる
インデックスをサポートしていない インデックスをサポートしている

データ登録

peopleテーブルにdetailというjsonb型の項目を登録するサンプルになります。
こちらのデータを元に後続のSQLの検証を行います。

create table people (
  detail jsonb
);
insert into people(detail)
values ('{"name": "太郎", "age": 35, "children": [{"name": "二郎", "age": 9}, {"name": "三郎", "age": 6}]}')
;

JSON演算子でJSONを扱う

ドキュメントを元に解説したいと思います。
演算子は、->・->>・#>・#>>の四つがあります。

記号の意味

JSON演算子は、-・#・>・>>の記号の組合せで表現されます。
記号に意味があるので、覚えておくと良いと思います。

  • -: 子要素を取得する
  • #: パスを指定して要素を取得する
  • >: JSONオブジェクトとして要素を取得する
  • >>: テキストとして要素を取得する

また、子要素を取得する際は、以下の二つの指定方法があります

  • JSONオブジェクトの場合
    • keyを指定する
    • '{"hoge": "fuga"}'::json->>'hoge' で fuga を取得できる
  • JSON配列の場合
    • 添字を指定する
    • '["hoge", "fuga"]'::json->>0 で hoge を取得できる

具体例

people.detailの内容に対して検索をかける形で記載していきますが、以下の二つのSQLは同じ結果になります。

select detail->'name' from people;
select '{"name": "太郎", "age": 35, "children": [{"name": "二郎", "age": 9}, {"name": "三郎", "age": 6}]}'::jsonb->'name';

子要素を取得（->>,->）

  • ->>: 子要素をテキストとして取得する

    select detail->>'name' from people; --> "太郎"(text型)
select detail->>'age' from people; --> "35"(text型)
select detail->>'children' from people; --> "[{"age": 9, "name": "二郎"}, {"age": 6, "name": "三郎"}]"(text型)

  • ->: 子要素をjsonオブジェクトとして取得する

    select detail->'name' from people; --> 太郎(jsonb型)
select detail->'age' from people; --> 35(jsonb型)
select detail->'children' from people; --> [{"age": 9, "name": "二郎"}, {"age": 6, "name": "三郎"}](jsonb型)

  • ->はjson/jsonb型を返すので、演算子を重ねて孫・曽孫・・・要素を取得することができる

    select detail->'children'->0 from people; --> {"age": 9, "name": "二郎"}(jsonb型)
select detail->'children'->0->'name' from people; --> 二郎(jsonb型)
select detail->'children'->>0 from people; --> "{"age": 9, "name": "二郎"}"(text型)

パス指定で子や孫要素を直接取得する（#>>,#>）

->を使って、順番に要素を辿っていくことはできますが、パスを指定して一気に取得することもできます。

  • #>>: パス指定した要素をテキストとして取得する

    select detail#>>'{children,0}' from people; --> {"age": 9, "name": "二郎"}(text型)
select detail#>>'{children,0,name}' from people; --> 二郎(text型)

  • #>: パス指定した要素をJSONオブジェクトとして取得する

    select detail#>'{children,0}' from people; --> {"age": 9, "name": "二郎"}(jsonb型)
select detail#>'{children,0,name}' from people; --> 二郎(jsonb型)

JSON関数

JSON作成関数とJSON処理関数があります。
JSON作成関数は見ればわかる内容なので、特にここでは解説しません。
json型を扱う場合はjson_xxx、jsonb型を扱う場合はjsonb_xxxという関数名になりますが、使い方はどちらも同じです。
ここではjson型を扱う関数をいくつか紹介します。

json_array_length： JSON配列の長さを返す

select json_array_length('["aaa", "bbb", "ccc"]'::json);
--
3

json_each: JSONオブジェクトをkey/value組合せの集合を返す

JSONオブジェクトをkey/value組合せで別レコードに分けて、record型で返します。

select json_each('{"age": 9, "name": "二郎", "metas": ["hoge", "fuga"]}'::json);
--
(age,9)
(name,"二郎")
(metas,["hoge", "fuga"])

record型のままだと扱いにくいので、自分はjson型に変換して、扱うようにしてます。

with records as (
  select json_each('{"age": 9, "name": "二郎", "metas": ["hoge", "fuga"]}'::json) as record
)
select
  row_to_json(record)->'key' as key
  , row_to_json(record)->'value' as value
from
  records
;
--
"age",9
"name","二郎"
"metas",["hoge", "fuga"]

json_object_keys: JSONオブジェクトのキーの集合を返す

select json_object_keys('{"age": 9, "name": "二郎", "metas": ["hoge", "fuga"]}'::json);
--
age
name
metas

JSONオブジェクトの値の集合を返す

json_object_values関数は存在しませんので、同等のことがやりたい場合は、
json_eachでrecord型の集合に変換して、row_to_jsonでjson型に戻して、->'value'で値だけ取得するようなSQLを組む必要があります。

select row_to_json(json_each('{"age": 9, "name": "二郎", "metas": ["hoge", "fuga"]}'::json))->'value';
--
9
"二郎"
["hoge", "fuga"]

json_array_elements: JSON配列をレコード分割して返す

select json_array_elements('["aaa", "bbb", "ccc"]'::json);
--
"aaa"
"bbb"
"ccc"

JSON配列をarray型に変関する

JSON配列をarray型に直接変換する関数は存在しません（たぶん）。
そのため、一旦json_array_elementsやjson_array_elements_text でレコードに分けて、
それをarray_aggで改めてarray型に集約する必要があります。

with data as (
  select json_array_elements_text('["aaa", "bbb", "ccc"]'::json) as record
)
select
  array_agg(record)
from
  data
;
--
{aaa,bbb,ccc}

array型になっていれば、array関数を自由に使えるので、色々加工できます。

with data as (
  select json_array_elements_text('["aaa", "bbb", "ccc"]'::json) as record
)
select
  concat('【', array_to_string(array_agg(record), '】【'), '】')
from
  data
;
--
【aaa】【bbb】【ccc】
関連記事
  1. pythonのTips
  2. BigQueryのTips
  3. PostgreSQLのTips
  4. OracleのTips
  5. 標準モジュールunittestでpythonのテストを書く