Hatena::ブログ(Diary)

日々の記録 別館

2014-12-11

jsqueryを使ってみた

まえがき

みなさん、こんにちは。ぬこ@横浜です。

このエントリはPostgreSQL Advent Calendar 2014 - Qiitaの11日目のエントリです。
昨日は s87さんに書いていただきました。

今回、PostgreSQL Advent Calendar 2014 - Qiitaがなかなか埋まってなくて(今日見たらほぼ埋まってましたが)、11日目が空いたままになっていたので、急遽、不肖ぬこ@横浜が書くことにしました。

今日のテーマ

先日のPostgreSQLカンファレンス2014 | 日本PostgreSQLユーザ会で、JSONBについてちょこっと喋らせていただきました(JSONB データ型を 使ってみよう)。
また、7日にはecpgからJSONBを扱うという誰得なエントリを書いてみました。
で、今日のテーマは・・・やっぱりJSONBに関するネタです。ホントは12/20日用にとっておこうかと思ってたけどw

jsqueryとは


jsqueryとは・・・私もまだ読み解いている途中なんですが、JSONBに対して強力なクエリ機能を提供する拡張機能です。
JSONBに対する様々な便利な演算子や、GINインデックス用の演算子などをサポートしています。
今年の9月11日にSRA OSS.さんが主催した、PostgreSQL 9.4 最新情報セミナー 〜 新しいJSONデータ型開発者の Oleg 氏を迎えて 〜が参考になるかと思います。

開発しているのはJSONBの開発チーム。PostgreSQL 9.4には含まれていませんが、もしかするとPostgreSQL 9.5には入るかもしれません。
現状、ソースなどはhttps://github.com/akorotkov/jsqueryから取得できるっぽい。

インストールビルド

まずは、jsqueryモジュールをとりあえず使えるようにしてみましょうか。

ソースの入手

今のところ、開発中なのでRPMパッケージ等はなさそうです。あっても自分は基本、野良ビルド派なので使いませんが。
ということで、ソースを入手してビルドしてみますか。
PostgreSQL 9.4rc1のソースアーカイブを展開したディレクトリに移動します。

ここで、jsqueryのgitリポジトリ(https://github.com/akorotkov/jsquery.git)からソースを入手します。

[nuko@localhost postgresql-9.4beta2]$ git clone https://github.com/akorotkov/jsquery.git
Cloning into 'jsquery'...
remote: Counting objects: 451, done.
remote: Total 451 (delta 0), reused 0 (delta 0)
Receiving objects: 100% (451/451), 249.36 KiB | 154.00 KiB/s, done.
Resolving deltas: 100% (286/286), done.
[nuko@localhost postgresql-9.4beta2]$

ソースのビルドインストール

jsqueryという名前をフォルダが生成されているので、jsqueryフォルダに移動し、USE_PGXS=1オプションを付与してmakeとmake installします。

[nuko@localhost postgresql-9.4beta2]$ cd jsquery/
[nuko@localhost jsquery]$ make USE_PGXS=1
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I./ -I/home/nuko/pgsql-9.4-b2/include/server -I/home/nuko/pgsql-9.4-b2/include/internal -D_GNU_SOURCE   -c -o jsonb_gin_ops.o jsonb_gin_ops.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I./ -I/home/nuko/pgsql-9.4-b2/include/server -I/home/nuko/pgsql-9.4-b2/include/internal -D_GNU_SOURCE   -c -o jsquery_constr.o jsquery_constr.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I./ -I/home/nuko/pgsql-9.4-b2/include/server -I/home/nuko/pgsql-9.4-b2/include/internal -D_GNU_SOURCE   -c -o jsquery_extract.o jsquery_extract.c
/usr/bin/bison -d -o jsquery_gram.c jsquery_gram.y
/usr/bin/flex   -o'jsquery_scan.c' jsquery_scan.l
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I./ -I/home/nuko/pgsql-9.4-b2/include/server -I/home/nuko/pgsql-9.4-b2/include/internal -D_GNU_SOURCE   -c -o jsquery_gram.o jsquery_gram.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I./ -I/home/nuko/pgsql-9.4-b2/include/server -I/home/nuko/pgsql-9.4-b2/include/internal -D_GNU_SOURCE   -c -o jsquery_io.o jsquery_io.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I./ -I/home/nuko/pgsql-9.4-b2/include/server -I/home/nuko/pgsql-9.4-b2/include/internal -D_GNU_SOURCE   -c -o jsquery_op.o jsquery_op.c
jsquery_op.c: 関数 ‘recursiveExecute’ 内:
jsquery_op.c:364:13: 警告: 変数 ‘r’ が設定されましたが使用されていません [-Wunused-but-set-variable]
     int32   r;
             ^
jsquery_op.c:434:13: 警告: 変数 ‘r’ が設定されましたが使用されていません [-Wunused-but-set-variable]
     int32   r;
             ^
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I./ -I/home/nuko/pgsql-9.4-b2/include/server -I/home/nuko/pgsql-9.4-b2/include/internal -D_GNU_SOURCE   -c -o jsquery_support.o jsquery_support.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -shared -o jsquery.so jsonb_gin_ops.o jsquery_constr.o jsquery_extract.o jsquery_gram.o jsquery_io.o jsquery_op.o jsquery_support.o -L/home/nuko/pgsql-9.4-b2/lib -Wl,--as-needed -Wl,-rpath,'/home/nuko/pgsql-9.4-b2/lib',--enable-new-dtags  
[nuko@localhost jsquery]$ make USE_PGXS=1 install
/usr/bin/mkdir -p '/home/nuko/pgsql-9.4-b2/lib'
/usr/bin/mkdir -p '/home/nuko/pgsql-9.4-b2/share/extension'
/usr/bin/mkdir -p '/home/nuko/pgsql-9.4-b2/share/extension'
/usr/bin/install -c -m 755  jsquery.so '/home/nuko/pgsql-9.4-b2/lib/jsquery.so'
/usr/bin/install -c -m 644 jsquery.control '/home/nuko/pgsql-9.4-b2/share/extension/'
/usr/bin/install -c -m 644 jsquery--1.0.sql '/home/nuko/pgsql-9.4-b2/share/extension/'
[nuko@localhost jsquery]$ 

途中で、警告が2つほど出てますが、まあ今回はさらっと無視しますw
生成されるEXTENSION名は jsquery です。これを利用したいデータベースに登録します。

[nuko@localhost jsquery]$ psql jsonb
psql (9.4beta2)
Type "help" for help.

jsonb=# CREATE EXTENSION jsquery ;
CREATE EXTENSION
jsonb=# 

無事に登録できました!

登録されたjsqueryの中身。

登録された中身を見てみましょう。

jsonb=# \dx
                  List of installed extensions
  Name   | Version |   Schema   |          Description           
---------+---------+------------+--------------------------------
 jsquery | 1.0     | public     | data type for jsonb inspection
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

jsonb=# \dx+ jsquery 
                                          Objects in extension "jsquery"
                                                Object Description                                                
------------------------------------------------------------------------------------------------------------------
 function gin_compare_jsonb_path_value(bytea,bytea)
 function gin_compare_jsonb_value_path(bytea,bytea)
 function gin_compare_partial_jsonb_path_value(bytea,bytea,smallint,internal)
 function gin_compare_partial_jsonb_value_path(bytea,bytea,smallint,internal)
 function gin_consistent_jsonb_path_value(internal,smallint,anyarray,integer,internal,internal,internal,internal)
 function gin_consistent_jsonb_value_path(internal,smallint,anyarray,integer,internal,internal,internal,internal)
 function gin_debug_query_path_value(jsquery)
 function gin_debug_query_value_path(jsquery)
 function gin_extract_jsonb_path_value(internal,internal,internal)
 function gin_extract_jsonb_query_path_value(anyarray,internal,smallint,internal,internal,internal,internal)
 function gin_extract_jsonb_query_value_path(anyarray,internal,smallint,internal,internal,internal,internal)
 function gin_extract_jsonb_value_path(internal,internal,internal)
 function gin_triconsistent_jsonb_path_value(internal,smallint,anyarray,integer,internal,internal,internal)
 function gin_triconsistent_jsonb_value_path(internal,smallint,anyarray,integer,internal,internal,internal)
 function json_jsquery_exec(jsonb,jsquery)
 function jsquery_cmp(jsquery,jsquery)
 function jsquery_eq(jsquery,jsquery)
 function jsquery_ge(jsquery,jsquery)
 function jsquery_gt(jsquery,jsquery)
 function jsquery_hash(jsquery)
 function jsquery_in(cstring)
 function jsquery_join_and(jsquery,jsquery)
 function jsquery_join_or(jsquery,jsquery)
 function jsquery_json_exec(jsquery,jsonb)
 function jsquery_le(jsquery,jsquery)
 function jsquery_lt(jsquery,jsquery)
 function jsquery_ne(jsquery,jsquery)
 function jsquery_not(jsquery)
 function jsquery_out(jsquery)
 operator !(NONE,jsquery)
 operator &(jsquery,jsquery)
 operator <(jsquery,jsquery)
 operator <=(jsquery,jsquery)
 operator <>(jsquery,jsquery)
 operator =(jsquery,jsquery)
 operator >(jsquery,jsquery)
 operator >=(jsquery,jsquery)
 operator @@(jsonb,jsquery)
 operator @@(jsquery,jsonb)
 operator class jsonb_path_value_ops for access method gin
 operator class jsonb_value_path_ops for access method gin
 operator class jsquery_ops for access method btree
 operator class jsquery_ops for access method hash
 operator family jsonb_path_value_ops for access method gin
 operator family jsonb_value_path_ops for access method gin
 operator family jsquery_ops for access method btree
 operator family jsquery_ops for access method hash
 operator |(jsquery,jsquery)
 type jsquery
(49 rows)

jsonb=# 

いろんな関数、オペレータが追加されてますねー。GINアクセスメソッドも追加されているので、GINインデックス構築時にはこの新しいオペレータを指定するのかも。
あと、最後にも表示されてますが、jsqueryというのはパッケージ名だけでなく型名としても使われています。
この型名、やっとで意味がわかりました。
jsqueryは"Jsonb Search QUERY "の略なんですね。textsearch用の型がtsqueryなのと同じ考え方(Text Search QUERY)で略された名前なのか。
そういえば全文検索(tsquery)もGIN, JSONBと同じOlegさんたちの開発チームが作ったんだっけ。

でも、jsqueryという名前のググらビリティって微妙・・・。
jqueryの間違いじゃね?とグーグル先生には思われてるみたい。(´・ω・`)

jsqueryを使ってみる。

登録できたので、早速使ってみましょう。

jsqueryはjsob型に対して @@ という演算子とJSONBのクエリ文字列を渡します。

| jsonb @@ jsquery

このクエリ文字列の構文形式も、結構複雑というかバリエーションがあって一度に紹介しきれないのですが、今回はそのうち、代表的なものや「これは便利そう!」というものを紹介したいと思います。

キーの存在有無

9.4までのJSONB演算子には、キーの存在有無の真偽値を返すものがなかったのですが、jsqueryではこれに対応しています。

jsonb=# SELECT '{"id": 3, "name": {"nickname": "nuko", "distribute": ["ksj", "neo4jfdw"]},"age":17}'::jsonb @@ 'name = *';
 ?column? 
----------
 t
(1 row)

jsonb=# SELECT '{"id": 3, "name": {"nickname": "nuko", "distribute": ["ksj", "neo4jfdw"]},"age":17}'::jsonb @@ 'hoge = *';
 ?column? 
----------
 f
(1 row)

jsonb=# 

この例では、nameというキーは存在するのでtrueを、hogeというキーは存在しないのでfalseを返却します。

階層の記述をスキップしたキーへの条件指定

9.4までのJSON演算子にもパス指定の演算子( #>, #>> )はありましたが、最上位の階層のキーから末端までの全てのパスを記述する必要がありました。

jsonb=# SELECT ('{"a1":{"b1":100, "b2":{"c1":50, "c2":200}},"a2":-10}'::jsonb #>> '{a1,b2,c2}') = '200';
 ?column? 
----------
 t
(1 row)

jsqueryでも勿論、フルパス的な記述は可能ですが

jsonb=# SELECT '{"a1":{"b1":100, "b2":{"c1":50, "c2":200}},"a2":-10}'::jsonb @@ 'a1.b2.c2 = 200';
 ?column? 
----------
 t
(1 row)

以下のように "*.キー名" みたいな記述で、末端階層のパスを指定することができます。

jsonb=# SELECT '{"a1":{"b1":100, "b2":{"c1":50, "c2":200}},"a2":-10}'::jsonb @@ '*.c2 = 200';
 ?column? 
----------
 t
(1 row)

jsonb=# SELECT '{"a1":{"b1":100, "b2":{"c1":50, "c2":200}},"a2":-10}'::jsonb @@ '*.c2 = 100';
 ?column? 
----------
 f
(1 row)

jsonb=# 

XMLxpathを知ってる人だと、"//要素名" っぽい感じだと思ってもらえばいいかも。
ただ、条件式としてはつけるけど、値を取得しようとして以下の様な書き方をすると怒られる。(´・ω・`)

jsonb=# SELECT '{"a1":{"b1":100, "b2":{"c1":50, "c2":200}},"a2":-10}'::jsonb @@ '*.c2';
ERROR:  bad jsquery representation at character 73
DETAIL:  syntax error, unexpected $end at end of input
STATEMENT:  SELECT '{"a1":{"b1":100, "b2":{"c1":50, "c2":200}},"a2":-10}'::jsonb @@ '*.c2';
ERROR:  bad jsquery representation
LINE 1: ...:100, "b2":{"c1":50, "c2":200}},"a2":-10}'::jsonb @@ '*.c2';
                                                                ^
DETAIL:  syntax error, unexpected $end at end of input
jsonb=# 

数値をきちんと扱える

JSONB型自体は、内部格納形式として数字はnumericとして格納してますが、9.4までのJSONB演算子(->>, #>>)で取り出したときには文字列になってしまいます。
なので、数字を含むJSONBを扱う場合には、CASTするなどの一工夫が必要でした。

jsonb=# SELECT data FROM test WHERE data @@ 'age >=17';
                                        data                                        
------------------------------------------------------------------------------------
 {"id": 2, "age": 59, "name": {"last": "Lane", "first": "Tom"}}
 {"id": 3, "age": 17, "name": {"first": "nuko"}, "distribute": ["ksj", "neo4jfdw"]}
(2 rows)

jsqueryではこんな感じで書きます。

jsonb=# SELECT data FROM test WHERE data @@ 'age >= 17';
                                        data                                        
------------------------------------------------------------------------------------
 {"id": 2, "age": 59, "name": {"last": "Lane", "first": "Tom"}}
 {"id": 3, "age": 17, "name": {"first": "nuko"}, "distribute": ["ksj", "neo4jfdw"]}
(2 rows)

配列に対する評価


指定した値が、配列に含まれるかどうかを評価する演算子(#)もあります。

jsonb=# SELECT data FROM test WHERE data @@ 'distribute.# = ksj';
                                        data                                        
------------------------------------------------------------------------------------
 {"id": 3, "age": 17, "name": {"first": "nuko"}, "distribute": ["ksj", "neo4jfdw"]}
(1 row)

配列同士を比較してオーバラップしているか判定する演算子(&&)や、

jsonb=# SELECT '{"a":[1, 10, 100, 100]}'::jsonb @@ 'a && [20,100]';
 ?column? 
----------
 t
(1 row)

jsonb=# SELECT '{"a":[1, 10, 100, 100]}'::jsonb @@ 'a && [2,200]';
 ?column? 
----------
 f
(1 row)

jsonb=# 

配列が包含されているか判定する演算子(@>)もあります。

jsonb=# SELECT '{"a":[1, 10, 100, 100]}'::jsonb @@ 'a @> [10,100]';
 ?column? 
----------
 t
(1 row)

jsonb=# SELECT '{"a":[1, 10, 100, 100]}'::jsonb @@ 'a @> [10,200]';
 ?column? 
----------
 f
(1 row)

jsonb=# 


全部の演算子について紹介したわけではないですが、ここで紹介したものだけでも、JSONBを使う場合には結構便利になりそうなものが多いですね。

まだ不安定な部分も・・・

jsquery、なかなか楽しい機能も入っていてとっても期待できますが、現状の版だとちょっと間違ったjsqueryクエリを記述すると、わりと簡単に落ちます(´・ω・`)

jsonb=# SELECT data @@ '//first' FROM jsonb_t;
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

無論、このへんは今後、改善されていくんじゃないかとは思いますが・・・。




明日は・・・osaponさんですね。よろしくお願いします!

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/nuko_yokohama/20141211/1418270641
リンク元
Connection: close