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日用にとっておこうかと思ってたけどｗ
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つほど出てますが、まあ今回はさらっと無視しますｗ
生成される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=# XMLやxpathを知ってる人だと、"//要素名" っぽい感じだと思ってもらえばいいかも。
ただ、条件式としてはつけるけど、値を取得しようとして以下の様な書き方をすると怒られる。(´・ω・｀)
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さんですね。よろしくお願いします！
