select *, ALL(x>0 AND x<4 FOR x IN j.ar) from tbl

select *, ALL(x>0 AND x<4 FOR x IN j.ar) cond from tbl where cond=1

+------+--------------+--------------------------------+
| id   | j            | all(x>0 and x<4 for x in j.ar) |
+------+--------------+--------------------------------+
|    1 | {"ar":[1,3]} |                              1 |
|    2 | {"ar":[3,7]} |                              0 |
+------+--------------+--------------------------------+
2 rows in set (0.00 sec)

+------+--------------+------+
| id   | j            | cond |
+------+--------------+------+
|    1 | {"ar":[1,3]} |    1 |
+------+--------------+------+
1 row in set (0.00 sec)

select * from tbl where all(m) >= 1

select * from tbl where all(m) in (1, 3, 7, 10)

+------+------+
| id   | m    |
+------+------+
|    1 | 1,3  |
|    2 | 3,7  |
+------+------+
2 rows in set (0.00 sec)

+------+------+
| id   | m    |
+------+------+
|    1 | 1,3  |
|    2 | 3,7  |
+------+------+
2 rows in set (0.00 sec)

select * from tbl where all(m) not in (2, 4)

+------+------+
| id   | m    |
+------+------+
|    1 | 1,3  |
|    2 | 3,7  |
+------+------+
2 rows in set (0.00 sec)

select * from tbl where tags all('bug', 'release')

mysql> select * from tbl

+------+---------------------------+
| id   | tags                      |
+------+---------------------------+
|    1 | bug priority_high release |
|    2 | bug priority_low release  |
+------+---------------------------+
2 rows in set (0.00 sec)

mysql> select * from tbl where tags not all('bug')

Empty set (0.00 sec)

+------+---------------------------+
| id   | tags                      |
+------+---------------------------+
|    1 | bug priority_high release |
|    2 | bug priority_low release  |
+------+---------------------------+
2 rows in set (0.00 sec)

select *, ANY(x>5 AND x<10 FOR x IN j.ar) from tbl

select *, ANY(x>5 AND x<10 FOR x IN j.ar) cond from tbl where cond=1

+------+--------------+---------------------------------+
| id   | j            | any(x>5 and x<10 for x in j.ar) |
+------+--------------+---------------------------------+
|    1 | {"ar":[1,3]} |                               0 |
|    2 | {"ar":[3,7]} |                               1 |
+------+--------------+---------------------------------+
2 rows in set (0.00 sec)

+------+--------------+------+
| id   | j            | cond |
+------+--------------+------+
|    2 | {"ar":[3,7]} |    1 |
+------+--------------+------+
1 row in set (0.00 sec)

mysql> select * from tbl

+------+------+
| id   | m    |
+------+------+
|    1 | 1,3  |
|    2 | 3,7  |
+------+------+
2 rows in set (0.01 sec)

mysql> select * from tbl where any(m) > 5

+------+------+
| id   | m    |
+------+------+
|    2 | 3,7  |
+------+------+
1 row in set (0.00 sec)

select * from tbl where any(m) in (1, 7, 10)

+------+------+
| id   | m    |
+------+------+
|    1 | 1,3  |
|    2 | 3,7  |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from tbl

+------+------+
| id   | m    |
+------+------+
|    1 | 1,3  |
|    2 | 3,7  |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from tbl where any(m) not in (1, 3, 5)

+------+------+
| id   | m    |
+------+------+
|    2 | 3,7  |
+------+------+
1 row in set (0.00 sec)

select * from tbl where tags any('bug', 'feature')

select * from tbl
--------------

+------+---------------------------+
| id   | tags                      |
+------+---------------------------+
|    1 | bug priority_high release |
|    2 | bug priority_low release  |
+------+---------------------------+
2 rows in set (0.00 sec)

--------------
select * from tbl where tags not any('feature', 'priority_low')
--------------

+------+---------------------------+
| id   | tags                      |
+------+---------------------------+
|    1 | bug priority_high release |
+------+---------------------------+
1 row in set (0.01 sec)

+------+---------------------------+
| id   | tags                      |
+------+---------------------------+
|    1 | bug priority_high release |
|    2 | bug priority_low release  |
+------+---------------------------+
2 rows in set (0.00 sec)

CONTAINS(polygon, x, y) checks whether the (x,y) point is within the given polygon, and returns 1 if true, or 0 if false. The polygon has to be specified using either the POLY2D() function. The former function is intended for "small" polygons, meaning less than 500 km (300 miles) a side, and it doesn't take into account the Earth's curvature for speed. For larger distances, you should use GEOPOLY2D, which tessellates the given polygon in smaller parts, accounting for the Earth's curvature.

The behavior of IF() is slightly different from its MySQL counterpart. It takes 3 arguments, checks whether the 1st argument is equal to 0.0, returns the 2nd argument if it is not zero, or the 3rd one when it is. Note that unlike comparison operators, IF() does not use a threshold! Therefore, it's safe to use comparison results as its 1st argument, but arithmetic operators might produce unexpected results. For instance, the following two calls will produce different results even though they are logically equivalent:

IF ( sqrt(3)*sqrt(3)-3<>0, a, b )
IF ( sqrt(3)*sqrt(3)-3, a, b )

In the first case, the comparison operator <> will return 0.0 (false) due to a threshold, and IF() will always return ** as a result. In the second case, the same sqrt(3)*sqrt(3)-3 expression will be compared with zero without a threshold by the IF() function itself. However, its value will be slightly different from zero due to limited floating-point calculation precision. Because of this, the comparison with 0.0 done by IF() will not pass, and the second variant will return 'a' as a result.

HISTOGRAM(expr, {hist_interval=size, hist_offset=value}) takes a bucket size and returns the bucket number for the value. The key function is:

key_of_the_bucket = interval + offset * floor ( ( value - offset ) / interval )

The histogram argument interval must be positive. The histogram argument offset must be positive and less than interval. It is used in aggregation, FACET, and grouping.

Example:

SELECT COUNT(*),
HISTOGRAM(price, {hist_interval=100}) as price_range
FROM facets
GROUP BY price_range ORDER BY price_range ASC;

IN(expr,val1,val2,...) takes 2 or more arguments and returns 1 if the 1st argument (expr) is equal to any of the other arguments (val1..valN), or 0 otherwise. Currently, all the checked values (but not the expression itself) are required to be constant. The constants are pre-sorted, and binary search is used, so IN() even against a large arbitrary list of constants will be very quick. The first argument can also be an MVA attribute. In that case, IN() will return 1 if any of the MVA values are equal to any of the other arguments. IN() also supports IN(expr,@uservar) syntax to check whether the value belongs to the list in the given global user variable. The first argument can be a JSON attribute.

INDEXOF(cond FOR var IN json.array) function iterates through all elements in the array and returns the index of the first element for which 'cond' is true, and -1 if 'cond' is false for every element in the array.

INTERVAL(expr,point1,point2,point3,...) takes 2 or more arguments and returns the index of the argument that is less than the first argument: it returns 0 if expr<point1, 1 if point1<=expr<point2, and so on. It is required that point1<point2<...<pointN for this function to work correctly.

LENGTH(attr_mva) function returns the number of elements in an MVA set. It works with both 32-bit and 64-bit MVA attributes. LENGTH(attr_json) returns the length of a field in JSON. The return value depends on the type of field. For example, LENGTH(json_attr.some_int) always returns 1, and LENGTH(json_attr.some_array) returns the number of elements in the array. LENGTH(string_expr) function returns the length of the string resulting from an expression. TO_STRING() must enclose the expression, regardless of whether the expression returns a non-string or it's simply a string attribute.

RANGE(expr, {range_from=value,range_to=value}) takes a set of ranges and returns the bucket number for the value. This expression includes the range_from value and excludes the range_to value for each range. A range can be open - having only the range_from or only the range_to value. It is used in aggregation, FACET, and grouping.

Example:

SELECT COUNT(*),
RANGE(price, {range_to=150},{range_from=150,range_to=300},{range_from=300}) price_range
FROM facets
GROUP BY price_range ORDER BY price_range ASC;

REMAP(condition, expression, (cond1, cond2, ...), (expr1, expr2, ...)) function allows you to make some exceptions to expression values depending on condition values. The condition expression should always result in an integer, while the expression can result in an integer or float.

Example:

SELECT id, size, REMAP(size, 15, (5,6,7,8), (1,1,2,2)) s
FROM products
ORDER BY s ASC;

This will put documents with sizes 5 and 6 first, followed by sizes 7 and 8. In case there's an original value not listed in the array (e.g. size 10), it will default to 15, and in this case, will be placed at the end.

More examples:

SELECT REMAP(userid, karmapoints, (1, 67), (999, 0)) FROM users;
SELECT REMAP(id%10, salary, (0), (0.0)) FROM employes;

Note, that CURTIME(), UTC_TIME(), UTC_TIMESTAMP(), and TIMEDIFF() can be promoted to numeric types using arbitrary conversion functions such as BIGINT(), DOUBLE(), etc.

select NOW();

+------------+
| NOW()      |
+------------+
| 1615788407 |
+------------+

select CURTIME();

+-----------+
| CURTIME() |
+-----------+
| 07:06:30  |
+-----------+

select curdate();

+------------+
| curdate()  |
+------------+
| 2023-08-02 |
+------------+

select UTC_TIME();

+------------+
| UTC_TIME() |
+------------+
| 06:06:18   |
+------------+

select UTC_TIMESTAMP();

+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2021-03-15 06:06:03 |
+---------------------+

select second(now());

+---------------+
| second(now()) |
+---------------+
| 52            |
+---------------+

select minute(now());

+---------------+
| minute(now()) |
+---------------+
| 5             |
+---------------+

select hour(now());

+-------------+
| hour(now()) |
+-------------+
| 7           |
+-------------+

select day(now());

+------------+
| day(now()) |
+------------+
| 15         |
+------------+

select month(now());

+--------------+
| month(now()) |
+--------------+
| 3            |
+--------------+

select quarter(now());

+--------------+
| quarter(now()) |
+--------------+
| 2            |
+--------------+

select year(now());

+-------------+
| year(now()) |
+-------------+
| 2021        |
+-------------+

select dayname(now());

+----------------+
| dayname(now()) |
+----------------+
| Wednesday      |
+----------------+

select monthname(now());

+------------------+
| monthname(now()) |
+------------------+
| August           |
+------------------+

select dayofweek(now());

+------------------+
| dayofweek(now()) |
+------------------+
| 5                |
+------------------+

select dayofyear(now());

+------------------+
| dayofyear(now()) |
+------------------+
|              214 |
+------------------+

select yearweek(now());

+-----------------+
| yearweek(now()) |
+-----------------+
|         2023211 |
+-----------------+

select yearmonth(now());

+------------------+
| yearmonth(now()) |
+------------------+
| 202103           |
+------------------+

select yearmonthday(now());

+---------------------+
| yearmonthday(now()) |
+---------------------+
| 20210315            |
+---------------------+

select timediff(1615787586, 1613787583);

+----------------------------------+
| timediff(1615787586, 1613787583) |
+----------------------------------+
| 555:33:23                        |
+----------------------------------+

select datediff(1615787586, 1613787583);

+----------------------------------+
| datediff(1615787586, 1613787583) |
+----------------------------------+
|                               23 |
+----------------------------------+

select date(now());

+-------------+
| date(now()) |
+-------------+
| 2023-08-02  |
+-------------+

select time(now());

+-------------+
| time(now()) |
+-------------+
| 15:21:27    |
+-------------+

SELECT DATE_FORMAT(NOW(), 'year %Y and time %T');

+------------------------------------------+
| DATE_FORMAT(NOW(), 'year %Y and time %T') |
+------------------------------------------+
| year 2023 and time 11:54:52              |
+------------------------------------------+

This example formats the current date and time, displaying the four-digit year and the time in 24-hour format.

DATE_HISTOGRAM(expr, {calendar_interval='unit_name'}) Takes a bucket size as a unit name and returns the bucket number for the value. Values are rounded to the closest bucket. The key function is:

key_of_the_bucket = interval * floor ( value / interval )

Intervals are specified using the unit name, such as week or as a single unit like 1M. Multiple units such as 2w are not supported.

The valid intervals are:

• minute, 1m
• hour, 1h
• day, 1d
• week, 1w (a week is the interval between the start day of the week, hour, minute, second and the next week but the same day and time of the week)
• month, 1M
• year, 1y (a year is the interval between the start day of the month, time and the next year but the same day of the month, time)

Used in aggregation, FACET, and grouping.

Example:

SELECT COUNT(*),
DATE_HISTOGRAM(tm, {calendar_interval='month'}) AS months
FROM facets
GROUP BY months ORDER BY months ASC;

DATE_RANGE(expr, {range_from='date_math', range_to='date_math'}) takes a set of ranges and returns the bucket number for the value. The expression includes the range_from value and excludes the range_to value for each range. The range can be open - having only the range_from or only the range_to value. The difference between this and the RANGE() function is that the range_from and range_to values can be expressed in Date math expressions.

Used in aggregation, FACET, and grouping.

Example:

SELECT COUNT(*),
DATE_RANGE(tm, {range_to='2017||+2M/M'},{range_from='2017||+2M/M',range_to='2017||+5M/M'},{range_from='2017||+5M/M'}) AS points
FROM idx_dates
GROUP BY points ORDER BY points ASC;

Date math lets you work with dates and times directly in your searches. It's especially useful for handling data that changes over time. With date math, you can easily do things like find entries from a certain period, analyze data trends, or manage when information should be removed. It simplifies working with dates by letting you add or subtract time from a given date, round dates to the nearest time unit, and more, all within your search queries.

To use date math, you start with a base date, which can be:

• now for the current date and time,
• or a specific date string ending with ||.

Then, you can modify this date with operations like:

• +1y to add one year,
• -1h to subtract one hour,
• /m to round to the nearest month.

You can use these units in your operations:

• s for seconds,
• m for minutes,
• h (or H) for hours,
• d for days,
• w for weeks,
• M for months,
• y for years.

Here are some examples of how you might use date math:

• now+4h means four hours from now.
• now-2d/d is the time two days ago, rounded to the nearest day.
• 2010-04-20||+2M/d is June 20, 2010, rounded to the nearest day.

GEODIST(lat1, lon1, lat2, lon2, [...]) function calculates the geosphere distance between two points specified by their coordinates. Note that by default, both latitudes and longitudes must be in radians, and the result will be in meters. You can use arbitrary expressions for any of the four coordinates. An optimized path will be chosen when one pair of arguments directly refers to a pair of attributes, and the other one is constant.

GEODIST() also accepts an optional 5th argument, allowing you to easily convert between input and output units and select the specific geodistance formula to use. The complete syntax and a few examples are as follows:

GEODIST(lat1, lon1, lat2, lon2, { option=value, ... })

GEODIST(40.7643929, -73.9997683, 40.7642578, -73.9994565, {in=degrees, out=feet})

GEODIST(51.50, -0.12, 29.98, 31.13, {in=deg, out=mi})

The known options and their values are:

• in = {deg | degrees | rad | radians}, specifies the input units;
• out = {m | meters | km | kilometers | ft | feet | mi | miles}, specifies the output units;
• method = {adaptive | haversine}, specifies the geodistance calculation method.

The default method is "adaptive". It is a well-optimized implementation that is both more precise and much faster at all times than "haversine".

GEOPOLY2D(lat1,lon1,lat2,lon2,lat3,lon3...) creates a polygon to be used with the CONTAINS() function. This function takes into account the Earth's curvature by tessellating the polygon into smaller ones, and should be used for larger areas. For small areas, the POLY2D() function can be used instead. The function expects coordinates to be pairs of latitude/longitude coordinates in degrees; if radians are used, it will give the same result as POLY2D().

POLY2D(x1,y1,x2,y2,x3,y3...) creates a polygon to be used with the CONTAINS() function. This polygon assumes a flat Earth, so it should not be too large; for large areas, the GEOPOLY2D() function, which takes Earth's curvature into consideration, should be used.