json
及び jsonb
のデータ型を取り扱うにあたって、使用できるオペレーターと関数は、公式サイト(英語)(→日本語版)ある通りである。
演算子についてちょっと書いてみる。
「例」の見方
演算子や関数の説明表にある「例」の
'{"a": 0, "b": 1}'::jsonb
↑こういうのは {"a": 0, "b": 1}
の値を持つjsonb型
のカラムですよ、ということを表す。
実際SQLを書くときは、tags
カラムがjsonb型
であるとき、
select * from tags ? 'b';
のように書く。
各演算子
各演算子の説明は公式ドキュメントが十分に詳しいので割愛。
PHPで使う際の注意点
これらの演算子は2015年9月15日現在
はPHPから使えない
PDOのドライバーが対応していないのだ。
じゃあどうするかというと、演算子をJSON/JSONB関数に置き換えればよい。
PostgreSQLの演算子は必ず対応した関数
が用意されている。
演算子に対応する関数は以下のSQLで確認できる。
postgres=> SELECT oprname, oprcode FROM pg_operator WHERE oprname = '?';
oprname | oprcode
---------+--------------
? | jsonb_exists
(1 row)
演算子 ?
は jsonb_exists関数
が対応していることがわかる。
'{"a": 0, "b", 1}'::jsonb ? 'b'
は
jsonb_exist('{"a": 0, "b", 1}'::jsonb, 'b')
と書き直せる。
関数で代替する際も、$pdo->prepare()
でステートメントを用意することができない(正常にエスケープ処理が行われない)ので、生のSQLを発行する必要がある。その際はもちろん、エスケープ処理には細心の注意を払うことが必要。
jsonb_exits (?演算子) の罠
jsonb型で数値配列を格納していて、その中のある数値を検索したい、という場合に、
select * from posts where '[0,1,2,3]::jsonb' ? 2 ;
これができない。
jsonb_exitsの第2引数は文字列型
じゃないとダメ。
かといって、
select * from posts where '[0,1,2,3]::jsonb' ? '2' ;
こうしてシングルクォーテーションで括っても結果は0件になる。
方法が他にあるのかわからないけど、私は諦めた。
代替手段
代わりに jsonb_contains ( @> 演算子)
を使う。
jsonbカラムのうち、検索値(jsonb)が含まれるかどうかを調べるものなので、
入力した数値をjson配列に書き直し、jsonbにキャストしてあげれば、比較ができるようになる。
例) 0が検索値
select * from "users" where jsonb_contains('[0,1,2,3]'::jsonb, '[0]'::jsonb);
jsonb型の部分更新
できない。。9.4
でできるのは検索のみ。
でも9.5
で部分更新が追加されるようていだそうだ。
楽しみ。