WHERE is an SQL clause which works for both fulltext matching and additional filtering. The following operators are available:

• Comparison operators <, > <=, >=, =, <>, BETWEEN, IN, IS NULL
• Boolean operators AND, OR, NOT

MATCH('query') is supported and maps to fulltext query.

{col_name | expr_alias} [NOT] IN @uservar condition syntax is supported. Refer to SET syntax for a description of global user variables.

If you prefer HTTP JSON interface you can also do filtering. It looks more complex that in SQL, but can be recommended for the cases when you need to prepare a query in a programmatic manner, for example as a result of a form in your application filled out by the user.

POST /search
{
  "index": "test1",
  "query": {
    "bool": {
      "must": [
        { "match" : { "_all" : "product" } },
        { "range": { "price": { "gte": 500, "lte": 1000 } } }
      ],
      "must_not": {
        "range": { "revision": { "lt": 15 } }
      }
    }
  }
}

POST /search
{
  "index":"test1",
  "query": {
    "bool": {
      "must": [
        { "match": {"_all":"keyword"} },
        { "range": { "revision": { "gte": 14 } } }
      ]
    }
  }
}

"must": [
  {"equals" : { "product_codes": 5 }},
  {"equals" : { "product_codes": 6 }}
]

  {"in" : { "all(product_codes)": [5,6] }}

"should": [
  {"equals" : { "product_codes": 7 }},
  {"equals" : { "product_codes": 8 }}
]

  {"in" : { "any(product_codes)": [7,8] }}

POST /search
{
  "index":"t",
  "query": {
    "bool": {
      "should": [
        {
          "equals": {
            "b": 1
          }
        },
        {
          "equals": {
            "b": 3
          }
        }
      ],
      "must": [
        {
          "equals": {
            "a": 1
          }
        }
      ],
      "must_not": {
        "equals": {
          "b": 2
        }
      }
    }
  }
}

a = 2 and (a = 10 or b = 0)

POST /search
{
  "index":"t",
  "query": {
    "bool": {
      "must": [
        {
          "equals": {
            "a": 2
          }
        },
        {
          "bool": {
            "should": [
              {
                "equals": {
                  "a": 10
                }
              },
              {
                "equals": {
                  "b": 0
                }
              }
            ]
          }
        }
      ]
    }
  }
}

(a = 1 and b = 1) or (a = 10 and b = 2) or (b = 0)

POST /search
{
  "index":"t",
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "equals": {
                  "a": 1
                }
              },
              {
                "equals": {
                  "b": 1
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "equals": {
                  "a": 10
                }
              },
              {
                "equals": {
                  "b": 2
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "equals": {
                  "b": 0
                }
              }
            ]
          }

        }
      ]
    }
  }
}

POST /search
{
  "index": "test1",
  "query": {
    "bool": {
      "must": [
        { "query_string" : "product" },
        { "query_string" : "good" }
      ]
    }
  }
}

POST /search
{
  "index":"test1",
  "query": {
    "equals": { "price": 500 }
  }
}

POST /search
{
  "index":"test1",
  "query": {
    "equals": { "any(price)": 100 }
  }
}

POST /search
{
  "index":"test1",
  "query": {
    "in": {
      "price": [1,10,100]
    }
  }
}

POST /search
{
  "index":"test1",
  "query": {
    "in": {
      "all(price)": [1,10]
    }
  }
}

POST /search
{
  "index":"test1",
  "query": {
    "range": {
      "price": {
        "gte": 500,
        "lte": 1000
      }
    }
  }
}

POST /search
{
  "index":"test",
  "query": {
    "geo_distance": {
      "location_anchor": {"lat":49, "lon":15},
      "location_source": {"attr_lat, attr_lon"},
      "distance_type": "adaptive",
      "distance":"100 km"
    }
  }
}

POST /search
{
  "index": "geodemo",
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "*": "station"
          }
        },
        {
          "equals": {
            "state_code": "ENG"
          }
        },
        {
          "geo_distance": {
            "distance_type": "adaptive",
            "location_anchor": {
              "lat": 52.396,
              "lon": -1.774
            },
            "location_source": "latitude_deg,longitude_deg",
            "distance": "10000 m"
          }
        }
      ]
    }
  }
}

Manticore lets you use arbitrary arithmetic expressions both via SQL and HTTP, involving attribute values, internal attributes (document ID and relevance weight), arithmetic operations, a number of built-in functions, and user-defined functions. Here’s the complete reference list for quick access.

+, -, *, /, %, DIV, MOD

The standard arithmetic operators. Arithmetic calculations involving those can be performed in three different modes:

1. using single-precision, 32-bit IEEE 754 floating point values (the default),
2. using signed 32-bit integers
3. using 64-bit signed integers

The expression parser will automatically switch to integer mode if there are no operations the result in a floating point value. Otherwise, it will use the default floating point mode. For instance, a+b will be computed using 32-bit integers if both arguments are 32-bit integers; or using 64-bit integers if both arguments are integers but one of them is 64-bit; or in floats otherwise. However, a/b or sqrt(a) will always be computed in floats, because these operations return a result of non-integer type. To avoid the first, you can either use IDIV(a,b) or a DIV b form. Also, a*b will not be automatically promoted to 64-bit when the arguments are 32-bit. To enforce 64-bit results, you can use BIGINT(), but note that if there are non-integer operations, BIGINT() will simply be ignored.

<, > <=, >=, =, <>

Comparison operators return 1.0 when the condition is true and 0.0 otherwise. For instance, (a=b)+3 will evaluate to 4 when attribute a is equal to attribute b, and to 3 when a is not. Unlike MySQL, the equality comparisons (ie. = and <> operators) introduce a small equality threshold (1e-6 by default). If the difference between compared values is within the threshold, they will be considered equal. BETWEEN and IN operators in case of multi-value attribute return true if at least one value matches the condition(same as ANY()). IN doesn't support JSON attributes. IS (NOT) NULL is supported only for JSON attributes.

AND, OR, NOT

Boolean operators (AND, OR, NOT) behave as usual. They are left-associative and have the least priority compared to other operators. NOT has more priority than AND and OR but nevertheless less than any other operator. AND and OR have the same priority so brackets use is recommended to avoid confusion in complex expressions.

&, |

These operators perform bitwise AND and OR respectively. The operands must be of an integer types.

• ABS()
• ALL()
• ANY()
• ATAN2()
• BIGINT()
• BITDOT()
• BM25F()
• CEIL()
• CONCAT()
• CONTAINS()
• COS()
• CRC32()
• DAY()
• DOUBLE()
• EXP()
• FIBONACCI()
• FLOOR()
• GEODIST()
• GEOPOLY2D()
• GREATEST()
• HOUR()
• IDIV()
• IF()
• IN()
• INDEXOF()
• INTEGER()
• INTERVAL()
• LAST_INSERT_ID()
• LEAST()
• LENGTH()
• LN()
• LOG10()
• LOG2()
• MAX()
• MIN()
• MINUTE()
• MIN_TOP_SORTVAL()
• MIN_TOP_WEIGHT()
• MONTH()
• NOW()
• PACKEDFACTORS()
• POLY2D()
• POW()
• RAND()
• REGEX()
• REMAP()
• SECOND()
• SIN()
• SINT()
• SQRT()
• SUBSTRING_INDEX()
• TO_STRING()
• UINT()
• YEAR()
• YEARMONTH()
• YEARMONTHDAY()
• WEIGHT()

In HTTP JSON interface expressions are supported via script_fields and expressions

{
    "index": "test",
    "query": { 
        "match_all": {} 
    }, "script_fields": {
        "add_all": { 
            "script": { 
                "inline": "( gid * 10 ) | crc32(title)" 
            } 
        },
        "title_len": { 
            "script": { 
                "inline": "crc32(title)" 
            } 
        }
    }
}

{
    "index":"movies_rt",
    "script_fields":{
        "cond1":{
            "script":{
                "inline":"actor_2_facebook_likes =296 OR movie_facebook_likes =37000"
            }
        },
        "cond2":{
            "script":{
                "inline":"IF (IN (content_rating,'TV-PG','PG'),2, IF(IN(content_rating,'TV-14','PG-13'),1,0))"
            }
        }
    },
    "limit":10,
    "sort":[
        {
            "cond2":"desc"
        },
        {
            "actor_1_name":"asc"
        },
        {
            "actor_2_name":"desc"
        }
    ],
    "profile":true,
    "query":{
        "bool":{
            "must":[
                {
                    "match":{
                        "*":"star"
                    }
                },
                {
                    "equals":{
                        "cond1":1
                    }
                }
            ],
            "must_not":[
                {
                    "equals":{
                        "content_rating":"R"
                    }
                }
            ]
        }
    }
}

The expression values are by default included in the _source array of the result set. If the source is selective (see Source selection) the expressions name can be added to the _source parameter in the request.

{
  "index": "test",
  "query": { "match_all": {} },
  "expressions":
  {
      "add_all": "( gid * 10 ) | crc32(title)",
      "title_len": "crc32(title)"
  }
}

SQL SELECT clause and HTTP /search endpoint support a number of options that can be used to fine-tune search behaviour.

SELECT ... [OPTION <optionname>=<value> [ , ... ]] [/*+ [NO_][ColumnarScan|DocidIndex|SecondaryIndex(<attribute>[,...])]] /*]

POST /search
{   
    "index" : "index_name",
    "options":   
    {
        "optionname": "value",
        "optionname2": <value2>
    }
}

SELECT * FROM test WHERE MATCH('@title hello @body world')
OPTION ranker=bm25, max_matches=3000,
field_weights=(title=10, body=3), agent_query_timeout=10000

POST /search
{   
    "index" : "test",
    "query": {
      "match": {
        "title": "hello"
      },
      "match": {
        "body": "world"     
      }
    },
    "options":   
    {
        "ranker": "bm25",
        "max_matches": 3000,
        "field_weights": {
            "title": 10,
            "body": 3
        },
        "agent_query_timeout": 10000
    }
}

+------+-------+-------+
| id   | title | body  |
+------+-------+-------+
|    1 | hello | world |
+------+-------+-------+
1 row in set (0.00 sec)

{
  "took": 0,
  "timed_out": false,
  "hits": {
    "total": 1,
    "total_relation": "eq",
    "hits": [
      {
        "_id": "1",
        "_score": 10500,
        "_source": {
          "title": "hello",
          "body": "world"
        }
      }
    ]
  }
}

Supported options are:

Integer. Enables or disables guaranteed aggregate accuracy when running groupby queries in multiple threads. Default is 0.

When running a groupby query, it can be run in parallel on plain index on several pseudo shards (if pseudo_sharding is on). A similar approach works on RT indexes. Each shard/chunk executes the query, but the number of groups is limited by max_matches. If the result sets from different shards/chunks have different groups, the group counts and aggregates may be inaccurate. Note that Manticore tries to increase max_matches up to max_matches_increase_threshold based on the number of unique values of the groupby attribute (retrieved from secondary indexes). If it succeeds, there will be no loss in accuracy.

However, if the number of unique values of the groupby attribute is high, further increasing max_matches may not be a good strategy because it can lead to a loss in performance and higher memory usage. Setting accurate_aggregation to 1 forces groupby searches to run in a single thread, which fixes the accuracy issue. Note that running in a single thread is only enforced when max_matches cannot be set high enough; otherwise, searches with accurate_aggregation=1 will still run in multiple threads.

Overall, setting accurate_aggregation to 1 will guarantee group count and aggregate accuracy in RT indexes and plain indexes with pseudo_sharding=1. The downside is that searches will run slower because they will be forced to run in a single thread.

Integer. Max time in milliseconds to wait for remote queries to complete, see this section.

0 or 1 (0 by default). boolean_simplify=1 enables simplifying the query to speed it up.

String, user comment that gets copied to a query log file.

Integer. Max found matches threshold. The value is selected automatically if not specified.

• N = 0 disables the threshold
• N > 0: instructs Manticore to stop looking for results as soon as it finds N documents.
• not set: Manticore will decide automatically what the value should be.

In case Manticore cannot calculate the exact matching documents count you will see total_relation: gte in the query meta information, which means that the actual count is Greater Than or Equal to the total (total_found in SHOW META via SQL, hits.total in JSON via HTTP). If the total value is precise you'll get total_relation: eq.

0 or 1 (0 by default). Expands keywords with exact forms and/or stars when possible. Refer to expand_keywords for more details.

Named integer list (per-field user weights for ranking).

Example:

SELECT ... OPTION field_weights=(title=10, body=3)

Use global statistics (frequencies) from the global_idf file for IDF computations.

Quoted, comma-separated list of IDF computation flags. Known flags are:

• normalized: BM25 variant, idf = log((N-n+1)/n), as per Robertson et al
• plain: plain variant, idf = log(N/n), as per Sparck-Jones
• tfidf_normalized: additionally divide IDF by query word count, so that TF*IDF fits into [0, 1] range
• tfidf_unnormalized: do not additionally divide IDF by query word count where N is the collection size and n is the number of matched documents

The historically default IDF (Inverse Document Frequency) in Manticore is equivalent to OPTION idf='normalized,tfidf_normalized', and those normalizations may cause several undesired effects.

First, idf=normalized causes keyword penalization. For instance, if you search for the | something and the occurs in more than 50% of the documents, then documents with both keywords the and something will get less weight than documents with just one keyword something. Using OPTION idf=plain avoids this. Plain IDF varies in [0, log(N)] range, and keywords are never penalized; while the normalized IDF varies in [-log(N), log(N)] range, and too frequent keywords are penalized.

Second, idf=tfidf_normalized causes IDF drift over queries. Historically, we additionally divided IDF by query keyword count, so that the entire sum(tf*idf) over all keywords would still fit into [0,1] range. However, that means that queries word1 and word1 | nonmatchingword2 would assign different weights to the exactly same result set, because the IDFs for both word1 and nonmatchingword2 would be divided by 2. OPTION idf='tfidf_unnormalized' fixes that. Note that BM25, BM25A, BM25F() ranking factors will be scale accordingly once you disable this normalization.

IDF flags can be mixed; plain and normalized are mutually exclusive; tfidf_unnormalized and tfidf_normalized are mutually exclusive; and unspecified flags in such a mutually exclusive group take their defaults. That means that OPTION idf=plain is equivalent to a complete OPTION idf='plain,tfidf_normalized' specification.

Named integer list. Per-table user weights for ranking.

0 or 1,automatically sum DFs over all the local parts of a distributed table, so that the IDF is consistent (and precise) over a locally sharded table.

0 or 1 (0 by default). low_priority=1 runs the query with low priority in terms of Linux CPU scheduling. Consider also option threads=1 instead, or use that together with low_priority=1, as it might be better in some use cases.

Integer. Per-query max matches value.

Maximum amount of matches that the server keeps in RAM for each table and can return to the client. Default is 1000.

Introduced in order to control and limit RAM usage, max_matches setting defines how much matches will be kept in RAM while searching each table. Every match found will still be processed; but only best N of them will be kept in memory and return to the client in the end. Assume that the table contains 2,000,000 matches for the query. You rarely (if ever) need to retrieve all of them. Rather, you need to scan all of them, but only choose “best” at most, say, 500 by some criteria (ie. sorted by relevance, or price, or anything else), and display those 500 matches to the end user in pages of 20 to 100 matches. And tracking only the best 500 matches is much more RAM and CPU efficient than keeping all 2,000,000 matches, sorting them, and then discarding everything but the first 20 needed to display the search results page. max_matches controls N in that "best N" amount.

This parameter noticeably affects per-query RAM and CPU usage. Values of 1,000 to 10,000 are generally fine, but higher limits must be used with care. Recklessly raising max_matches to 1,000,000 means that searchd will have to allocate and initialize 1-million-entry matches buffer for every query. That will obviously increase per-query RAM usage, and in some cases can also noticeably impact performance.

See also max_matches_increase_threshold, which can affect the behavior of the max_matches option.

Integer. Sets the threshold that max_matches can be increased to. Default is 16384.

Manticore may increase max_matches to improve groupby and/or aggregation accuracy when pseudo_sharding is enabled and if it detects that the number of unique values of groupby attribute is less than this threshold. Loss of accuracy may occur when pseudo sharding executes the query in several threads or RT table performs parallel searches in disk chunks.

If the number of unique values of groupby attribute is less than the threshold, max_matches will be set to this number. Otherwise, default max_matches will be used.

If max_matches was set explicitly in query options, this threshold has no effect.

Note that if this threshold is set too high, the result will be increased memory consumption and general performance degradation.

You can also force guaranteed groupby/aggregate accuracy mode using accurate_aggregation option.

Sets maximum search query time, in milliseconds. Must be a non-negative integer. Default value is 0 which means "do not limit". Local search queries will be stopped once that much time has elapsed. Note that if you're performing a search which queries several local tables, this limit applies to each table separately. Note it may increase the query's response time a little bit, the overhead is caused by constant tracking if it's time to stop the query.

Integer. Max predicted search time, see predicted_time_costs.

none allows to replace all query terms with their exact forms if table was built with index_exact_words enabled. Useful to prevent stemming or lemmatizing query terms.

MySQL [(none)]> select * from tbl where match('-donald');
ERROR 1064 (42000): index t: query error: query is non-computable (single NOT operator)
MySQL [(none)]> select * from t where match('-donald') option not_terms_only_allowed=1;
+---------------------+-----------+
| id                  | field     |
+---------------------+-----------+
| 1658178727135150081 | smth else |
+---------------------+-----------+

Any of:

• proximity_bm25
• bm25
• none
• wordcount
• proximity
• matchany
• fieldmask
• sph04
• expr
• or export

Refer to Search results ranking for more details on each ranker.

Lets you specify a specific integer seed value for an ORDER BY RAND() query, for example: ... OPTION rand_seed=1234. By default, a new and different seed value is autogenerated for every query

Integer. Distributed retries count.

Integer. Distributed retry delay, msec.

• pq - priority queue, set by default
• kbuffer - gives faster sorting for already pre-sorted data, e.g. table data sorted by id The result set is in both cases the same; picking one option or the other may just improve (or worsen!) performance.

Limits max number of threads to use for current query processing. Default - no limit (the query can occupy all threads as defined globally). For batch of queries the option must be attached to the very first query in the batch, and it is then applied when working queue is created and then is effective for the whole batch. This option has same meaning as option max_threads_per_query, but applied only to the current query or batch of queries.

Quoted, colon-separated of library name:plugin name:optional string of settings. Query-time token filter gets created on search each time full-text invoked by every table involved and let you implement a custom tokenizer that makes tokens according to custom rules.

SELECT * FROM index WHERE MATCH ('yes@no') OPTION token_filter='mylib.so:blend:@'

SELECT * FROM students where age > 21 /*+ SecondaryIndex(age) */