ドキュメントを検索する最良の方法
最初の部分では、次のようなテーブルを作成しました。
create table my_docs( id serial primary key, body jsonb not null, search tsvector, created_at timestamptz not null default now(), updated_at timestamptz not null default now() )
データの保存方法を制御できるため、独自の関数を作成して、このデータをさまざまな楽しい方法で抽出できます。 最も難しい部分は終わりました（保存、更新など）-今から楽しみましょう。
IDによるドキュメントの取得
save_document関数のおかげで、各ドキュメントには完全に関連付けられたidフィールドがあります。 これは依然としてPostgresであるため、各フィールドには主キーが必要であり、それをドキュメント自体に入れます。 整数を作成しましたが、必要に応じてbigint Twitterスノーフレークを使用することもできます。 ここでは、 シリアルタイプを使用します。
このための機能はかなり簡単です。
create function find_document(tbl varchar, id int, out jsonb) as $$ //find by the id of the row var result = plv8.execute("select * from " + tbl + " where id=$1;",id); return result[0] ? result[0].body : null; $$ language plv8; select * from find_document('customers',20);
これは、可能な限り最も単純な関数です。検出する必要があるテーブル名とIDを取得し、すべてのクエリ（最愛の人）の中で最も高速なクエリを実行します 。 スピード： 気に入っています。
では、一括クエリ用に作成してみましょう。 これを行うには、いくつかの基準を紹介し、最初の試合について講義します。 これは、結果を並べ替える場合にのみ正常に機能するため、 ORDER BYを追加し、そのデフォルトパラメータとしてIDを指定します。
create function find_document( tbl varchar, criteria varchar, orderby varchar default 'id' ) returns jsonb as $$ var valid = JSON.parse(criteria); //this will throw if it invalid var results = plv8.execute("select body from " + tbl + " where body @> $1 order by body ->> '" + orderby + "' limit 1;",criteria); return results[0] ? results[0].body : null $$ language plv8; select * from find_document('customers','{"last": "Conery"}', 'first');
さらに、使用するドライバーによっては奇妙な動作が予想されます。 最初に注意したいのは、Postgresで許可されているため、 find_document関数をオーバーロードすることです。 これは、idで検索する元の関数とこの関数の唯一の違いが引数のリストであることを意味します。
Npgsqlドライバーの場合、これは問題ではありません。 node_pgドライバーの場合、これは別の問題です。 orderbyパラメーターにデフォルト値を設定しているため、実行する関数を選択するときに混乱が生じます。 私が知る限り、node_pgドライバーは関数の引数のタイプを気にせず、その数だけを気にします 。 したがって、上記の「IDで検索」関数を実行しようとすると、2番目の関数が起動します。
繰り返しますが、Npgsql（.NETドライバー）にはこのような問題はありません。 そのため、問題がある場合は、いずれかの関数の名前を変更するか、パラメーターのデフォルト値を削除してください。
もう1つ注目したいのは、 criteriaパラメータをvarcharに設定することです。これは技術的には正しくありませんが、APIが少し良くなりました。 jsonbとして設定した場合、リクエストは次のように実行する必要があります。
select * from find_document('customers','{"last": "Conery"}'::jsonb, 'first');
主にコードからAPIを使用するため、違いはわずかです（これについては次の投稿で説明します）。
フィルタリング
いくつかの返されたドキュメントに対してのみ同じことを繰り返しましょう：
create function filter_documents( tbl varchar, criteria varchar, orderby varchar default 'id' ) returns setof jsonb as $$ var valid = JSON.parse(criteria);//this will throw if it invalid var results = plv8.execute("select body from " + tbl + " where body @> $1 order by body ->> '" + orderby + "'",criteria); var out = []; for(var i = 0;i < results.length; i++){ out.push(results[i].body); } return out; $$ language plv8; select * from find_document('customer_docs','{"last": "Conery"}');
これはすでに興味深いものです。 結果はsetof jsonbになります。つまり 、いくつかのjsonb行を返さなければなりません 。 PLV8を使用してこれを行う方法は完全には明確ではありません。また、私よりも良い方法があるかもしれませんが、それはうまくいくと確信しています。
結果（ドキュメント指向のテーブルからの行）を取得したらすぐに、 jsonbフィールドの本体を取得して配列に挿入するループを実行する必要があります。このループは後で返します。
bodyフィールドがjsonbであり、これがテキストであるため、これはすべて機能します。 これはjavascriptオブジェクトではありません。もしそうだとすると、エラー（古い[オブジェクトオブジェクト]解析の愚かさ）が発生するからです。
SQLインジェクション
ここでは、 orderbyパラメーターが直接内側に連結されていることに気付くでしょう。 ユーザーにデータベースへのSQLの書き込みを許可する場合、はい、これは問題です。 しかし、希望を持って、ドライバーからこの機能を実行します。ドライバーは、おおよそ次の形式でリクエストをパラメーター化します。
db.filter("customers", { last : "Conery", orderBy : "a';DROP TABLE test; SELECT * FROM users WHERE 't' = 't" }, function(err, res){ console.log(err); console.log(res); });
...動作しません。 どうして？ 理想的には、あなたはこの種のことをしているからです：
select * from filter_documents($1, $2, $3);
そうでなければ、あなたはあなたが値するものを得ます:)。
全文検索
必要に応じて、ドキュメント内の全文検索を終了しましょう。 これは私のお気に入りの部分です。
create function search_documents(tbl varchar, query varchar) returns setof jsonb as $$ var sql = "select body, ts_rank_cd(search,to_tsquery($1)) as rank from " + tbl + " where search @@ to_tsquery($1) " + " order by rank desc;" var results = plv8.execute(sql,query); var out = []; for(var i = 0; i < results.length; i++){ out.push(results[i].body); } return out; $$ language plv8; select * from search_documents('customers', 'jolene');
Postgresの全文検索でインデックスがどのように機能するかを知っていれば、それはすべて非常に簡単です。 ここでは、 検索フィールド（速度のためにGINインデックスによってインデックス付けされています）を操作します。これは保存するたびに更新されます。 この要求は非常に速く、非常に簡単に処理できます。
インデックスをより柔軟にする
基準を受け入れる2つの関数（検索とフィルタリング）で、「content」演算子を使用します。 これは小さな@>文字です。
このステートメントはjsonbに固有のものであり、 bodyフィールドでGINインデックスを使用できます。 このインデックスは次のとおりです。
create index idx_customers on customers using GIN(body jsonb_path_ops);
ここで、 Jsonb_path_opsは特別な奇跡を与えます。 これは、インデクサーにjsonbの 「コンテンツ」 操作を最適化するように指示します （実際、このjsonbピースは別のjsonbピースに存在します）。 これは、インデックスがより高速で小さいことを意味します。
これで、書き込み/読み取りに関して、PostgreSQLがMongoDBなどをバイパスする方法に関する多くのソースと記事を参照できました。 しかし、これは誤解を招きます。
読み書き速度
1つのMongoDBサーバーに対して1つのPostgreSQLサーバーを使用する場合、MongoDBは非常に暗く見え、Postgresはほぼすべてのメトリックで喫煙します。 これは、Postgresがこのように設計されたためです-「スケーラブルな」データベース。
MongoDBを最適化し、サーバーを追加して負荷を分散する場合、インジケーターは互いに近くなりますが、さらに、 予期しない方法で動作する水平システムに対処する必要があります 。 もちろん、これは非常に議論の余地がありますが、次の点に注意する必要があります。
- Postgresのインデックス作成が遅くなります。 そのため、これにより録音パフォーマンスが損なわれる場合、インデックスのGINを指定するときに、 （（body-> my_field ）を指定することにより）インデックスを作成するものにのみインデックスを設定する必要があります。
- 非常に頻繁にアクセスする場合（たとえば、電子メールアドレス）、別の列にコピーして一意にするだけで、コードまたは別の関数で同期を処理できます。
次の記事では、このようなものをコードから呼び出す方法について詳しく説明します！