はじめに
こんなJSONの配列データがあったとします。
[{"a":"foo"},{"b":"bar"},{"c":"baz"}]
PostgreSQL だとこう。
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json as example
| example |
|---|
| [{"a":"foo"},{"b":"bar"},{"c":"baz"}] |
こんなJSONのデータをPostgreSQL で扱う際の演算子と関数一覧です。json型のみでjsonb型は省いています。 元ネタは PostgreSQL 14.0文書です。
演算子
要素にアクセスするための演算子 ->
配列の添字を指定して要素にアクセスする。
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2 as second_element
| second_element |
|---|
| {"c":"baz"} |
配列の添字（逆順）を指定して配列の要素（オブジェクト）にアクセスする。
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -1 as last_element
| last_element |
|---|
| {"c":"baz"} |
配列の添字（逆順）を指定して配列の要素（オブジェクト）にアクセスし、さらにオブジェクトのキーを指定して値を取得する。
添字の指定にも、オブジェクトのキーの指定にも -> を利用する。
SELECT last_element -> 'c' as value -- {"c":"baz"} の key を指定してvalueを得る FROM ( SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]' :: json -> -1 as last_element ) as _
| value |
|---|
| "baz" |
要素にアクセスしてテキスト型で値を取得するための演算子 ->>
わかりづらいが、ここでの戻り値はテキスト型になっている。
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json ->> 0 as str
| str |
|---|
| {"a":"foo"} |
戻り値はテキスト型のため、JSON型データへの演算子を利用するとエラーになる。
SELECT str -> 'a' -- ここで str は '{"a":"foo"}'になっている。 FROM ( SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]' :: json ->> 0 as str ) as _
ERROR: operator does not exist: text -> unknown
LINE 1: SELECT str -> 'a' FROM (
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
-> と同様にオブジェクトのキーを指定して値にアクセスする場合にも利用できる。
SELECT '{"a":1,"b":2}'::json ->> 'b' as str
| str |
|---|
| 2 |
JSONのパスを指定して要素にアクセスするための演算子 #>
{}のなかに、 -> の引数になるような値を書き連ねていくことで、JSONの木構造を辿って要素にアクセスできる。
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json #> '{0}' as example
| example |
|---|
| {"a":"foo"} |
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json #> '{0,a}' as example
| example |
|---|
| "foo" |
JSONのパスを指定して要素にアクセスしてテキスト型で値を取得するための演算子 #>>
上記の別バージョンで、戻り値の型がテキスト型になる。
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json #>> '{0}' as str
| str |
|---|
| {"a":"foo"} |
SELECT '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json #>> '{0,a}' as str
| str |
|---|
| foo |
