Search results

SQL

When you run a query via SQL over mysql protocol as a result you get the requested columns back or empty result set in case nothing is found.

SQL
📋
select * from idx;
Response
+------+------+--------+
| id   | age  | name   |
+------+------+--------+
|    1 |   25 | joe    |
|    2 |   25 | mary   |
|    3 |   33 | albert |
+------+------+--------+
3 rows in set (0.00 sec)

In addition to that you can use SHOW META call to see additional meta-information about the latest query.

SQL
📋
select * from idx where match('joe'); show meta;
Response
+------+------+------+
| id   | age  | name |
+------+------+------+
|    1 |   25 | joe  |
+------+------+------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 1     |
| total_found   | 1     |
| time          | 0.000 |
| keyword[0]    | joe   |
| docs[0]       | 1     |
| hits[0]       | 1     |
+---------------+-------+
6 rows in set (0.00 sec)

In some cases, e.g. when you do faceted search you can get multiple result sets as a response to your SQL query.

SQL
📋
select * from idx where match('joe') facet age;
Response
+------+------+
| 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 idx where match('"joe"/3'); show warnings;
Response
+------+------+------+
| 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 get an error:

SQL
📋
select * from idx where match('@surname joe');
Response
ERROR 1064 (42000): index idx: query error: no field 'surname' found in schema

HTTP

Via HTTP JSON iterface 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 search
  • timed_out: if the query timed out or not
  • hits: search results. has the following properties:
  • total: total number of matching documents
  • hits: an array containing matches

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 ranker
  • _source: an array containing the attributes of this match

Source selection

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 be included in the result set. Example:

{
  "index":"test",
  "_source":"attr*",
  "query": { "match_all": {} }
}

You can specify the attributes which 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.

Filters

WHERE

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

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.

HTTP

JSON queries have two distinct entities: fulltext queries and filters. Both can be organised in a tree (using a bool query), but for now filters work only for the root element of the query. For example:

{
  "index":"test",
  "query": { "range": { "price": { "lte": 11 } } }
}

Here's an example of several filters in a bool query:

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

This is a fulltext query that matches all the documents containing product in any field. These documents must have a price greater or equal than 500 (gte) and less or equal than 1000 (lte). All of these documents must not have a revision less than 15 (lt).

bool query

A bool query matches documents matching boolean combinations of other queries and/or filters. Queries and filters must be specified in "must", "should" or "must_not" sections. Example:

{
  "index":"test",
  "query":
  {
    "bool":
    {
      "must":
      [
        { "match": {"_all":"keyword"} },
        { "range": { "int_col": { "gte": 14 } } }
      ]
    }
  }
}

must

Queries and filters specified in the "must" section must match the documents. If several fulltext queries or filters are specified, all of them. This is the equivalent of AND queries in SQL.

should

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.

must_not

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.

Example:

{
  "index": "test1",
  "query":
  {
    "bool":
    {
      "must":
      {
        "match" : { "_all" : "product" }
      },
      "must_not":
      [
        { "match": {"_all":"phone"} },
        { "range": { "price": { "gte": 500 } } }
      ]
    }
  }
}

Queries in SQL format

Queries in SQL format (query_string) can also be used in bool queries. Example:

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

Equality filters

Equality filters are the simplest filters that work with integer, float and string attributes. Example:

{
  "index":"test1",
  "query":
  {
    "equals": { "price": 500 }
  }
}

Set filters

Set filters check if attribute value is equal to any of the values in the specified set. Example:

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

Set filters support integer, string and multi-value attributes.

Range filters

Range filters match documents that have attribute values within a specified range. Example:

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

Range filters support the following properties:

gte

Value must be greater than or equal to

gt

value must be greater than

lte

value must be less than or equal to

lt

value must be less

Geo distance filters

geo_distance filters are used to filter the documents that are within a specific distance from a geo location.

Example:

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

Specifies the pin location, in degrees. Distances are calculated from this point.

location_source

Specifies the attributes that contain latitude and longitude.

distance_type

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.

distance

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 or meters
  • Kilometer: km or kilometers
  • Centimeter: cm or centimeters
  • Millimeter: mm or millimeters
  • Mile: mi or miles
  • Yard: yd or yards
  • Feet: ft or feet
  • Inch: in or inch
  • Nautical mile: NM, nmi or nauticalmiles

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.

geo_distance can be used as a filter in bool queries along with matches or other attribute filters:

{
  "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"
          }
        }
      ]
    }
  }
}

Expressions in search

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.

Arithmetic operators

+, -, *, /, %, 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

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

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.

Boolean operators

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.

Bitwise operators

&, |

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

Functions:

Expressions in HTTP JSON

In HTTP JSON interface expressions are supported via script_fields and expressions

script_fields

{
    "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. First expression calculates ( gid * 10 ) | crc32(title) and stores the result in the add_all attribute. Second expression calculates crc32(title) and stores the result in the title_len attribute.

Only inline expressions are supported for now. The value of inline property (the expression to compute) has the same syntax as SQL expressions.

The expression name can be used 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"
                    }
                }
            ]
        }
    }
}

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.

expressions

expressions is an alternative to script_fields with a simpler syntax. Example request adds two expressions and stores the results into add_all and title_len attributes.

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