はじまる

適当な事を適当に書く

Postgres JSON （JSON配列）データの要素にアクセスするための演算子一覧

PostgreSQL 情報処理

はじめに

こんな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