SQL練習室
← SQLガイド

PostgreSQLのJSONBとは?->と->>の違い・入れ子の取り出し・@>での検索

jsonbは、JSON形式のデータをそのまま1つの列に保存できるPostgreSQLのデータ型です。商品ごとにブランド、価格、タグなど項目が違うデータを、列を増やさずに扱えます。->と->>で値を取り出し、@>で条件に合う行を探し、配列は行に展開して集計する、という流れで使い方を確認しましょう。標準SQLにもJSONを扱う構文はありますが、このページの演算子(->、->>、@>、?)は標準SQLの構文ではなく、PostgreSQLの書き方です。

例で使う表

productsは商品1件が1行で、attrsがjsonb列です。brand(ブランド)、price(価格・円)、tags(タグの配列)が入り、ノートPCとモニターにはspecs(仕様)という入れ子のオブジェクトもあります。jsonbはキーを短い順、同じ長さなら辞書順に並べて保存するため、表示するとtagsが先頭に来ます。ORDER BYのないSQLの行順は保証されません。

products:商品4件
idnameattrs
1ノートPC{"tags":["pc","work"],"brand":"Acme","price":98000,"specs":{"os":"Linux","ram":16}}
2マウス{"tags":["accessory","work"],"brand":"Boltix","price":2500}
3モニター{"tags":["display"],"brand":"Acme","price":32000,"specs":{"size":27}}
4キーボード{"tags":["accessory"],"brand":"Boltix","price":6800}

JSONBの基本構文

sql
SELECT name, attrs ->> 'brand' AS brand
FROM products
ORDER BY id;

->はjsonb、->>はtextで値を取り出す

attrs -> 'brand'はキーbrandの値をjsonbのまま返し、attrs ->> 'brand'は同じ値をtextとして返します。この結果表では両方ともAcmeと表示されますが、->の実体はJSONの文字列(二重引用符つきの"Acme")です。右辺をJSONとして書いた'"Acme"'と比べるとtrueになることで、jsonbのままだと分かります。WHEREで普通の文字列と比べたり、画面に表示したりする用途では->>を使います。さらに別のキーをたどりたいときは、jsonbを返す->でつなぎます。存在しないキーを指定してもエラーにはならず、NULLが返ります。

sql
SELECT
  name,
  attrs -> 'brand' AS brand_json,
  attrs ->> 'brand' AS brand_text,
  attrs -> 'brand' = '"Acme"' AS json_is_acme
FROM products
ORDER BY id;
実行結果
namebrand_jsonbrand_textjson_is_acme
ノートPCAcmeAcmetrue
マウスBoltixBoltixfalse
モニターAcmeAcmetrue
キーボードBoltixBoltixfalse

#>>と連続した->で入れ子の値を取り出す

specsの中のosのように入れ子になった値は、attrs #>> '{specs,os}'とキーの経路を配列リテラルで渡すと、textで取り出せます。attrs -> 'specs' ->> 'os'のように、途中は->、最後だけ->>でつないでも同じ結果です。途中のキーがない商品(マウス、キーボード)や、osキーがない商品(モニター)は、エラーではなくNULLになります。経路が深いときは#>>、1〜2段なら連続した->が読みやすくなります。

sql
SELECT
  name,
  attrs #>> '{specs,os}' AS os_path,
  attrs -> 'specs' ->> 'os' AS os_chain
FROM products
ORDER BY id;
実行結果
nameos_pathos_chain
ノートPCLinuxLinux
マウスNULLNULL
モニターNULLNULL
キーボードNULLNULL

->>の結果は数値にキャストして比べる

->>で取り出した値は、元が数値でもtextです。価格を数値として比べるには、(attrs ->> 'price')::integerのようにキャストします。5000円以上の商品は、ノートPC、モニター、キーボードの3件です。マウス(2500円)は除かれます。キャストを忘れて文字列のまま比べると、辞書順の比較になり結果が変わります(よくある間違いで確認します)。

sql
SELECT id, name
FROM products
WHERE (attrs ->> 'price')::integer >= 5000
ORDER BY id;
実行結果
idname
1ノートPC
3モニター
4キーボード

@>で指定した値を含む行を探す

attrs @> '{"tags":["work"]}'は、attrsの中にtagsが「workを含む配列」として入っている行を探す、包含検索です。右辺もjsonbとして書き、配列は要素を含んでいれば一致します。ノートPCとマウスが該当し、tagsにworkがないモニターとキーボードは除かれます。キーと値の組を複数並べると、すべてを満たす行だけが残ります。値の取り出しよりも、条件指定を簡潔に書きたいときに向いています。

sql
SELECT id, name
FROM products
WHERE attrs @> '{"tags":["work"]}'
ORDER BY id;
実行結果
idname
1ノートPC
2マウス

?でキーが存在する行を探す

attrs ? 'specs'は、最上位にspecsというキーがあるかをtrue・falseで調べます。ノートPCとモニターが該当します。?は最上位のキーだけを調べ、入れ子の中のキー(specsの中のsizeなど)は対象外です。キーがあるかだけを知りたく、値は問わないときに使います。複数のキーのうちどれか1つでもあれば?|、すべてあれば?&も使えます。

sql
SELECT id, name
FROM products
WHERE attrs ? 'specs'
ORDER BY id;
実行結果
idname
1ノートPC
3モニター

jsonb_array_elements_textで配列を行に展開する

tagsのような配列は、jsonb_array_elements_text(attrs -> 'tags')で、要素ごとに1行へ展開できます。FROMに並べると、商品の行と展開した行が組み合わされ、ノートPCは2行、モニターは1行になります。展開後はWHEREやGROUP BYで普通の列と同じように扱えます。textではなくjsonbのまま取り出す場合はjsonb_array_elementsを使います。

sql
SELECT p.name, t.tag
FROM products AS p, jsonb_array_elements_text(p.attrs -> 'tags') AS t(tag)
ORDER BY p.id, t.tag;
実行結果
nametag
ノートPCpc
ノートPCwork
マウスaccessory
マウスwork
モニターdisplay
キーボードaccessory

JSONBでよくある間違い

->>の結果を数値と比べたつもりで文字列比較になる

->>はtextを返すため、attrs ->> 'price' >= '5000'は文字列の辞書順比較です。'32000'は先頭の3が5より小さいので除かれ、'6800'や'98000'は残ります。モニター(32000円)が漏れるので、数値で比べるときは必ず::integerや::numericにキャストします。なお、->の結果(jsonb)と'Acme'のような普通の文字列を=で比べようとすると、右辺がJSONとして不正なためエラーになります。文字列と比べるなら->>を使います。

sql
SELECT id, name
FROM products
WHERE attrs ->> 'price' >= '5000'
ORDER BY id;

実際に1問解いてみる

->>

productsのattrs(jsonb)からbrandをtextとして取り出し、商品名nameとあわせて取得してください。brandの列には別名brandを付けます。行順は問いません。

テーブル

読み込み中…

実行結果

関連構文

JSONBについてよくある質問

->と->>はどう使い分けますか?
->はjsonbを返すので、さらに->でたどったり、@>で比べたりするときに使います。->>はtextを返すので、画面に表示する、文字列と比べる、数値へキャストする、といった最終的な値の取り出しに使います。
存在しないキーを取り出すとエラーになりますか?
->、->>、#>>は、キーや経路が存在しなくてもエラーにならずNULLを返します。そのため、キー名を打ち間違えても結果が空になるだけで、気づきにくい点に注意してください。
jsonbの中の数値を合計するにはどうしますか?
->>でtextとして取り出し、::integerや::numericにキャストしてからSUMに渡します。例えばSUM((attrs ->> 'price')::integer)のように書きます。
キーの順序が保持されないのはなぜですか?
jsonbは検索を速くするため、解析済みの形式で保存します。その際にキーを短い順、同じ長さなら辞書順に並べ替えるので、入力時の順序は残りません。順序を保存したい特別な事情があるときだけjsonを使います。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。