このエントリーをはてなブックマークに追加

sqliteのudfをつくってみた

json文字列から特定のデータを検索するsqliteのudfを作ってみた。 jsonをカラムに突っ込んじゃったけど、jsonの特定の値で何かしたくなった場合に使えるかもしれない。 まぁ、需要はあまりないよね。

スクラッチから書く気力は無かったのでvjsonのコードをベースにして作ってみた。

基本的な動作しか確認していないので、エスケープ処理とかUTF-8とかの処理のテストがまだ不十分。 あとコンパイルオプションで変更可能だけど、ネストしたキーの最大長が1024バイト以上あるjson文字列は扱えなかったり。 32回以上ネストしているjson文字列は扱えなかったりする。

使い方

insertするときにjfminでラップするとjson文字列の余分なスペースとかを取り除いてくれる。

挿入の例:
  select load_extension('../sqlite_json_finder.so.0.0.0');  <--- これでライブラリをロードする
  create table data (key integer, value text);"
  insert into data values (1, jfmin('{ "a" : 1, "b" : { "c" : 1 } }'));

selectでjfgetを使うとjson文字列の中から特定の値を取ってくる。

データの検索例:
  select key,jfget(value, 'a') frm data;

ネストの検索も’.’でつなげば可能。

ネストしたデータの検索例:
  select key,jfget(value, 'b.c') frm data;

速度

検索速度に関しては、ネストしてないものなら割と速いと思う。比較対象探し中。 ちなみに、jsonをbsonに変換してblobで突っ込んでbsonを検索するバージョン作ってみたけどbson版のほうが速かった。 さすがbson。ただしblobにすると”select * “ができないという問題がある。あとbsonはjsonよりデータサイズ増えたりする。なのでやめました。 あと、根本的な問題としてindexはれないので、スキーマ切ってnativeなsql叩くよりは全然遅いよ。手元のデータでは3倍強ぐらい遅かった。 まぁ、でも速度をあまり要求されないところで使う分には十分な性能だと思う。

TODO

たくさんあります。
  • ネストの検索に無駄があるので改善する
  • keyに”.”がある場合はエスケープできるようにする
  • エスケープ処理のテスト
  • UTF-8周りのテスト
  • ネスト回数制約をなくす
  • keyの長さ制約をなくす
  • mysqlで利用できるようにする

その他

実は既にjsonをパースするudfのmysql版はあったりする。mysql対応したら比較してみるつもり。
name:
email:
comment: