今回の場合、言葉的には半構造化データのほうが適切なのかも。
postgreSQLが有力候補かなあ。
ひとまずはjsqueryを使いたい。
herokuでできるのか。
できること
- タグ検索（GIN インデックス）
- 標準的な検索（jsquery利用）
db1=# SELECT * FROM t_info WHERE j @@ 'f1.# IN (1,2,3)'
⇒ トップ直下の f1 キー値が配列であって、1、2、3 いずれかの値を含む
db1=# SELECT * FROM t_info WHERE j @@ '%.f2.f3 = *'
⇒ トップ直下の任意キー値から "f2" → "f3" という階層を持つ
db1=# SELECT * FROM t_info WHERE j @@ '*.f1 < 100'
⇒ いずれかの階層にある f1 キーの値が 100 以下である
- hstore型もあるのね
heroku
jsqueryは使えるextensionに含まれていなさそうだなあ。
でも、きっとモジュールじゃなくて標準装備されていくんだろうなと楽観。
posgresql json
シングルクォーテーションとダブルクォーテーションの違いに気をつける。
「@」と「-」も。
json_column={"a":"b"}
# マッチする
select jsonb from sandbox where jsonb -> 'a' ? 'b';
> {"a":"b"}
# 表示される
select jsonb->'a' from sandbox;
> "b"
# マッチする
select jsonb from sandbox where jsonb @> '{"a":"b"}';
> {"a":"b"}
json_column={
"a":"b",
"tags":["t1","t2"]
}
# マッチする
select jsonb from sandbox where jsonb -> 'tags' ? 't1';
{"tags": {"t1": "val", "t2": "val", "t3": "val"}, "time": 1462441837}
# マッチする
select jsonb from sandbox where jsonb -> 'tags' ->'t1' ? 'val';
select jsonb from sandbox where jsonb -> 'tags' @>'{"t2":"val"}';
select jsonb from sandbox where jsonb @>'{"tags":{"t2":"val"}}';
select jsonb from sandbox where jsonb #> '{"tags","t2"}' ? 'val';
{"tags": ["t1", "t2", "t3"], "time": 1462441837}
# マッチする
select jsonb from sandbox where jsonb @>'{"tags":["t2"]}';
{"tags": ["t1", "t2", "t3"], "time": 1462441837}
{"tags": {"t1": "val", "t2": "val", "t3": "val"}, "time": 1462441837}
# マッチする
select jsonb from sandbox where jsonb -> 'tags' ?& '{"t1","t2","t3"}';
{"tags": ["t1", "t2", "t3"], "time": 1462441837}
{"tags": [{"t1": "val"}, {"t2": "val"}, {"t3": "val"}], "time": 1462441837}
# マッチする
select jsonb from sandbox where jsonb #> '{"tags",0}' ? 't1';
{"tags": [{"t1": "val"}, {"t2": "val"}, {"t3": "val"}], "time": 1462441837}
# マッチする
select jsonb from sandbox where jsonb #> '{"tags",0,"t1"}' ? 'val';
select jsonb from sandbox where jsonb #>> '{"tags",0,"t1"}' = 'val';
型変換も含め、下記が詳しいなあ。
http://qiita.com/kumazo@github/items/483f47360f8b61a9fbb9
公式のLISTがわかりやすい。
http://www.postgresql.org/docs/9.5/static/functions-json.html
memo
- ワイルドカード等はなく、JSONPath的な柔軟性は望めない。
-
http://oss.sios.com/yorozu-blog/postgresql95-jsonb?fb_action_ids=901714373253834&fb_action_types=og.likes
- ■ jsonb_pretty 関数
- オペレータでは子要素の更新ができませんが、jsonb_replace 関数では可能
pdo driver jsonb
PHPから使うときにはドライバーの対応が必要みたい。重要。
これらの演算子は2015年9月15日現在はPHPから使えない
PDOのドライバーが対応していないのだ。
じゃあどうするかというと、演算子をJSON/JSONB関数に置き換えればよい。
PostgreSQLの演算子は必ず対応した関数が用意されている。
演算子に対応する関数は以下のSQLで確認できる。
postgres=# SELECT oprname, oprcode FROM pg_operator WHERE oprcode::text LIKE 'json%';
oprname | oprcode
---------+--------------------------
-> | json_object_field
->> | json_object_field_text
-> | json_array_element
->> | json_array_element_text
#> | json_extract_path
#>> | json_extract_path_text
-> | jsonb_object_field
->> | jsonb_object_field_text
-> | jsonb_array_element
->> | jsonb_array_element_text
#> | jsonb_extract_path
#>> | jsonb_extract_path_text
= | jsonb_eq
<> | jsonb_ne
< | jsonb_lt
> | jsonb_gt
<= | jsonb_le
>= | jsonb_ge
@> | jsonb_contains
#あるかどうか
? | jsonb_exists
#どれかあるかどうか
?| | jsonb_exists_any
#全部あるかどうか
?& | jsonb_exists_all
<@ | jsonb_contained
|| | jsonb_concat
#- | jsonb_delete_path
(25 rows)
jsonにする必要性について知り合いに問われて一考
下記のような方針でどうかしら。キーバリュー的な考え方は別途。
- 引っ掛かりがあったのは
- 「スキーマやバリデーションとセキュリティ」
- 「デコードデンコードの負荷」
- 「ツリーオブジェクト（的なもの）との優劣」
- 構造についてのデータだけ切り分ける
- 許可するのは数字と数種類の記号（[{}"'a-zA-Z]程度）に絞る
- サーバーでのバリデーションは文字列として行う
- エンコードやデコードは極力行わない
- 許可するのは数字と数種類の記号（[{}"'a-zA-Z]程度）に絞る
- ツリーオブジェクト（的なもの）との優劣
- 構造についてのデータをレコードとして保持する形式にする必要については検討してもいいけど難しそう
- 構造についての情報はあまり弄くらなくていい表面的なレイアウトのようなものにするなら優位
- 構造の関連性による作用が発生しない、するにしても代替策が可能なもの
- 作用があれば別データでレコード保持してもいいかな、、うーん、この辺は面倒
- サーバーサイドで頻繁にはデコードしない
- サーバーサイドで頻繁には追加や変更しない
- 構造の関連性による作用が発生しない、するにしても代替策が可能なもの
- 結局、どこに負荷をかけるかのバランスかな
- ツリーオブジェクト（的なもの）に属性をつけることでクリアできそうで確立されてる
- ツリーオブジェクト（的なもの）の操作がjsonbの関数で提供されればそちらのほうがいいかも
- もしそうでも操作の拡張については若干気になる
- あと、目的の階層が特定できている検索、タグ機能などには優位
データを行列として表現してみた
- 特定の、、半構造化データを行列にしたら若干しっくりいった
- 場合によっては、上手に行列を扱えればそれでいい気がした
- それは、RDBSの列が不特定多数（json）でいいのではないか
- 行はRDBSで、クエリーやイテレーターで処理する
- 列はjsonbとか何かしらで不特定多数に対応させる
参考
jsonb 検索 postgresql - Google 検索
https://goo.gl/DKN4s7
PostgreSQLのJSONB型を利用してタグ検索を行う | yohgaki's blog
http://goo.gl/l2XoT9
PostgreSQL 9.4 の JSON サポートについて - Qiita
http://goo.gl/6cbXXH
jsquery heroku - Google 検索
https://goo.gl/JMBi67
postgrespro/jsquery: JsQuery – json query language with GIN indexing support
https://goo.gl/VubguB
PostgreSQLガイダンス（1）：PostgreSQL 9.4 GINインデックスの評価、JSONBデータ型の使い方 (2/2) - ＠IT
http://goo.gl/UwKUKH
日々の記録 別館
http://goo.gl/DCOvvB
jsqueryを使ってみた - 日々の記録 別館
http://goo.gl/13xY1F
RDB Postgresql 9.4 beta 2 で JSONB を試す - @//メモ
http://goo.gl/trrHjG
PostgreSQL JSON/JSONBの文書操作とMongoDBの文書操作 - 日々の記録 別館
http://goo.gl/mPjzrM