Percolate table

A percolate table is a special table that stores queries rather than documents. It is used for prospective searches, or “search in reverse.”

To learn more about performing a search query against a percolate table, see the section Percolate query.
To learn how to prepare a table for searching, see the section Adding rules to a percolate table.

The schema of a percolate table is fixed and contains the following fields:

Field	Description
ID	An unsigned 64-bit integer with auto-increment functionality. It can be omitted when adding a PQ rule, as described in add a PQ rule
Query	Full-text query of the rule, which can be thought of as the value of MATCH clause or JSON /search. If per field operators are used inside the query, the full-text fields need to be declared in the percolate table configuration. If the stored query is only for attribute filtering (without full-text querying), the query value can be empty or omitted. The value of this field should correspond to the expected document schema, which is specified when creating the percolate table.
Filters	Optional. Filters are an optional string containing attribute filters and/or expressions, defined the same way as in the WHERE clause or JSON filtering. The value of this field should correspond to the expected document schema, which is specified when creating the percolate table.
Tags	Optional. Tags represent a list of string labels separated by commas that can be used for filtering/deleting PQ rules. The tags can also be returned along with matching documents when performing a Percolate query

Note that you do not need to add the above fields when creating a percolate table.

What you need to keep in mind when creating a new percolate table is to specify the expected schema of a document, which will be checked against the rules you will add later. This is done in the same way as for any other local table.

SQL:

Creating a percolate table via MySQL protocol:

CREATE TABLE products(title text, meta json) type='pq';

Query OK, 0 rows affected (0.00 sec)

Creating a percolate table via JSON over HTTP:

POST /cli -d "CREATE TABLE products(title text, meta json) type='pq'"

{
"total":0,
"error":"",
"warning":""
}

Creating a percolate table via PHP client:

$index = [
    'index' => 'products',
    'body' => [
        'columns' => [
            'title' => ['type' => 'text'],
            'meta' => ['type' => 'json']
        ],
        'settings' => [
            'type' => 'pq'
        ]
    ]
];
$client->indices()->create($index);

Array(
    [total] => 0
    [error] =>
    [warning] =>
)

Python:

utilsApi.sql('CREATE TABLE products(title text, meta json) type=\'pq\'')

javascript:

res = await utilsApi.sql('CREATE TABLE products(title text, meta json) type=\'pq\'');

java:

utilsApi.sql("CREATE TABLE products(title text, meta json) type='pq'");

Creating a percolate table via config:

table products {
  type = percolate
  path = tbl_pq
  rt_field = title
  rt_attr_json = meta
}