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:

• 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()

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.

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