When you run a query via SQL over the MySQL protocol, you receive the requested columns as a result or an empty result set if nothing is found.
- SQL
SELECT * FROM tbl;
+------+------+--------+
| id | age | name |
+------+------+--------+
| 1 | 25 | joe |
| 2 | 25 | mary |
| 3 | 33 | albert |
+------+------+--------+
3 rows in set (0.00 sec)
Additionally, you can use the SHOW META call to see extra meta-information about the latest query.
- SQL
SELECT id,story_author,comment_author FROM hn_small WHERE story_author='joe' LIMIT 3; SHOW META;
++--------+--------------+----------------+
| id | story_author | comment_author |
+--------+--------------+----------------+
| 152841 | joe | SwellJoe |
| 161323 | joe | samb |
| 163735 | joe | jsjenkins168 |
+--------+--------------+----------------+
3 rows in set (0.01 sec)
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| total | 3 |
| total_found | 20 |
| total_relation | gte |
| time | 0.010 |
+----------------+-------+
4 rows in set (0.00 sec)
In some cases, such as when performing a faceted search, you may receive multiple result sets as a response to your SQL query.
- SQL
SELECT * FROM tbl WHERE MATCH('joe') FACET age;
+------+------+
| id | age |
+------+------+
| 1 | 25 |
+------+------+
1 row in set (0.00 sec)
+------+----------+
| age | count(*) |
+------+----------+
| 25 | 1 |
+------+----------+
1 row in set (0.00 sec)
In case of a warning, the result set will include a warning flag, and you can see the warning using SHOW WARNINGS.
- SQL
SELECT * from tbl where match('"joe"/3'); show warnings;
+------+------+------+
| id | age | name |
+------+------+------+
| 1 | 25 | joe |
+------+------+------+
1 row in set, 1 warning (0.00 sec)
+---------+------+--------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------+
| warning | 1000 | quorum threshold too high (words=1, thresh=3); replacing quorum operator with AND operator |
+---------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
If your query fails, you will receive an error:
- SQL
SELECT * from tbl where match('@surname joe');
ERROR 1064 (42000): index idx: query error: no field 'surname' found in schema
Via the HTTP JSON interface, the query result is sent as a JSON document. Example:
{
"took":10,
"timed_out": false,
"hits":
{
"total": 2,
"hits":
[
{
"_id": "1",
"_score": 1,
"_source": { "gid": 11 }
},
{
"_id": "2",
"_score": 1,
"_source": { "gid": 12 }
}
]
}
}
took
: time in milliseconds it took to execute the searchtimed_out
: whether the query timed out or nothits
: search results, with the following properties:total
: total number of matching documentshits
: an array containing matches
The query result can also include query profile information. See Query profile.
Each match in the hits
array has the following properties:
_id
: match id_score
: match weight, calculated by the ranker_source
: an array containing the attributes of this match
By default, all attributes are returned in the _source
array. You can use the _source
property in the request payload to select the fields you want to include in the result set. Example:
{
"index":"test",
"_source":"attr*",
"query": { "match_all": {} }
}
You can specify the attributes you want to include in the query result as a string ("_source": "attr*"
) or as an array of strings ("_source": [ "attr1", "attri*" ]"
). Each entry can be an attribute name or a wildcard (*
, %
and ?
symbols are supported).
You can also explicitly specify which attributes you want to include and which to exclude from the result set using the includes
and excludes
properties:
"_source":
{
"includes": [ "attr1", "attri*" ],
"excludes": [ "*desc*" ]
}
An empty list of includes is interpreted as "include all attributes," while an empty list of excludes does not match anything. If an attribute matches both the includes and excludes, then the excludes win.
WHERE
is an SQL clause that works for both full-text 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 a full-text query.
The {col_name | expr_alias} [NOT] IN @uservar
condition syntax is supported. Refer to the SET syntax for a description of global user variables.
If you prefer the HTTP JSON interface, you can also apply filtering. It might seem more complex than SQL, but it is recommended for cases when you need to prepare a query programmatically, such as when a user fills out a form in your application.
Here's an example of several filters in a bool
query.
This full-text query matches all documents containing product
in any field. These documents must have a price greater than or equal to 500 (gte
) and less than or equal to 1000 (lte
). All of these documents must not have a revision less than 15 (lt
).
- JSON
POST /search
{
"index": "test1",
"query": {
"bool": {
"must": [
{ "match" : { "_all" : "product" } },
{ "range": { "price": { "gte": 500, "lte": 1000 } } }
],
"must_not": {
"range": { "revision": { "lt": 15 } }
}
}
}
}
The bool
query matches documents based on boolean combinations of other queries and/or filters. Queries and filters must be specified in must
, should
, or must_not
sections and can be nested.
- JSON
POST /search
{
"index":"test1",
"query": {
"bool": {
"must": [
{ "match": {"_all":"keyword"} },
{ "range": { "revision": { "gte": 14 } } }
]
}
}
}
Queries and filters specified in the must
section are required to match the documents. If multiple fulltext queries or filters are specified, all of them must match. This is the equivalent of AND
queries in SQL. Note that if you want to match against an array (multi-value attribute), you can specify the attribute multiple times. The result will be positive only if all the queried values are found in the array, e.g.:
"must": [
{"equals" : { "product_codes": 5 }},
{"equals" : { "product_codes": 6 }}
]
Note also, it may be better in terms of performance to use:
{"in" : { "all(product_codes)": [5,6] }}
(see details below).
Queries and filters specified in the should
section should match the documents. If some queries are specified in must
or must_not
, should
queries are ignored. On the other hand, if there are no queries other than should
, then at least one of these queries must match a document for it to match the bool query. This is the equivalent of OR
queries. Note, if you want to match against an array (multi-value attribute) you can specify the attribute multiple times, e.g.:
"should": [
{"equals" : { "product_codes": 7 }},
{"equals" : { "product_codes": 8 }}
]
Note also, it may be better in terms of performance to use:
{"in" : { "any(product_codes)": [7,8] }}
(see details below).
Queries and filters specified in the must_not
section must not match the documents. If several queries are specified under must_not
, the document matches if none of them match.
- JSON
POST /search
{
"index":"t",
"query": {
"bool": {
"should": [
{
"equals": {
"b": 1
}
},
{
"equals": {
"b": 3
}
}
],
"must": [
{
"equals": {
"a": 1
}
}
],
"must_not": {
"equals": {
"b": 2
}
}
}
}
}
A bool query can be nested inside another bool so you can make more complex queries. To make a nested boolean query just use another bool
instead of must
, should
or must_not
. Here is how this query:
a = 2 and (a = 10 or b = 0)
should be presented in JSON.
- JSON
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
}
}
]
}
}
]
}
}
}
More complex query:
(a = 1 and b = 1) or (a = 10 and b = 2) or (b = 0)
- JSON
(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
}
}
]
}
}
]
}
}
}
Queries in SQL format (query_string
) can also be used in bool queries.
- JSON
POST /search
{
"index": "test1",
"query": {
"bool": {
"must": [
{ "query_string" : "product" },
{ "query_string" : "good" }
]
}
}
}
Equality filters are the simplest filters that work with integer, float and string attributes.
- JSON
POST /search
{
"index":"test1",
"query": {
"equals": { "price": 500 }
}
}
Filter equals
can be applied to a multi-value attribute and you can use:
any()
which will be positive if the attribute has at least one value which equals to the queried value;all()
which will be positive if the attribute has a single value and it equals to the queried value
- JSON
POST /search
{
"index":"test1",
"query": {
"equals": { "any(price)": 100 }
}
}
Set filters check if attribute value is equal to any of the values in the specified set.
Set filters support integer, string and multi-value attributes.
- JSON
POST /search
{
"index":"test1",
"query": {
"in": {
"price": [1,10,100]
}
}
}
When applied to a multi-value attribute you can use:
any()
(equivalent to no function) which will be positive if there's at least one match between the attribute values and the queried values;all()
which will be positive if all the attribute values are in the queried set
- JSON
POST /search
{
"index":"test1",
"query": {
"in": {
"all(price)": [1,10]
}
}
}
Range filters match documents that have attribute values within a specified range.
Range filters support the following properties:
gte
: greater than or equal togt
: greater thanlte
: less than or equal tolt
: less than
- JSON
POST /search
{
"index":"test1",
"query": {
"range": {
"price": {
"gte": 500,
"lte": 1000
}
}
}
}
geo_distance
filters are used to filter the documents that are within a specific distance from a geo location.
Specifies the pin location, in degrees. Distances are calculated from this point.
Specifies the attributes that contain latitude and longitude.
Specifies distance calculation function. Can be either adaptive or haversine. adaptive is faster and more precise, for more details see GEODIST()
. Optional, defaults to adaptive.
Specifies the maximum distance from the pin locations. All documents within this distance match. The distance can be specified in various units. If no unit is specified, the distance is assumed to be in meters. Here is a list of supported distance units:
- Meter:
m
ormeters
- Kilometer:
km
orkilometers
- Centimeter:
cm
orcentimeters
- Millimeter:
mm
ormillimeters
- Mile:
mi
ormiles
- Yard:
yd
oryards
- Feet:
ft
orfeet
- Inch:
in
orinch
- Nautical mile:
NM
,nmi
ornauticalmiles
location_anchor
and location_source
properties accept the following latitude/longitude formats:
- an object with lat and lon keys:
{ "lat": "attr_lat", "lon": "attr_lon" }
- a string of the following structure:
"attr_lat, attr_lon"
- an array with the latitude and longitude in the following order:
[attr_lon, attr_lat]
Latitude and longitude are specified in degrees.
- Basic example
- Advanced example
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"
}
}
}
Manticore enables the use of arbitrary arithmetic expressions through both SQL and HTTP, incorporating attribute values, internal attributes (document ID and relevance weight), arithmetic operations, several built-in functions, and user-defined functions. Below is the complete reference list for quick access.
+, -, *, /, %, DIV, MOD
Standard arithmetic operators are available. Arithmetic calculations involving these operators can be executed in three different modes:
- using single-precision, 32-bit IEEE 754 floating point values (default),
- using signed 32-bit integers,
- using 64-bit signed integers.
The expression parser automatically switches to integer mode if no operations result in a floating point value. Otherwise, it uses the default floating point mode. For example, 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, as these operations return a non-integer result. To avoid this, you can use IDIV(a,b)
or a DIV b
form. Additionally, a*b
will not automatically promote to 64-bit when arguments are 32-bit. To enforce 64-bit results, use BIGINT(), but note that if non-integer operations are present, BIGINT() will simply be ignored.
<, > <=, >=, =, <>
The comparison operators return 1.0 when the condition is true and 0.0 otherwise. For example, (a=b)+3
evaluates to 4 when attribute a
is equal to attribute b
, and to 3 when a
is not. Unlike MySQL, the equality comparisons (i.e., =
and <>
operators) include a small equality threshold (1e-6 by default). If the difference between the compared values is within the threshold, they are considered equal.
The BETWEEN
and IN
operators, in the case of multi-value attributes, return true if at least one value matches the condition (similar to ANY()). The IN
operator does not support JSON attributes. The IS (NOT) NULL
operator is supported only for JSON attributes.
AND, OR, NOT
Boolean operators (AND, OR, NOT) behave as expected. They are left-associative and have the lowest priority compared to other operators. NOT has higher priority than AND and OR but still less than any other operator. AND and OR share the same priority, so using parentheses is recommended to avoid confusion in complex expressions.
&, |
These operators perform bitwise AND and OR respectively. The operands must be of integer types.
- ABS()
- ALL()
- ANY()
- ATAN2()
- BIGINT()
- BITDOT()
- BM25F()
- CEIL()
- CONCAT()
- CONTAINS()
- COS()
- CRC32()
- DATE_HISTOGRAM()
- DATE_RANGE()
- DAY()
- DOUBLE()
- EXP()
- FIBONACCI()
- FLOOR()
- GEODIST()
- GEOPOLY2D()
- GREATEST()
- HOUR()
- HISTOGRAM()
- 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()
- RANGE()
- REGEX()
- REMAP()
- SECOND()
- SIN()
- SINT()
- SQRT()
- SUBSTRING_INDEX()
- TO_STRING()
- UINT()
- YEAR()
- YEARMONTH()
- YEARMONTHDAY()
- WEIGHT()
In the 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)"
}
}
}
}
In this example, two expressions are created: add_all
and title_len
. The first expression calculates ( gid * 10 ) | crc32(title)
and stores the result in the add_all
attribute. The second expression calculates crc32(title)
and stores the result in the title_len
attribute.
Currently, only inline
expressions are supported. The value of the inline
property (the expression to compute) has the same syntax as SQL expressions.
The expression name can be utilized in filtering or sorting.
- script_fields
{
"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"
}
}
]
}
}
}
By default, expression values are included in the _source
array of the result set. If the source is selective (see Source selection), the expression name can be added to the _source
parameter in the request. Note, the names of the expressions must be in lowercase.
expressions
is an alternative to script_fields
with a simpler syntax. The example request adds two expressions and stores the results into add_all
and title_len
attributes. Note, the names of the expressions must be in lowercase.
- expressions
{
"index": "test",
"query": { "match_all": {} },
"expressions":
{
"add_all": "( gid * 10 ) | crc32(title)",
"title_len": "crc32(title)"
}
}