pg-jsonpathを書いた

Programming PostgreSQL

はじめに

この記事は PostgreSQL Advent Calendar 2012 の21日目です。

JSON型の機能

PostgreSQLには9.2からJSON型が導入されました。JSON型が提供するのは主に次の2つです。

非構造化データ型

ところでPostgreSQLには以前から非構造化データ型としてXML型があります。

XMLにはよく知られている通りXPathという内部の要素を指定する構文が標準化されており、libxmlなどのデファクトなライブラリを通して提供されています。

PostgreSQLでもlibxmlを利用したXPathでのアクセスが提供されています。

新たに導入されたJSON型にも同様に内部の値にアクセスを求める声はあるようで、wiki上で議論があります。（ JSON API Brainstorm - PostgreSQL wiki ）

ただ、コアでは提供されていないため、俺々ライブラリが色々作られています。（”postgresql json query”あたりでググると色々でてきます）

乱立した背景にはJSONにはXMLにおけるXPathのような標準化されたアクセス方法がないことがあります。

そしてJSONPathへ

XPathのように標準化されたわけではないですが、JSONにも仕様を定められたアクセス方法が存在します。それがJSONPathです。

JSONPath - XPath for JSON

デファクトと言えるほどには浸透していませんが、

  • 仕様が文書として定められている
  • 公式で実装が提供されている

という点で俺々で定義したライブラリよりは遥かにましと思われるので、PostgreSQLで動くようにしました。

JSONPathはjavascriptのライブラリが公式で提供されているのでplv8の上で動かしています。

使い方

ソースコード

choplin/pg-jsonpath · GitHub

インストール

plv8 が必要ですので先にインストールしておいて下さい。

EXTENSIONには対応していますが、pgxnには入れていないのでダウンロードしてmakeして下さい。

git clone git://github.com/choplin/pg-jsonpath.git
cd pg-jsonpath
make && make install
psql -e "CREATE EXTENSION jsonpath";

呼び出し

型は以下の通りです。

jsonPath(obj json, expr text) RETURNS json[]

exprはJSONPathの式になるので、公式ドキュメントを参照して下さい。

SELECT jsonPath('{"x": {"a":1, "b":2}}'::json, '$.x.[a,b]');
 jsonpath
----------
 {1,2}
(1 row)

queryとかindexとかjoin

関数を通して内部の値にアクセスすることができるので、後はPostgreSQLの既存の機能と組み合わせれば大体できます。

CREATE TABLE tweets (
    id int,
    val json
);

INSERT INTO tweets VALUES
    (1, '{"user": "a", "content": "hi"}'),
    (2, '{"user": "a", "content": "ho"}'),
    (3, '{"user": "b", "content": "he"}'),
    (4, '{"user": "c", "content": "ha"}'),
    (5, '{"user": "c", "content": "hu"}'),
    (6, '{"user": "c", "content": "hihi"}');

SELECT

SELECT id, (jsonPath(val, '$.user'))[1] FROM tweets;
 id | jsonpath
----+----------
  1 | "a"
  2 | "a"
  3 | "b"
  4 | "c"
  5 | "c"
  6 | "c"
(6 rows)

WHERE

SELECT * FROM tweets WHERE (jsonPath(val, '$.user'))[1]::text = '"a"'
 id | val
----+--------------------------------
  1 | {"user": "a", "content": "hi"}
  2 | {"user": "a", "content": "ho"}
(2 rows)

Index

CREATE INDEX idx_tweets_user ON tweets ( ((jsonPath(val, '$.user'))[1]::text) );
SET enable_seqscan TO off;

QUERY PLAN
-------------------------------------------------------------------------------
(2 rows)

Join

CREATE INDEX idx_tweets_user ON tweets ( ((jsonPath(val, '$.user'))[1]::text) );
SET enable_seqscan TO off;

EXPLAIN SELECT * FROM tweets WHERE (jsonPath(val, '$.user'))[1]::text = '"a"'
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Index Scan using idx_tweets_user on tweets  (cost=0.26..8.52 rows=1 width=36)
   Index Cond: (((jsonpath(val, '$.user'::text))[1])::text = '"a"'::text)
(2 rows)

TODO

文字列だけの値の場合、JSONとしてvalidであるためには “some string” のようにダブルクォーテーションで囲まれている必要があります。

jsonPathはjson[]を返すため、結果が文字列だけの場合でもjsonとしてvalidである必要があるので “result” のようになってしまいます。

その結果、上記の例のように、textにcastした後にも “” が複数回出現してめんどくさいことになります。

いい感じに収める方法があれば教えてください。

そもそもコアで対応されれば拡張は必要なくなるんですが今どんな状況なんでしょうか？

まとめ

pg-jsonpathを使えばJSON型の内部の値にアクセスできるようになります。

queryもindexもjoinも使えるので、スキーマレスという理由だけでドキュメント指向DBを検討している方はPostgreSQLも選択肢に入れてみてもいいかもしれません。

Comments