Manticore Search Manual: Searching

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

JSON

📋

⚙

POST /search
{
  "table": "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

JSON

📋

⚙

POST /search
{
  "table":"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

JSON

📋

⚙

POST /search
{
  "table":"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

JSON

📋

⚙

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

POST /search
{
  "table":"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

JSON

📋

⚙

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

POST /search
{
  "table":"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

JSON

📋

⚙

POST /search
{
  "table": "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

JSON

📋

⚙

POST /search
{
  "table":"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

JSON

📋

⚙

POST /search
{
  "table":"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

JSON

📋

⚙

POST /search
{
  "table":"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

JSON

📋

⚙

POST /search
{
  "table":"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 to
gt: greater than
lte: less than or equal to
lt: less than

‹›

JSON

JSON

📋

⚙

POST /search
{
  "table":"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 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

📋

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

WARNING: This functionality is in beta stage. Use it with caution.

Table joins in Manticore Search enable you to combine documents from two tables by matching related columns. This functionality allows for more complex queries and enhanced data retrieval across multiple tables.

SELECT
    select_expr [, select_expr] ...
    FROM tbl_name
    {INNER | LEFT} JOIN tbl2_name
    ON join_condition
    [...other select options]

join_condition: {
    left_table.attr = right_table.attr
    | left_table.json_attr.string_id = string(right_table.json_attr.string_id)
    | left_table.json_attr.int_id = int(right_table.json_attr.int_id)
}

For more information on select options, refer to the SELECT section.

When joining by a value from a JSON attribute, you need to explicitly specify the value's type using the int() or string() function.

‹›

String JSON attribute
Int JSON attribute

📋

SELECT ... ON left_table.json_attr.string_id = string(right_table.json_attr.string_id)

POST /search
{
  "table": "table_name",
  "query": {
    <optional full-text query against the left table>
  },
  "join": [
    {
      "type": "inner" | "left",
      "table": "joined_table_name",
      "query": {
        <optional full-text query against the right table>
      },
      "on": [
        {
          "left": {
            "table": "left_table_name",
            "field": "field_name",
            "type": "<common field's type when joining using json attributes>"
          },
          "operator": "eq",
          "right": {
            "table": "right_table_name",
            "field": "field_name"
          }
        }
      ]
    }
  ],
  "options": {
    ...
  }
}

on.type: {
    int
    | string
}

Note, there is the type field in the left operand section which you should use when joining two tables using json attributes. The allowed values are string and int.

Manticore Search supports two types of joins:

INNER JOIN: Returns only the rows where there is a match in both tables. For example, the query performs an INNER JOIN between the orders and customers tables, including only the orders that have matching customers.

‹›

SQL
JSON

📋

SELECT product, customers.email, customers.name, customers.address
FROM orders
INNER JOIN customers
ON customers.id = orders.customer_id
WHERE MATCH('maple', customers)
ORDER BY customers.email ASC;

‹›

Response

+---------+-------------------+----------------+-------------------+
| product | customers.email   | customers.name | customers.address |
+---------+-------------------+----------------+-------------------+
| Laptop  | [email protected] | Alice Johnson  | 123 Maple St      |
| Tablet  | [email protected] | Alice Johnson  | 123 Maple St      |
+---------+-------------------+----------------+-------------------+
2 rows in set (0.00 sec)

LEFT JOIN: Returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for the right table's columns. For example, this query retrieves all customers along with their corresponding orders using a LEFT JOIN. If no corresponding order exists, NULL values will appear. The results are sorted by the customer's email, and only the customer's name and the order quantity are selected.

‹›

SQL
JSON

📋

SELECT
name, orders.quantity
FROM customers
LEFT JOIN orders
ON orders.customer_id = customers.id
ORDER BY email ASC;

‹›

Response

+---------------+-----------------+-------------------+
| name          | orders.quantity | @int_attr_email   |
+---------------+-----------------+-------------------+
| Alice Johnson |               1 | [email protected] |
| Alice Johnson |               1 | [email protected] |
| Bob Smith     |               2 | [email protected]   |
| Carol White   |               1 | [email protected] |
| John Smith    |            NULL | [email protected]  |
+---------------+-----------------+-------------------+
5 rows in set (0.00 sec)

{
  "took": 0,
  "timed_out": false,
  "hits": {
    "total": 5,
    "total_relation": "eq",
    "hits": [
      {
        "_id": 1,
        "_score": 1,
        "_source": {
          "name": "Alice Johnson",
          "address": "123 Maple St",
          "email": "[email protected]",
          "orders.id": 3,
          "orders.customer_id": 1,
          "orders.quantity": 1,
          "orders.order_date": "2023-01-03",
          "orders.tags": [
            101,
            104
          ],
          "orders.details": {
            "price": 450,
            "warranty": "1 year"
          },
          "orders.product": "Tablet"
        }
      },
      {
        "_id": 1,
        "_score": 1,
        "_source": {
          "name": "Alice Johnson",
          "address": "123 Maple St",
          "email": "[email protected]",
          "orders.id": 1,
          "orders.customer_id": 1,
          "orders.quantity": 1,
          "orders.order_date": "2023-01-01",
          "orders.tags": [
            101,
            102
          ],
          "orders.details": {
            "price": 1200,
            "warranty": "2 years"
          },
          "orders.product": "Laptop"
        }
      },
      {
        "_id": 2,
        "_score": 1,
        "_source": {
          "name": "Bob Smith",
          "address": "456 Oak St",
          "email": "[email protected]",
          "orders.id": 2,
          "orders.customer_id": 2,
          "orders.quantity": 2,
          "orders.order_date": "2023-01-02",
          "orders.tags": [
            103
          ],
          "orders.details": {
            "price": 800,
            "warranty": "1 year"
          },
          "orders.product": "Phone"
        }
      },
      {
        "_id": 3,
        "_score": 1,
        "_source": {
          "name": "Carol White",
          "address": "789 Pine St",
          "email": "[email protected]",
          "orders.id": 4,
          "orders.customer_id": 3,
          "orders.quantity": 1,
          "orders.order_date": "2023-01-04",
          "orders.tags": [
            105
          ],
          "orders.details": {
            "price": 300,
            "warranty": "1 year"
          },
          "orders.product": "Monitor"
        }
      },
      {
        "_id": 4,
        "_score": 1,
        "_source": {
          "name": "John Smith",
          "address": "15 Barclays St",
          "email": "[email protected]",
          "orders.id": 0,
          "orders.customer_id": 0,
          "orders.quantity": 0,
          "orders.order_date": "",
          "orders.tags": [],
          "orders.details": null,
          "orders.product": ""
        }
      }
    ]
  }
}

This query retrieves products, customer names, product prices, and product tags from the orders and customers tables. It performs a LEFT JOIN, ensuring all customers are included even if they have not made an order. The query filters the results to include only orders with a price greater than 500 and matches the products to the terms 'laptop', 'phone', or 'monitor'. The results are ordered by the id of the orders in ascending order. Additionally, the query facets the results based on the warranty details from the JSON attributes of the joined orders table.

‹›

SQL
JSON

📋

SELECT orders.product, name, orders.details.price, orders.tags
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
WHERE orders.details.price > 500
AND MATCH('laptop|phone|monitor', orders)
ORDER BY orders.id ASC
FACET orders.details.warranty;

‹›

Response

+----------------+---------------+----------------------+-------------+
| orders.product | name          | orders.details.price | orders.tags |
+----------------+---------------+----------------------+-------------+
| Laptop         | Alice Johnson |                 1200 | 101,102     |
| Phone          | Bob Smith     |                  800 | 103         |
+----------------+---------------+----------------------+-------------+
2 rows in set (0.01 sec)
--- 2 out of 2 results in 0ms ---

+-------------------------+----------+
| orders.details.warranty | count(*) |
+-------------------------+----------+
| 2 years                 |        1 |
| 1 year                  |        1 |
+-------------------------+----------+
2 rows in set (0.01 sec)
--- 2 out of 2 results in 0ms ---

When using JOINs in Manticore Search, keep the following points in mind:

Field selection: When selecting fields from two tables in a JOIN, do not prefix fields from the left table, but do prefix fields from the right table. For example:
```
SELECT field_name, right_table.field_name FROM ...
```
JOIN conditions: Always explicitly specify the table names in your JOIN conditions:
```
JOIN ON table_name.some_field = another_table_name.some_field
```
Expressions with JOINs: When using expressions that combine fields from both joined tables, alias the result of the expression:
```
SELECT *, (nums2.n + 3) AS x, x * n FROM nums LEFT JOIN nums2 ON nums2.id = nums.num2_id
```
Filtering on aliased expressions: You cannot use aliases for expressions involving fields from both tables in the WHERE clause.
Using ANY with MVA: When using the ANY() function with multi-valued attributes in JOINs, alias the multi-valued attribute from the joined table:
```
SELECT *, t2.m AS alias
FROM t
LEFT JOIN t2 ON t.id = t2.t_id
WHERE ANY(alias) IN (3, 5)
```

By following these guidelines, you can effectively use JOINs in Manticore Search to combine data from multiple indexes and perform complex queries.

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.

In the HTTP JSON interface, expressions are supported via script_fields and expressions.

{
    "table": "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

script_fields

📋

{
    "table":"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

expressions

📋

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

Filters

WHERE

HTTP JSON

bool query

must

should

must_not

Nested bool query

Queries in SQL format

Various filters

Equality filters

Set filters

Range filters

Geo distance filters

location_anchor

location_source

distance_type

distance

Joining tables

General syntax

SQL

JSON

Types of Joins

Complex Join with Faceting

Caveats and Best Practices

Expressions in search

Arithmetic operators

Comparison operators

Boolean operators

Bitwise operators

Functions:

Expressions in HTTP JSON

script_fields

expressions