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.

SELECT * FROM tbl;

+------+------+--------+
| id   | age  | name   |
+------+------+--------+
|    1 |   25 | joe    |
|    2 |   25 | mary   |
|    3 |   33 | albert |
+------+------+--------+
3 rows in set (0.00 sec)

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)

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)

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)

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

ERROR 1064 (42000): index idx: query error: no field 'surname' found in schema

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

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.

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)"
  }
}