Kouhei Sutou
null+****@clear*****
Tue May 9 09:43:34 JST 2017
Kouhei Sutou 2017-05-09 09:43:34 +0900 (Tue, 09 May 2017) New Revision: 5162ec24337d74416388291511816daa7a922975 https://github.com/pgroonga/pgroonga.github.io/commit/5162ec24337d74416388291511816daa7a922975 Message: Update operators list Copied files: reference/operators/contain-jsonb.md (from reference/operators/jsonb-contain.md) reference/operators/script-jsonb.md (from reference/operators/jsonb-query.md) Modified files: _layouts/base.html reference/index.md reference/operators/jsonb-contain.md reference/operators/jsonb-query.md Modified: _layouts/base.html (+4 -0) =================================================================== --- _layouts/base.html 2017-05-08 20:59:57 +0900 (a86d680) +++ _layouts/base.html 2017-05-09 09:43:34 +0900 (675f37d) @@ -14,6 +14,10 @@ <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> + {% if page.redirect %} + <meta http-equiv="refresh" content="0;URL={{ page.redirect }}"> + {% endif %} + <link rel="shortcut icon" href="/favicon.ico"> <link rel="icon" href="/favicon.png"> Modified: reference/index.md (+231 -23) =================================================================== --- reference/index.md 2017-05-08 20:59:57 +0900 (982f0ae) +++ reference/index.md 2017-05-09 09:43:34 +0900 (69776f7) @@ -28,63 +28,271 @@ PGroonga defines functions, operators, operator classes and so on into `pgroonga ## Operators +### For `text` + +By the default operator class: + * [`LIKE` operator](operators/like.html) * `ILIKE` operator - * [`&@` operator](operators/match-v2.html) + * [`&@` operator](operators/match-v2.html): Full text search by a keyword - * [`%%` operator](operators/match.html) + * [`%%` operator](operators/match.html): Full text search by a keyword * Deprecated since 1.2.0. Use [`&@` operator](operators/match-v2.html) instead. - * [`&?` operator](operators/query-v2.html) for non `jsonb` types + * [`&?` operator](operators/query-v2.html): Full text search by easy to use query language - * [`@@` operator](operators/query.html) for non `jsonb` types + * [`@@` operator](operators/query.html): Full text search by easy to use query language * Deprecated since 1.2.0. Use [`&?` operator](operators/query-v2.html) instead. - * [`@@` operator](operators/jsonb-query.html) for `jsonb` type +By `pgroonga.text_regexp_ops` operator class: + + * [`LIKE` operator](operators/like.html) + + * `ILIKE` operator + + * [`&~` operator](operators/regular-expression-v2.html): Search by a regular expression + + * [`@~` operator](operators/regular-expression.html): Search by regular a expression + + * Deprecated since 1.2.1. Use [`&~` operator](operators/regular-expression-v2.html) instead. + +### For `text[]` + + * [`&@` operator](operators/match-v2.html): Full text search by a keyword + + * [`%%` operator](operators/match.html): Full text search by a keyword + + * Deprecated since 1.2.0. Use [`&@` operator](operators/match-v2.html) instead. + + * [`&?` operator](operators/query-v2.html): Full text search by easy to use language - * [`@>` operator](operators/jsonb-contain.html) + * [`@@` operator](operators/query.html): Full text search by easy to use language + + * Deprecated since 1.2.0. Use [`&?` operator](operators/query-v2.html) instead. + +### For `varchar` + +By the default operator class: + + * `<` + + * `<=` + + * `=` + + * `>=` + + * `>` + +By `pgroonga.varchar_full_text_search_ops` operator class: + + * [`&@` operator](operators/match-v2.html): Full text search by a keyword + + * [`%%` operator](operators/match.html): Full text search by a keyword + + * Deprecated since 1.2.0. Use [`&@` operator](operators/match-v2.html) instead. - * [`@~` operator](operators/regular-expression.html) + * [`&?` operator](operators/query-v2.html): Full text search by easy to use language -### v2 operators + * [`@@` operator](operators/query.html): Full text search by easy to use language + + * Deprecated since 1.2.0. Use [`&?` operator](operators/query-v2.html) instead. + +By `pgroonga.varchar_regexp_ops` operator class: + + * [`&~` operator](operators/regular-expression-v2.html): Search by regular expression + + * [`@~` operator](operators/regular-expression.html): Search by regular expression + + * Deprecated since 1.2.1. Use [`&~` operator](operators/regular-expression-v2.html) instead. + +### For `varchar[]` + + * [`&@` operator](operators/match-v2.html): Full text search by a keyword + + * [`%%` operator](operators/match.html): Full text search by a keyword + + * Deprecated since 1.2.1. Use [`&@` operator](operators/match-v2.html) instead. + +### For boolean, numbers and timestamps + +Supported types: `boolean`, `smallint`, `integer`, `bigint`, `real`, `double precision`, `timestamp` and `timestamp with time zone` + + * `<` + + * `<=` + + * `=` + + * `>=` + + * `>` + +### For `jsonb` + + * [`&@` operator](operators/match-jsonb-v2.html): Full text search against all text data in `jsonb` by a keyword + + * [`&?` operator](operators/query-jsonb-v2.html): Full text search against all text data in `jsonb` by easy to use query language + + * [`` &` `` operator](operators/script-jsonb-v2.html): Advanced search by ECMAScript like query language + + * [`@@` operator](operators/script-jsonb.html): Advanced search by ECMAScript like query language + + * Deprecated since 1.2.1. Use [`` &` `` operator](operators/script-jsonb-v2.html) instead. + + * [`@>` operator](operators/contain-jsonb.html): Search by a `jsonb` data + +## Operators v2 PGroonga 1.Y.Z provides `pgroonga.XXX_v2` operator classes. They don't provide backward compatibility until PGroonga 2.0.0. But they include many improvements aggressively when new versions are released. If you use them, you need to use [incompatible case steps](../upgrade/#incompatible-case) to upgrade PGroonga. - * `pgroonga.text_full_text_search_ops_v2` operator class +### For `text` + +By `pgroonga.text_full_text_search_ops_v2` operator class: + + * [`LIKE` operator](operators/like.html) + + * `ILIKE` operator + + * [`&@` operator](operators/match-v2.html): Full text search by a keyword + + * [`%%` operator](operators/match.html): Full text search by a keyword + + * Don't use this operator class for newly written code. It's just for backward compatibility. + + * [`&?` operator](operators/query-v2.html): Full text search by easy to use query language + + * [`@@` operator](operators/query.html): Full text search by easy to use query language + + * Don't use this operator class for newly written code. It's just for backward compatibility. + + * [`&~?` operator](operators/similar-search-v2.html): Similar search + + * [`` &` `` operator](operators/script-v2.html): Advanced search by ECMAScript like query language + + * [`&@>` operator](operators/match-contain-v2.html): Full text search by an array of keywords + + * [`&?>` operator](operators/query-contain-v2.html): Full text search by an array of queries in easy to use query language + +By `pgroonga.text_term_search_ops_v2` operator class: + + * [`&^` operator](operators/prefix-search-v2.html): Prefix search + + * [`&^~` operator](operators/prefix-rk-search-v2.html): Prefix RK search + + * [`&^>` operator](operators/prefix-search-contain-v2.html): Prefix search by an array of prefixes + + * [`&^~>` operator](operators/prefix-rk-search-contain-v2.html): Prefix RK search by an array of prefixes + +By `pgroonga.text_regexp_ops_v2` operator class: + + * [`LIKE` operator](operators/like.html) + + * `ILIKE` operator + + * [`&~` operator](operators/regular-expression-v2.html): Search by regular expression + + * [`@~` operator](operators/regular-expression.html): Search by regular expression + + * Don't use this operator class for newly written code. It's just for backward compatibility. + +### For `text[]` + +By `pgroonga.text_full_text_search_ops_v2` operator class: + + * [`&@` operator](operators/match-v2.html): Full text search by a keyword + + * [`%%` operator](operators/match.html): Full text search by a keyword + + * Don't use this operator class for newly written code. It's just for backward compatibility. + + * [`&?` operator](operators/query-v2.html): Full text search by easy to use language + + * [`@@` operator](operators/query.html): Full text search by easy to use language + + * Don't use this operator class for newly written code. It's just for backward compatibility. + + * [`&~?` operator](operators/similar-search-v2.html): Similar search + + * [`` &` `` operator](operators/script-v2.html): Advanced search by ECMAScript like query language + + * [`&@>` operator](operators/match-contain-v2.html): Full text search by an array of keywords + + * [`&?>` operator](operators/query-contain-v2.html): Full text search by an array of queries in easy to use query language + +By `pgroonga.text_array_term_search_ops_v2` operator class + + * [`&^` operator](operators/prefix-search-v2.html): Prefix search + + * [`&^~` operator](operators/prefix-rk-search-v2.html): Prefix RK search + + * [`&^>` operator](operators/prefix-search-contain-v2.html): Prefix search by an array of prefixes + + * [`&^~>` operator](operators/prefix-rk-search-contain-v2.html): Prefix RK search by an array of prefixes + +### For `varchar` + +By `pgroonga.varchar_full_text_search_ops_v2` operator class: + + * [`&@` operator](operators/match-v2.html): Full text search by a keyword + + * [`%%` operator](operators/match.html): Full text search by a keyword + + * Don't use this operator class for newly written code. It's just for backward compatibility. + + * [`&?` operator](operators/query-v2.html): Full text search by easy to use query language + + * [`@@` operator](operators/query.html): Full text search by easy to use query language + + * Don't use this operator class for newly written code. It's just for backward compatibility. + + * [`&~?` operator](operators/similar-search-v2.html): Similar search + + * [`` &` `` operator](operators/script-v2.html): Advanced search by ECMAScript like query language + + * [`&@>` operator](operators/match-contain-v2.html): Full text search by an array of keywords + + * [`&?>` operator](operators/query-contain-v2.html): Full text search by an array of queries in easy to use query language + +By `pgroonga.varchar_regexp_ops_v2` operator class: + + * [`&~` operator](operators/regular-expression-v2.html): Search by regular expression + + * [`@~` operator](operators/regular-expression.html): Search by regular expression - * `LIKE` operator + * Don't use this operator class for newly written code. It's just for backward compatibility. - * `ILIKE` operator +### For `varchar[]` - * [`&@` operator](operators/match-v2.html) +By `pgroonga.varchar_array_ops_v2` operator class: - * [`&?` operator](operators/query-v2.html) for non `jsonb` types + * [`&@` operator](operators/match-v2.html): Full text search by a keyword - * [`&~?` operator](operators/similar-search-v2.html) + * [`%%` operator](operators/match.html): Full text search by a keyword - * [`` &` `` operator](operators/script-v2.html) + * Don't use this operator class for newly written code. It's just for backward compatibility. - * [`&@>` operator](operators/match-contain-v2.html) +### For `jsonb` - * [`&?>` operator](operators/query-contain-v2.html) for non `jsonb` types +By `pgroonga.jsonb_ops_v2` operator class: - * `pgroonga.text_term_search_ops_v2` operator class + * [`&@` operator](operators/match-jsonb-v2.html): Full text search against all text data in `jsonb` by a keyword - * [`&^` operator](operators/prefix-search-v2.html) + * [`&?` operator](operators/query-jsonb-v2.html): Full text search against all text data in `jsonb` by easy to use query language - * [`&^~` operator](operators/prefix-rk-search-v2.html) + * [`` &` `` operator](operators/script-jsonb-v2.html): Advanced search by ECMAScript like query language - * `pgroonga.text_array_term_search_ops_v2` operator class + * [`@@` operator](operators/script-jsonb.html): Advanced search by ECMAScript like query language - * [`&^>` operator](operators/prefix-search-contain-v2.html) + * Don't use this operator class for newly written code. It's just for backward compatibility. - * [`&^~>` operator](operators/prefix-rk-search-contain-v2.html) + * [`@>` operator](operators/contain-jsonb.html): Search by a `jsonb` data ## Functions Copied: reference/operators/contain-jsonb.md (+0 -0) 100% =================================================================== Modified: reference/operators/jsonb-contain.md (+1 -115) =================================================================== --- reference/operators/jsonb-contain.md 2017-05-08 20:59:57 +0900 (a474eaf) +++ reference/operators/jsonb-contain.md 2017-05-09 09:43:34 +0900 (05d3e2d) @@ -1,119 +1,5 @@ --- title: "@> operator" upper_level: ../ +redirect: contain-jsonb.html --- - -# `@>` operator - -## Summary - -PGroonga supports fast index search by `@>` operator. - -[`@>` operator is a built-in PostgreSQL operator]({{ site.postgresql_doc_base_url.en }}/functions-json.html#FUNCTIONS-JSONB-OP-TABLE). `@>` operator returns true when the right hand side `jsonb` type value is a subset of left hand side `jsonb` type value. - -## Syntax - -Here is the syntax of this operator: - -```sql -jsonb_column @> jsonb_query -``` - -`jsonb_column` is a column that its type is `jsonb`. - -`jsonb_query` is a `jsonb` type value used as query. - -The operator returns `true` when `jsonb_query` is a subset of `jsonb_column` value, `false` otherwise. - -## Usage - -Here are sample schema and data for examples: - -```sql -CREATE TABLE logs ( - record jsonb -); - -CREATE INDEX pgroonga_logs_index ON logs USING pgroonga (record); - -INSERT INTO logs - VALUES ('{ - "message": "Server is started.", - "host": "www.example.com", - "tags": [ - "web", - "example.com" - ] - }'); -INSERT INTO logs - VALUES ('{ - "message": "GET /", - "host": "www.example.com", - "code": 200, - "tags": [ - "web", - "example.com" - ] - }'); -INSERT INTO logs - VALUES ('{ - "message": "Send to <info �� example.com>.", - "host": "mail.example.net", - "tags": [ - "mail", - "example.net" - ] - }'); -``` - -Disable sequential scan: - -```sql -SET enable_seqscan = off; -``` - -Here is an example for match case: - -(It uses [`jsonb_pretty()` function]({{ site.postgresql_doc_base_url.en }}/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; --- jsonb_pretty --- ------------------------------------- --- { + --- "host": "www.example.com", + --- "tags": [ + --- "web", + --- "example.com" + --- ], + --- "message": "Server is started."+ --- } --- { + --- "code": 200, + --- "host": "www.example.com", + --- "tags": [ + --- "web", + --- "example.com" + --- ], + --- "message": "GET /" + --- } --- (2 rows) -``` - -Here is an example for not match case. - -If you use an array in the search condition `jsonb` type value, all elements must be included in the search target `jsonb` type value. Position of element isn't cared. If there are one or more elements that are included in the search condition `jsonb` type value but aren't included in the search target `jsonb` type value, record that have the search target `jsonb` type value isn't matched. - -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; --- jsonb_pretty --- -------------- --- (0 rows) -``` - -## See also - - * [`jsonb` support](../jsonb.html) - * [`@@` operator](jsonb-query.html) Modified: reference/operators/jsonb-query.md (+1 -245) =================================================================== --- reference/operators/jsonb-query.md 2017-05-08 20:59:57 +0900 (ef751e8) +++ reference/operators/jsonb-query.md 2017-05-09 09:43:34 +0900 (054ed3a) @@ -1,249 +1,5 @@ --- title: "@@ operator for jsonb type" upper_level: ../ +redirect: script-jsonb.html --- - -# `@@` operator for `jsonb` type - -## Summary - -`@@` operator is a PGroonga original operator. You can use complex condition that can't be written by [`@>` operator](jsonb-contain.html) such as range search. - -If you know [JsQuery](https://github.com/postgrespro/jsquery), you can understand like "PGroonga provides `jsonb` type related search features that are similar to JsQuery with different syntax". - -## Syntax - -Here is the syntax of this operator: - -```sql -jsonb_column @@ condition -``` - -`jsonb_column` is a column that its type is `jsonb`. - -`condition` is a `text` value used as query. It uses [Groonga's script syntax](http://groonga.org/docs/reference/grn_expr/script_syntax.html). - -The operator returns `true` when `condition` matches `jsonb_column` value, `false` otherwise. - -## Usage - -Here are sample schema and data for examples: - -```sql -CREATE TABLE logs ( - record jsonb -); - -CREATE INDEX pgroonga_logs_index ON logs USING pgroonga (record); - -INSERT INTO logs - VALUES ('{ - "message": "Server is started.", - "host": "www.example.com", - "tags": [ - "web", - "example.com" - ] - }'); -INSERT INTO logs - VALUES ('{ - "message": "GET /", - "host": "www.example.com", - "code": 200, - "tags": [ - "web", - "example.com" - ] - }'); -INSERT INTO logs - VALUES ('{ - "message": "Send to <info �� example.com>.", - "host": "mail.example.net", - "tags": [ - "mail", - "example.net" - ] - }'); -``` - -Disable sequential scan: - -```sql -SET enable_seqscan = off; -``` - -You need to understand how PGroonga creates index against `jsonb` type value to create search condition. - -PGroonga splits a `jsonb` type value into values and then creates indexes against these values. In SQL, think about the following schema: - -```sql -CREATE TABLE values ( - key text PRIMARY KEY, - path text, - paths text[], - type text, - boolean boolean, - number double precision, - string text, - size numeric -); -``` - -Here are descriptions of column: - - * `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 and 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"][]` - * `a.b.c` - * `["a"]["b"]["c"]` - * `["a"]["b"]["c"][]` - * `b.c` - * `["b"]["c"]` - * `["b"]["c"][]` - * `c` - * `["c"]` - * `["c"][]` - * `[]` - - * `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 -{ - "message": "GET /", - "host": "www.example.com", - "code": 200, - "tags": [ - "web", - "example.com" - ] -} -``` - -The JSON is split to the following values. (They are part of all split values.) - -| key | path | paths | type | boolean | number | string | size | -| --- | ---- | ----- | ---- | ------- | ------ | ------ | ---- | -| `.|object` | `.` | `[.]` | `object` | | | | | -| `.["message"]|string|GET /` | `.["message"]` | `[.message, .["message"], message, ["message"]]` | `string` | | | `GET /` | | -| `.["tags"][]|string|web` | `.["tags"]` | `[.tags, .["tags"], .["tags"][], tags, ["tags"], ["tags"][], []]` | `string` | | | `web` | | - -You specify condition that matches split value to `@@` operator. If there is one or more split values that match specified condition in `jsonb` type value, the `jsonb` type value is matched. - -Here is a condition that searches `jsonb` type value that has `www.example.com` string: - -(It uses [`jsonb_pretty()` function]({{ site.postgresql_doc_base_url.en }}/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE) provided since PostgreSQL 9.5 for readability.) - -```sql -SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'string == "www.example.com"'; --- jsonb_pretty --- ------------------------------------- --- { + --- "host": "www.example.com", + --- "tags": [ + --- "web", + --- "example.com" + --- ], + --- "message": "Server is started."+ --- } --- { + --- "code": 200, + --- "host": "www.example.com", + --- "tags": [ + --- "web", + --- "example.com" + --- ], + --- "message": "GET /" + --- } --- (2 rows) -``` - -Here is a condition that searches `jsonb` type value 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'; --- jsonb_pretty --- -------------------------------- --- { + --- "code": 200, + --- "host": "www.example.com",+ --- "tags": [ + --- "web", + --- "example.com" + --- ], + --- "message": "GET /" + --- } --- (1 row) -``` - -Here is a condition for full text search from all text values in `jsonb` value type: - -```sql -SELECT jsonb_pretty(record) FROM logs WHERE record @@ 'string @ "started"'; --- jsonb_pretty --- ------------------------------------- --- { + --- "host": "www.example.com", + --- "tags": [ + --- "web", + --- "example.com" + --- ], + --- "message": "Server is started."+ --- } --- (1 row) -``` - -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")'; --- jsonb_pretty --- ---------------------------------------------- --- { + --- "host": "www.example.com", + --- "tags": [ + --- "web", + --- "example.com" + --- ], + --- "message": "Server is started." + --- } --- { + --- "host": "mail.example.net", + --- "tags": [ + --- "mail", + --- "example.net" + --- ], + --- "message": "Send to <info �� example.com>."+ --- } --- (2 rows) -``` - -## See also - - * [`jsonb` support](../jsonb.html) - * [`@>` operator](jsonb-contain.html) Copied: reference/operators/script-jsonb.md (+0 -0) 100% =================================================================== -------------- next part -------------- HTML����������������������������...Download