【PostgreSQL】 jsonb型カラムをSQLで検索・集計したい

Postgresでは9.2からjson型が、9.4からjsonをバイナリ解析して保持するjsonb型が使えるようになりました。
普段のアプリケーション開発ではRuby on Railsのstore_accessorを経由してjsonb型を使うことが多いので、そこまでjsonb型特有の文法で困ったことはないのですが、分析や集計をする時にSQLでjsonb型を扱う必要があり、jsonb型の検索に使える演算子や関数を調べたのでまとめてみたいと思います。

特定のキーの値にアクセスする演算子

例えば次のようなテーブルがあったとして

CREATE TABLE sample (
    id SERIAL,
    value JSONB 
);  


INSERT INTO sample
  (value)
VALUES 
    ('{"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}'::jsonb)
;

"a"キーの値にアクセスするには ->>演算子を使います。

-- {"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}  

SELECT value->>'a'
FROM sample;
  
     ?column?
-----------------
1

ただし、->> は値をテキスト型にして返すので、WHEREの条件にする際には適切な型にキャストする必要があります。

-- {"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}  
  
SELECT value->>'a'
FROM sample
WHERE (value->>'a')::INT = 1
;

  
      value
-----------------
1

->>はテキストで値を返すので、"b"キーの値の、さらに"c"キーの要素といったようなネストした要素にアクセスするためには使えません。
ネストした要素にアクセスするときは->演算子を使います。->は指定したキーの値をJSONオブジェクトとして扱える形で返すので、value->'a'->'b'といった具合に繋げて書くことができます。

-- {"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}  
  
SELECT value->'b'->'c'
FROM sample
;  
  
     ?column?
-----------------
[1,2,3]

JSON配列が返ってきました。
JSON配列の中身を取り出す時にも同様に->と->>が利用できます。
配列に使う時は->0のように数値で添字を指定することで、指定した位置の要素を取得できます。

-- {"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}  
  
SELECT value->'b'->'c'->0 --最初の要素
FROM sample
;

     ?column?
-----------------
1

キーとバリューの組み合わせで比較して検索

例えば「"a"キーの値が1でかつ"e"キーの値がtrue」など条件が複数になるとWHERE文が複雑になってきます。

-- {"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}  
  
SELECT value
FROM sample
WHERE (value->'a' )::INT= 1
AND (value->'e')::BOOL = true
;
  
      value
-----------------
{"a":1,"b":{"c":[1,2,3]},"d":null,"e":true,"f":"hello"}

そんな時は@>演算子で、トップレベルでキーと値の組み合わせが一致するカラムを検索できます。

-- {"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}  
  
SELECT value
FROM sample
WHERE value@>'{"a": 1, "e": true}'
;
  
      value
-----------------
{"a":1,"b":{"c":[1,2,3]},"d":null,"e":true,"f":"hello"}

これなら{"a": 1, "e": true}のように手元で一致させたいJSONオブジェクトを作った物をそのまま使って検索できるので楽ですし、読むときもどんなJSONを検索しているのか一目でわかりますね。

ただし、トップレベルの要素しか比較対象にならないので、深い場所にある要素には->と組み合わせて使う必要があります。

-- {"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}  
  
SELECT value
FROM sample
WHERE value->'b'@>'{"c": [1,2,3]}'
;
  
      value
-----------------
{"a":1,"b":{"c":[1,2,3]},"d":null,"e":true,"f":"hello"}

あるキーが存在するかどうかで検索する

jsonb型を使っている以上、やはりあるレコードのカラムには存在し、あるレコードのカラムにはないキーというのもあり得ると思います。

そんなレコードを検索する時は、?演算子を使うとキーが存在するレコードだけに検索対象を絞りこむということが簡単にできます。

-- {"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}  
  
SELECT value
FROM sample
WHERE value?'c'
;  

-- トップレベルに"c"キーがあるレコードがないので結果は0件

これもネストの先にある要素に使うには->と組み合わせます。

-- {"a":  1, "b": {"c": [1, 2, 3]}, "d": null, "e": true, "f": "hello"}  
  
SELECT value
FROM sample
WHERE value->'b'?'c'
;
  
      value
-----------------
{"a":1,"b":{"c":[1,2,3]},"d":null,"e":true,"f":"hello"}

JSONオブジェクトをテーブルのように扱う

例えば同じキーを持ったレコードのみに絞りこめたら、これを集計のためにそのままテーブルのように扱いたいという場面もあると思います。

 INSERT INTO sample
  (value)
VALUES 
    ('{"name": "tarou", "age": 10}'::jsonb),
   ('{"name": "hanako", "age": 33}'::jsonb),
   ('{"name": "tama", "age": 3}'::jsonb)
;

これをnameカラムとageカラムをもつテーブルのように扱いたい場合は、jsonb_to_record関数が使えます。

  
SELECT profiles.name, profiles.age
FROM
    sample,
   jsonb_to_record(sample.value) AS profiles(name text, age int)
;
  
  
  name  |  age 
 ------ + ----- 
 tarou  |  10  
 hanako |  33  
 tama   |   3

ただし、この関数では対応できないもパターンがありました。
例えば次のようにJSON配列にレコードにしたいカラムが入っており、しかも要素数が不定な場合は厄介です。

CREATE TABLE sample (
    id SERIAL,
    value JSONB 
);
  
  
INSERT INTO sample
  (value)
VALUES 
   ('[{"name": "rei", "age": 3}, {"name": "tarou", "age": 5}, {"name": "john", "age": 6} ]'::jsonb),
   ('[{"name": "umeko", "age": 12}, {"name": "budda", "age": 45} ]'::jsonb),
   ('[{"name": "mark", "age": 33} ]'::jsonb);

しかし、こんな混乱した状況でも、jsonb_to_recordsetを使うと一発で解決できます。
この関数は、それぞれのレコードのJSON配列を平坦化した上で、nameカラムとageカラムのあるテーブルのように扱えるように整形できます。

SELECT profiles.name, profiles.age
FROM 
    sample,
   jsonb_to_recordset(value) AS profiles(name text, age int)
;  
  
  name  |  age 
 ------ + -----
  rei   |   3   
  tarou |   5   
  john  |   6   
  umeko |  12  
  budda |  45  
  mark  |  33

いかがでしたか?
私の使った場面では今回紹介した演算子や関数で十分対応できましたが、さらに複雑なユースケースのために、他にもたくさんの演算子や関数が用意されています。
ぜひ公式のドキュメントにも目を通してみてください。
8.14. JSONデータ型

表計算ソフトのイラスト