Kouhei Sutou
null+****@clear*****
Sat Oct 10 23:29:04 JST 2015
Kouhei Sutou 2015-10-10 23:29:04 +0900 (Sat, 10 Oct 2015) New Revision: 7b1e05294fdc45815351e09bb5eebd8db405d02d https://github.com/pgroonga/pgroonga.github.io/commit/7b1e05294fdc45815351e09bb5eebd8db405d02d Message: Translate Added files: reference/jsonb.md reference/operators/jsonb-contain.md reference/operators/jsonb-query.md Modified files: overview/index.md reference/index.md reference/operators/query.md tutorial/index.md Modified: overview/index.md (+1 -1) =================================================================== --- overview/index.md 2015-10-10 21:53:06 +0900 (6853c20) +++ overview/index.md 2015-10-10 23:29:04 +0900 (c2dd22c) @@ -11,7 +11,7 @@ Groonga is an embeddable super fast full text search engine. It can be embedded As default, PostgreSQL isn't capable for CJK full text search. But you can use super fast CJK full text search by installing PGroonga. -And more, PGroonga supports full text search against all texts in JSON. It's an unique feature. Built-in PostgreSQL features and [JsQuery](https://github.com/postgrespro/jsquery) don't support it. +And more, PGroonga supports full text search against all text values in JSON. It's an unique feature. Built-in PostgreSQL features and [JsQuery](https://github.com/postgrespro/jsquery) don't support it. ## Related extensions Modified: reference/index.md (+9 -6) =================================================================== --- reference/index.md 2015-10-10 21:53:06 +0900 (c934273) +++ reference/index.md 2015-10-10 23:29:04 +0900 (2e2c7ed) @@ -11,16 +11,19 @@ This document describes about all features. [Tutorial](../tutorial/) focuses on * [CREATE INDEX USING pgroonga](create-index-using-pgroonga.html) * [PGroonga versus GiST and GIN](pgroonga-versus-gist-and-gin.html) + * [`jsonb` support](jsonb.html) ## Operators - * [LIKE operator](operators/like.html) - * [%% operator](operators/query.html) - * [@@ operator](operators/match.html) + * [`LIKE` operator](operators/like.html) + * [`%%` operator](operators/match.html) + * [`@@` operator](operators/query.html) for non `jsonb` types + * [`@@` operator](operators/jsonb-query.html) for `jsonb` type + * [`@>` operator](operators/jsonb-contain.html) ## Functions - * [pgroonga.score function](functions/pgroonga-score.html) - * [pgroonga.command function](functions/pgroonga-command.html) - * [pgroonga.table_name function](functions/pgroonga-table-name.html) + * [`pgroonga.score` function](functions/pgroonga-score.html) + * [`pgroonga.command` function](functions/pgroonga-command.html) + * [`pgroonga.table_name` function](functions/pgroonga-table-name.html) Added: reference/jsonb.md (+44 -0) 100644 =================================================================== --- /dev/null +++ reference/jsonb.md 2015-10-10 23:29:04 +0900 (b7a3178) @@ -0,0 +1,44 @@ +--- +title: jsonb support +layout: en +--- + +# `jsonb` support + +PGroonga also supports `jsonb` type. You can search JSON data by one or more keys and/or one or more values with PGroonga. + +You can also search JSON data by full text search against all text values in JSON. It's an unique feature of PGroonga. Built-in PostgreSQL features and [JsQuery](https://github.com/postgrespro/jsquery) don't support it. + +Think about the following JSON: + +```json +{ + "message": "Server is started.", + "host": "www.example.com", + "tags": [ + "web", + ] +} +``` + +You can find the JSON by full text search with `search`, `example` or `web` because all text values are full text search target. + +## Operators + +PGroonga provides the following two operators for searching against `jsonb`: + + * [`@>` operator](operators/jsonb-contain.html) + * [`@@` operator](operators/jsonb-query.html) + +`@>` operator is simpler than `@@` operator but you can't use complex condition such as range search. + +`@@` operator is more complex than `@>` operator but you can use complex condition. You can also use full text search against all text values in JSON. + +## Comparison with GIN + +PostgreSQL provides built-in fast `jsonb` search feature by GIN. + +Here are differences between PGroonga and GIN: + + * Index creation time: No difference + * Search time: PGroonga is a bit faster Added: reference/operators/jsonb-contain.md (+14 -0) 100644 =================================================================== --- /dev/null +++ reference/operators/jsonb-contain.md 2015-10-10 23:29:04 +0900 (5f9ece5) @@ -0,0 +1,14 @@ +--- +title: "@> operator" +layout: en +--- + +# `@>` operator + +PGroonga supports fast index search by `@>` operator. + +[`@>` is a built-in PostgreSQL operator](http://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE). `@>` returns true when the right hand side `jsonb` is a sub set of left hand side `jsonb`. + +## See also + + * [`jsonb` support](../jsonb.html) Added: reference/operators/jsonb-query.md (+12 -0) 100644 =================================================================== --- /dev/null +++ reference/operators/jsonb-query.md 2015-10-10 23:29:04 +0900 (8d6203b) @@ -0,0 +1,12 @@ +--- +title: "@@ operator for jsonb type" +layout: en +--- + +# `@@` operator for `jsonb` type + +`@@` operator is a PGroonga original operator. You can use complex condition that can't be written by `@>` operator such as range search. If you know [JsQuery](https://github.com/postgrespro/jsquery), you can understand like "PGroonga provides `jsonb` related search features that are similar to JsQuery with different syntax". + +## See also + + * [`jsonb` support](../jsonb.html) Modified: reference/operators/query.md (+2 -2) =================================================================== --- reference/operators/query.md 2015-10-10 21:53:06 +0900 (631090b) +++ reference/operators/query.md 2015-10-10 23:29:04 +0900 (eb3dee5) @@ -1,9 +1,9 @@ --- -title: "@@ operator" +title: "@@ operator for non jsonb types" layout: en --- -# `@@` operator +# `@@` operator for non `jsonb` types You can do full text search with multiple keywords by `@@` operator like `KEYWORD1 KEYWORD2`. You can also do OR search by `KEYWORD1 OR KEYWORD2`: Modified: tutorial/index.md (+56 -73) =================================================================== --- tutorial/index.md 2015-10-10 21:53:06 +0900 (1768198) +++ tutorial/index.md 2015-10-10 23:29:04 +0900 (f8fe843) @@ -431,32 +431,11 @@ SELECT * FROM products WHERE tags %% 'PostgreSQL'; ## How to use PGroonga for JSON -TODO: Split details to reference/ +PGroonga also supports `jsonb` type. You can search JSON data by one or more keys and/or one or more values with PGroonga. -PGroongaは`jsonb`型のデータもサポートしています。PGroongaのインデック -スを作成することにより高速に検索できます。 +You can also search JSON data by full text search against all text values in JSON. It's an unique feature of PGroonga. Built-in PostgreSQL features and [JsQuery](https://github.com/postgrespro/jsquery) don't support it. -PGroongaは`jsonb`の検索のために次の2つの演算子を提供しています。 - - * `@>`演算子 - * `@@`演算子 - -[`@>`演算子はPostgreSQLが標準で提供している演算子](http://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE) -です。右辺が左辺のサブセットなら真になります。 - -`@>`演算子はGINでも高速化できる演算子です。インデックス作成時間は -PGroongaとGINでそれほど変わりませんが、検索時間はPGroongaの方が少し速 -いです。 - -`@@`演算子はPGroonga独自の演算子です。GINではインデックスを使えない複 -雑な検索条件も記述できます。もし、 -[JsQuery](https://github.com/postgrespro/jsquery)を知っているなら -構文が違うJsQueryのようなものと考えてください。 - -JsQueryができる検索とPGroongaができる検索はほぼ同じですが、PGroongaだ -けができる特徴的な検索は全文字列値に対しての全文検索です。 - -たとえば、次のJSONがあるとします。 +Think about the following JSON: ```json { @@ -468,12 +447,22 @@ JsQueryができる検索とPGroongaができる検索はほぼ同じですが } ``` -すべての文字列値に対して全文検索ができるので、「`server`」でも -「`example`」でも「`web`」でもヒットします。 +You can find the JSON by full text search with `search`, `example` or `web` because all text values are full text search target. + +PGroonga provides the following two operators for searching against `jsonb`: + + * `@>` operator + * `@@` operator -#### サンプル用テーブル定義とサンプルデータ +[`@>` operator is a built-in PostgreSQL operator](http://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE). `@>` returns true when the right hand side `jsonb` is a sub set of left hand side `jsonb`. -例を示すために使うサンプル用のテーブル定義とサンプルデータを次に示します。 +You can execute `@>` faster by PGroonga. + +`@@` operator is a PGroonga original operator. You can use complex condition that can't be written by `@>` operator such as range search. If you know [JsQuery](https://github.com/postgrespro/jsquery), you can understand like "PGroonga provides `jsonb` related search features that are similar to JsQuery with different syntax". + +### Sample schema and data + +Here are sample schema and data for examples: ```sql CREATE TABLE logs ( @@ -512,20 +501,19 @@ INSERT INTO logs }'); ``` -少ないデータでもインデックスを使うようにシーケンシャルスキャンを無効に -します。 +Disable sequential scan: ```sql SET enable_seqscan = off; ``` -#### `@>`演算子 +{: #jsonb-contain} -`@>`演算子は`jsonb`で条件を指定します。カラムの値が条件として指定した -`jsonb`を含んでいればマッチします。 +### `@>` operator -マッチする例です。（結果を見やすくするためにPostgreSQL 9.5から使える -`jsonb_pretty()`関数を使っています。） +`@>` operator specify search condition by `jsonb` value. If search target `jsonb` value contains the condition `jsonb` value, record that have the search target `jsonb` is matched. + +Here is an example for match case. (It uses [`jsonb_pretty()` function](http://www.postgresql.org/docs/devel/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE) provided since PostgreSQL 9.5 for readability.) ```sql SELECT jsonb_pretty(record) FROM logs WHERE record @> '{"host": "www.example.com"}'::jsonb; @@ -551,13 +539,11 @@ SELECT jsonb_pretty(record) FROM logs WHERE record @> '{"host": "www.example.com -- (2 rows) ``` -マッチしない例です。 +Here is an example for not match case. + +If you use an array in the condition `jsonb` value, all elements must be included in the search target `jsonb` value. Position of element isn't cared. If there are one or more elements that are included in the condition `jsonb` value but aren't included in the search target `jsonb` value, record that have the search target `jsonb` value isn't matched. -条件の`jsonb`で配列を指定した場合、すべての要素が含まれていればマッチ -します。（配列の要素の順序は関係ありません。）しかし、1つでも含まれて -いない要素があればマッチしません。次の`tags`に`"mail"`を含むレコードも -`"web"`を含むレコードもありますが、両方含むレコードはないのでヒットし -ません。 +In the following example, there are records that have only `"mail"` or `"web"` but there are no records that have both `"mail"` and `"web"`. So the following `SELECT` returns no record: ```sql SELECT jsonb_pretty(record) FROM logs WHERE record @> '{"tags": ["mail", "web"]}'::jsonb; @@ -566,16 +552,13 @@ SELECT jsonb_pretty(record) FROM logs WHERE record @> '{"tags": ["mail", "web"]} -- (0 rows) ``` -#### `@@`演算子 +See [`@>` operator](../reference/operators/jsonb-contain.html) for more details. + +### `@@` operator -`@@`演算子は -[Groongaのスクリプト構文](http://groonga.org/ja/docs/reference/grn_expr/script_syntax.html) -で条件を指定します。条件をどのように指定すればよいかわかるためには、 -PGroongaがどのように`jsonb`のデータに対してインデックスを作成している -かを理解する必要があります。 +`@@` operator uses [Groonga's script syntax](http://groonga.org/docs/reference/grn_expr/script_syntax.html) for specifying search condition. You need to understand how PGroonga creates index against `jsonb` data to create search condition. -PGroongaは`jsonb`の値を分解し、それぞれの値に対してインデックスを張っ -ています。SQLでいうと次のスキーマがあると考えてください。 +PGroonga splits a `jsonb` value into values and then creates indexes against these values. In SQL, think about the following schema: ```sql CREATE TABLE values ( @@ -584,17 +567,17 @@ CREATE TABLE values ( paths text[], type text, boolean boolean, - number double, + number double precision, string text, size numeric ); ``` -それぞれ次の値が入っています。 +Here are descriptions of column: - * `key`: 同じ値では同一になる値。フォーマットは「`${パス}|${種類}|${値}`」。条件で使うことはない。 - * `path`: その値の位置を示すルートからのパス。[jq](https://stedolan.github.io/jq/)と互換で、オブジェクトは`["${要素名}"]`、配列は`[]`となる。たとえば、`{"tags": ["web"]}`の`"web"`を示すパスは`.["tags"][]`。パスが完全にわかっている場合は条件でこの値を使う。 - * `paths`: その値の位置を示すパスが複数入っている。絶対パス、サブパス、`.${要素名1}.${要素名2}`表記のパス、配列なしのパスが入っているので条件で指定するときに便利。たとえば、`{"a": {"b": "c": ["x"]}}`の`"x"`の場合は次のパスが入っている。 + * `key`: The ID of the value. If value has the same path and content, `key` is the same value. Key format is "`${PATH}|${TYPE}|${VALUE}`". It's not used in search condition. + * `path`: The path of the value from root. It uses [jq](https://stedolan.github.io/jq/) compatible format. Object is `["${ELEMENT_NAME}"]`, array is `[]`. For example, the path of `"web"` in `{"tags": ["web"]}` is `.["tags"][]`. If you know absolute path of the value, you can use this value in search condition. + * `paths`: The paths of the value. It includes absolute path, sub paths, `.${ELEMENT_NAME1}.${ELEMENT_NAME2}` format paths, paths without array. This column is convenient for search condition because you can use one of them for search condition. Here are paths for `"x"` in `{"a": {"b": "c": ["x"]}}`: * `.a.b.c` * `.["a"]["b"]["c"]` * `.["a"]["b"]["c"][]` @@ -608,18 +591,18 @@ CREATE TABLE values ( * `["c"]` * `["c"][]` * `[]` - * `type`: そのパスの値の種類。種類によって値がどのカラムに入るかが変わる。次のうちのどれか。 - * `object`: オブジェクト。値はない。 - * `array`: 配列。`size`に要素数が入る。 - * `boolean`: 真偽値。`boolean`に値が入る。 - * `number`: 数値。`number`に値が入る。 - * `string`: 文字列。`string`に値が入る。 - * `boolean`: `type`が`boolean`のとき有効な値が入っている。それ以外のときは`false`が入っている。 - * `number`: `type`が`number`のとき有効な値が入っている。それ以外のときは`0`が入っている。 - * `string`: `type`が`string`のとき有効な値が入っている。それ以外のときは空文字列が入っている。 - * `size`: `type`が`array`のとき配列の要素数が入っている。それ以外のときは`0`が入っている。 - -たとえば、次のJSONを考えます。 + * `type`: The type of the value. This column value is one of them: + * `"object"`: Object. No value. + * `"array"`: Array. The number of elements is stored in `size` column. + * `"boolean"`: Boolean. The value is stored in `boolean` column. + * `"number"`: Number. The value is stored in `number` column. + * `"string"`: String. The value is stored in `string` column. + * `boolean`: The value if `type` column value is `"boolean"`, `false` otherwise. + * `number`: The value if `type` column value is `"number"`, `0` otherwise. + * `string`: The value if `type` column value is `"string"`, `""` otherwise. + * `size`: The number of elements if `type` column value is `"array"`, `0` otherwise. + +Here is a sample JSON: ```json { @@ -633,7 +616,7 @@ CREATE TABLE values ( } ``` -このJSONは次のように分解されます。（一部です。） +The JSON is split to the following values. (They are part of all split values.) | key | path | paths | type | boolean | number | string | size | | --- | ---- | ----- | ---- | ------- | ------ | ------ | ---- | @@ -641,9 +624,9 @@ CREATE TABLE values ( | `.["message"]|string|GET /` | `.["message"]` | `[.message, .["message"], message, ["message"]]` | `string` | | | `GET /` | | | `.["tags"][]|string|web` | `.["tags"]` | `[.tags, .["tags"], .["tags"][], tags, ["tags"], ["tags"][], []]` | `string` | | | `web` | | -`@`演算子の条件では分解した個々の値を特定する条件を指定します。指定した条件すべてを含んだ`jsonb`がマッチした`jsonb`になります。 +You specify condition that matches split value to `@@` operator. If there is one or more values that match specified condition in `jsonb`, the `jsonb` is matched. -`www.example.com`という文字列を含んだ`jsonb`を検索する場合は次のようにします。 +Here is a condition that searches `jsonb` that has `www.example.com` string: ```sql SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'string == "www.example.com"'; @@ -669,7 +652,7 @@ SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'string == "www.example.co -- (2 rows) ``` -`code`が`200`台のレコードを検索する場合は次のようにします。省略記法（`.code`）でパスを指定したいので`paths @ "..."`という条件指定をしています。 +Here is a condition that searches `jsonb` that has number between `200` to `299` as `code` column value. The condition uses `paths @ "..."` syntax to use simple path format (`.code`) to specify path. ```sql SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'paths @ ".code" && number >= 200 && number < 300'; @@ -687,7 +670,7 @@ SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'paths @ ".code" && number -- (1 row) ``` -全文検索をする場合は次のように`string @ "..."`を使います。 +Here is a condition for full text search from all text values in `jsonb`: ```sql SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'string @ "started"'; @@ -704,8 +687,7 @@ SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'string @ "started"'; -- (1 row) ``` -クエリー構文（`a OR b`のような書き方）を使って全文検索をしたい場合は -`query("string", "...")`を使います。 +You can use [Groonga's query syntax](http://groonga.org/docs/reference/grn_expr/query_syntax.html) (`a OR b` can be used) for full text search by `query("string", "...")` syntax: ```sql SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'query("string", "send OR server")'; @@ -730,6 +712,7 @@ SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'query("string", "send OR -- (2 rows) ``` +See [`@@` operator for `jsonb`](../reference/operators/jsonb-query.html) for more details. {: #groonga} -------------- next part -------------- HTML����������������������������...Download