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の行順は保証されません。
| id | name | attrs |
|---|---|---|
| 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の基本構文
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が返ります。
SELECT
name,
attrs -> 'brand' AS brand_json,
attrs ->> 'brand' AS brand_text,
attrs -> 'brand' = '"Acme"' AS json_is_acme
FROM products
ORDER BY id;| name | brand_json | brand_text | json_is_acme |
|---|---|---|---|
| ノートPC | Acme | Acme | true |
| マウス | Boltix | Boltix | false |
| モニター | Acme | Acme | true |
| キーボード | Boltix | Boltix | false |
#>>と連続した->で入れ子の値を取り出す
specsの中のosのように入れ子になった値は、attrs #>> '{specs,os}'とキーの経路を配列リテラルで渡すと、textで取り出せます。attrs -> 'specs' ->> 'os'のように、途中は->、最後だけ->>でつないでも同じ結果です。途中のキーがない商品(マウス、キーボード)や、osキーがない商品(モニター)は、エラーではなくNULLになります。経路が深いときは#>>、1〜2段なら連続した->が読みやすくなります。
SELECT
name,
attrs #>> '{specs,os}' AS os_path,
attrs -> 'specs' ->> 'os' AS os_chain
FROM products
ORDER BY id;| name | os_path | os_chain |
|---|---|---|
| ノートPC | Linux | Linux |
| マウス | NULL | NULL |
| モニター | NULL | NULL |
| キーボード | NULL | NULL |
->>の結果は数値にキャストして比べる
->>で取り出した値は、元が数値でもtextです。価格を数値として比べるには、(attrs ->> 'price')::integerのようにキャストします。5000円以上の商品は、ノートPC、モニター、キーボードの3件です。マウス(2500円)は除かれます。キャストを忘れて文字列のまま比べると、辞書順の比較になり結果が変わります(よくある間違いで確認します)。
SELECT id, name
FROM products
WHERE (attrs ->> 'price')::integer >= 5000
ORDER BY id;| id | name |
|---|---|
| 1 | ノートPC |
| 3 | モニター |
| 4 | キーボード |
@>で指定した値を含む行を探す
attrs @> '{"tags":["work"]}'は、attrsの中にtagsが「workを含む配列」として入っている行を探す、包含検索です。右辺もjsonbとして書き、配列は要素を含んでいれば一致します。ノートPCとマウスが該当し、tagsにworkがないモニターとキーボードは除かれます。キーと値の組を複数並べると、すべてを満たす行だけが残ります。値の取り出しよりも、条件指定を簡潔に書きたいときに向いています。
SELECT id, name
FROM products
WHERE attrs @> '{"tags":["work"]}'
ORDER BY id;| id | name |
|---|---|
| 1 | ノートPC |
| 2 | マウス |
?でキーが存在する行を探す
attrs ? 'specs'は、最上位にspecsというキーがあるかをtrue・falseで調べます。ノートPCとモニターが該当します。?は最上位のキーだけを調べ、入れ子の中のキー(specsの中のsizeなど)は対象外です。キーがあるかだけを知りたく、値は問わないときに使います。複数のキーのうちどれか1つでもあれば?|、すべてあれば?&も使えます。
SELECT id, name
FROM products
WHERE attrs ? 'specs'
ORDER BY id;| id | name |
|---|---|
| 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を使います。
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;| name | tag |
|---|---|
| ノートPC | pc |
| ノートPC | work |
| マウス | accessory |
| マウス | work |
| モニター | display |
| キーボード | accessory |
JSONBでよくある間違い
->>の結果を数値と比べたつもりで文字列比較になる
->>はtextを返すため、attrs ->> 'price' >= '5000'は文字列の辞書順比較です。'32000'は先頭の3が5より小さいので除かれ、'6800'や'98000'は残ります。モニター(32000円)が漏れるので、数値で比べるときは必ず::integerや::numericにキャストします。なお、->の結果(jsonb)と'Acme'のような普通の文字列を=で比べようとすると、右辺がJSONとして不正なためエラーになります。文字列と比べるなら->>を使います。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:JSON型 — jsonとjsonbの違い、jsonbの包含・存在判定、インデックスの考え方
- PostgreSQL公式:JSON関数と演算子 — ->、->>、#>>、@>、?、jsonb_array_elements_textの仕様