Boolean optimization

Queries may be automatically optimized if OPTION boolean_simplify=1 is specified. Some transformations performed by this optimization include:

  • Excess brackets: ((A | B) | C) becomes ( A | B | C ); ((A B) C) becomes ( A B C )
  • Excess AND NOT: ((A !N1) !N2) becomes (A !(N1 | N2))
  • Common NOT: ((A !N) | (B !N)) becomes ((A|B) !N)
  • Common Compound NOT: ((A !(N AA)) | (B !(N BB))) becomes (((A|B) !N) | (A !AA) | (B !BB)) if the cost of evaluating N is greater than the added together costs of evaluating A and B
  • Common subterm: ((A (N | AA)) | (B (N | BB))) becomes (((A|B) N) | (A AA) | (B BB)) if the cost of evaluating N is greater than the added together costs of evaluating A and B
  • Common keywords: (A | “A B”~N) becomes A; (“A B” | “A B C”) becomes “A B”; (“A B”~N | “A B C”~N) becomes (“A B”~N)
  • Common phrase: (“X A B” | “Y A B”) becomes (“(“X”|”Y”) A B”)
  • Common AND NOT: ((A !X) | (A !Y) | (A !Z)) becomes (A !(X Y Z))
  • Common OR NOT: ((A !(N | N1)) | (B !(N | N2))) becomes (( (A !N1) | (B !N2) ) !N) Note that optimizing the queries consumes CPU time, so for simple queries -or for hand-optimized queries- you’ll do better with the default boolean_simplify=0 value. Simplifications are often better for complex queries, or algorithmically generated queries.

Queries like “-dog”, which implicitly include all documents from the collection, can not be evaluated. This is both for technical and performance reasons. Technically, Manticore does not always keep a list of all IDs. Performance-wise, when the collection is huge (ie. 10-100M documents), evaluating such queries could take very long.

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

SQL

  1. SELECT * FROM tbl;

Response

  1. +------+------+--------+
  2. | id   | age  | name   |
  3. +------+------+--------+
  4. |    1 |   25 | joe    |
  5. |    2 |   25 | mary   |
  6. |    3 |   33 | albert |
  7. +------+------+--------+
  8. 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

SQL

  1. SELECT id,story_author,comment_author FROM hn_small WHERE story_author='joe' LIMIT 3; SHOW META;

Response

  1. ++--------+--------------+----------------+
  2. | id     | story_author | comment_author |
  3. +--------+--------------+----------------+
  4. | 152841 | joe          | SwellJoe       |
  5. | 161323 | joe          | samb           |
  6. | 163735 | joe          | jsjenkins168   |
  7. +--------+--------------+----------------+
  8. 3 rows in set (0.01 sec)
  10. +----------------+-------+
  11. | Variable_name  | Value |
  12. +----------------+-------+
  13. | total          | 3     |
  14. | total_found    | 20    |
  15. | total_relation | gte   |
  16. | time           | 0.010 |
  17. +----------------+-------+
  18. 4 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

SQL

  1. SELECT * FROM tbl WHERE MATCH('joe') FACET age;

Response

  1. +------+------+
  2. | id   | age  |
  3. +------+------+
  4. |    1 |   25 |
  5. +------+------+
  6. 1 row in set (0.00 sec)
  8. +------+----------+
  9. | age  | count(*) |
  10. +------+----------+
  11. |   25 |        1 |
  12. +------+----------+
  13. 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

SQL

  1. SELECT * from tbl where match('"joe"/3'); show warnings;

Response

  1. +------+------+------+
  2. | id   | age  | name |
  3. +------+------+------+
  4. |    1 |   25 | joe  |
  5. +------+------+------+
  6. 1 row in set, 1 warning (0.00 sec)
  8. +---------+------+--------------------------------------------------------------------------------------------+
  9. | Level   | Code | Message                                                                                    |
  10. +---------+------+--------------------------------------------------------------------------------------------+
  11. | warning | 1000 | quorum threshold too high (words=1, thresh=3); replacing quorum operator with AND operator |
  12. +---------+------+--------------------------------------------------------------------------------------------+
  13. 1 row in set (0.00 sec)

If your query fails you will get an error:

  • SQL

SQL

  1. SELECT * from tbl where match('@surname joe');

Response

  1. 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:

  1. {
  2.   "took":10,
  3.   "timed_out": false,
  4.   "hits":
  5.   {
  6.     "total": 2,
  7.     "hits":
  8.     [
  9.       {
  10.         "_id": "1",
  11.         "_score": 1,
  12.         "_source": { "gid": 11 }
  13.       },
  14.       {
  15.         "_id": "2",
  16.         "_score": 1,
  17.         "_source": { "gid": 12 }
  18.       }
  19.     ]
  20.   }
  21. }
  • 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:

  1. {
  2.   "index":"test",
  3.   "_source":"attr*",
  4.   "query": { "match_all": {} }
  5. }

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:

  1. "_source":
  2. {
  3.   "includes": [ "attr1", "attri*" ],
  4.   "excludes": [ "*desc*" ]
  5. }

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

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.

Here’s an example of several filters in a bool query.

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).

  • JSON

JSON

  1. POST /search
  2. {
  3.   "index": "test1",
  4.   "query": {
  5.     "bool": {
  6.       "must": [
  7.         { "match" : { "_all" : "product" } },
  8.         { "range": { "price": { "gte": 500, "lte": 1000 } } }
  9.       ],
  10.       "must_not": {
  11.         "range": { "revision": { "lt": 15 } }
  12.       }
  13.     }
  14.   }
  15. }

bool query

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 and can be nested.

  • JSON

JSON

  1. POST /search
  2. {
  3.   "index":"test1",
  4.   "query": {
  5.     "bool": {
  6.       "must": [
  7.         { "match": {"_all":"keyword"} },
  8.         { "range": { "revision": { "gte": 14 } } }
  9.       ]
  10.     }
  11.   }
  12. }

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. Note, if you want to match against an array (multi-value attribute)) you can specify the attribute multiple times and if only all the queried values are found in the array the result will be positive, e.g.:

  1. "must": [
  2.   {"equals" : { "product_codes": 5 }},
  3.   {"equals" : { "product_codes": 6 }}
  4. ]

Note also, it may be better in terms of performance to use:

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

(see details below).

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. Note, if you want to match against an array (multi-value attribute)) you can specify the attribute multiple times, e.g.:

  1. "should": [
  2.   {"equals" : { "product_codes": 7 }},
  3.   {"equals" : { "product_codes": 8 }}
  4. ]

Note also, it may be better in terms of performance to use:

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

(see details below).

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.

  • JSON

JSON

  1. POST /search
  2. {
  3.   "index":"t",
  4.   "query": {
  5.     "bool": {
  6.       "should": [
  7.         {
  8.           "equals": {
  9.             "b": 1
  10.           }
  11.         },
  12.         {
  13.           "equals": {
  14.             "b": 3
  15.           }
  16.         }
  17.       ],
  18.       "must": [
  19.         {
  20.           "equals": {
  21.             "a": 1
  22.           }
  23.         }
  24.       ],
  25.       "must_not": {
  26.         "equals": {
  27.           "b": 2
  28.         }
  29.       }
  30.     }
  31.   }
  32. }

Nested bool query

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:

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

should be presented in JSON.

  • JSON

JSON

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

  1. POST /search
  2. {
  3.   "index":"t",
  4.   "query": {
  5.     "bool": {
  6.       "must": [
  7.         {
  8.           "equals": {
  9.             "a": 2
  10.           }
  11.         },
  12.         {
  13.           "bool": {
  14.             "should": [
  15.               {
  16.                 "equals": {
  17.                   "a": 10
  18.                 }
  19.               },
  20.               {
  21.                 "equals": {
  22.                   "b": 0
  23.                 }
  24.               }
  25.             ]
  26.           }
  27.         }
  28.       ]
  29.     }
  30.   }
  31. }

More complex query:

  1. (a = 1 and b = 1) or (a = 10 and b = 2) or (b = 0)
  • JSON

JSON

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

  1. POST /search
  2. {
  3.   "index":"t",
  4.   "query": {
  5.     "bool": {
  6.       "should": [
  7.         {
  8.           "bool": {
  9.             "must": [
  10.               {
  11.                 "equals": {
  12.                   "a": 1
  13.                 }
  14.               },
  15.               {
  16.                 "equals": {
  17.                   "b": 1
  18.                 }
  19.               }
  20.             ]
  21.           }
  22.         },
  23.         {
  24.           "bool": {
  25.             "must": [
  26.               {
  27.                 "equals": {
  28.                   "a": 10
  29.                 }
  30.               },
  31.               {
  32.                 "equals": {
  33.                   "b": 2
  34.                 }
  35.               }
  36.             ]
  37.           }
  38.         },
  39.         {
  40.           "bool": {
  41.             "must": [
  42.               {
  43.                 "equals": {
  44.                   "b": 0
  45.                 }
  46.               }
  47.             ]
  48.           }
  50.         }
  51.       ]
  52.     }
  53.   }
  54. }

Queries in SQL format

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

  • JSON

JSON

  1. POST /search
  2. {
  3.   "index": "test1",
  4.   "query": {
  5.     "bool": {
  6.       "must": [
  7.         { "query_string" : "product" },
  8.         { "query_string" : "good" }
  9.       ]
  10.     }
  11.   }
  12. }

Various filters

Equality filters

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

  • JSON

JSON

  1. POST /search
  2. {
  3.   "index":"test1",
  4.   "query": {
  5.     "equals": { "price": 500 }
  6.   }
  7. }

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

JSON

  1. POST /search
  2. {
  3.   "index":"test1",
  4.   "query": {
  5.     "equals": { "any(price)": 100 }
  6.   }
  7. }

Set filters

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

JSON

  1. POST /search
  2. {
  3.   "index":"test1",
  4.   "query": {
  5.     "in": {
  6.       "price": [1,10,100]
  7.     }
  8.   }
  9. }

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

JSON

  1. POST /search
  2. {
  3.   "index":"test1",
  4.   "query": {
  5.     "in": {
  6.       "all(price)": [1,10]
  7.     }
  8.   }
  9. }

Range filters

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

Range filters support the following properties:

  • gte: greater than or equal to
  • gt: greater than
  • lte: less than or equal to
  • lt: less than
  • JSON

JSON

  1. POST /search
  2. {
  3.   "index":"test1",
  4.   "query": {
  5.     "range": {
  6.       "price": {
  7.         "gte": 500,
  8.         "lte": 1000
  9.       }
  10.     }
  11.   }
  12. }

Geo distance filters

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

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.

  • Basic example
  • Advanced example

Basic example Advanced example

  1. POST /search
  2. {
  3.   "index":"test",
  4.   "query": {
  5.     "geo_distance": {
  6.       "location_anchor": {"lat":49, "lon":15},
  7.       "location_source": {"attr_lat, attr_lon"},
  8.       "distance_type": "adaptive",
  9.       "distance":"100 km"
  10.     }
  11.   }
  12. }

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

  1. POST /search
  2. {
  3.   "index": "geodemo",
  4.   "query": {
  5.     "bool": {
  6.       "must": [
  7.         {
  8.           "match": {
  9.             "*": "station"
  10.           }
  11.         },
  12.         {
  13.           "equals": {
  14.             "state_code": "ENG"
  15.           }
  16.         },
  17.         {
  18.           "geo_distance": {
  19.             "distance_type": "adaptive",
  20.             "location_anchor": {
  21.               "lat": 52.396,
  22.               "lon": -1.774
  23.             },
  24.             "location_source": "latitude_deg,longitude_deg",
  25.             "distance": "10000 m"
  26.           }
  27.         }
  28.       ]
  29.     }
  30.   }
  31. }