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) проверяет, находится ли точка (x,y) внутри заданного полигона, и возвращает 1, если истинно, или 0, если ложно. Полигон должен быть задан с помощью функции POLY2D(). Первая функция предназначена для "маленьких" полигонов, то есть со стороной менее 500 км (300 миль), и она не учитывает кривизну Земли для скорости. Для больших расстояний следует использовать GEOPOLY2D, которая разбивает заданный полигон на меньшие части, учитывая кривизну Земли.

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

В первом случае оператор сравнения <> вернёт 0.0 (ложь) из-за порога, и IF() всегда вернёт ** в результате. Во втором случае то же выражение sqrt(3)*sqrt(3)-3 будет сравниваться с нулём без порога самой функцией IF(). Однако его значение будет немного отличаться от нуля из-за ограниченной точности вычислений с плавающей запятой. Из-за этого сравнение с 0.0, выполняемое IF(), не пройдёт, и второй вариант вернёт 'a' в качестве результата.

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

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,...) принимает 2 или более аргументов и возвращает 1, если первый аргумент (expr) равен любому из остальных аргументов (val1..valN), или 0 в противном случае. В настоящее время все проверяемые значения (но не само выражение) должны быть константами. Константы предварительно сортируются, и используется бинарный поиск, поэтому IN() даже для большого произвольного списка констант будет очень быстрым. Первый аргумент также может быть атрибутом MVA. В этом случае IN() вернет 1, если любое из значений MVA равно любому из других аргументов. IN() также поддерживает синтаксис IN(expr,@uservar) для проверки, принадлежит ли значение списку в заданной глобальной пользовательской переменной. Первый аргумент может быть атрибутом JSON.

INDEXOF(cond FOR var IN json.array) функция перебирает все элементы в массиве и возвращает индекс первого элемента, для которого 'cond' истинно, и -1, если 'cond' ложно для каждого элемента в массиве.

INTERVAL(expr,point1,point2,point3,...) принимает 2 или более аргументов и возвращает индекс аргумента, который меньше первого аргумента: возвращает 0, если expr<point1, 1, если point1<=expr<point2, и так далее. Для корректной работы этой функции требуется, чтобы point1<point2<...<pointN.

LENGTH(attr_mva) функция возвращает количество элементов в наборе MVA. Работает как с 32-битными, так и с 64-битными атрибутами MVA. LENGTH(attr_json) возвращает длину поля в JSON. Возвращаемое значение зависит от типа поля. Например, LENGTH(json_attr.some_int) всегда возвращает 1, а LENGTH(json_attr.some_array) возвращает количество элементов в массиве. LENGTH(string_expr) функция возвращает длину строки, полученной из выражения. TO_STRING() должно заключать выражение, независимо от того, возвращает ли выражение не-строку или это просто строковый атрибут.

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;

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

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

Это поместит документы с размерами 5 и 6 первыми, за ними последуют размеры 7 и 8. В случае, если есть исходное значение, не указанное в массиве (например, размер 10), оно по умолчанию будет равно 15 и, в данном случае, будет помещено в конец.

Обратите внимание, что CURTIME(), UTC_TIME(), UTC_TIMESTAMP() и TIMEDIFF() могут быть преобразованы в числовые типы с помощью произвольных функций преобразования, таких как BIGINT(), DOUBLE() и т.д.

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()) |
+-------------+
| 2024        |
+-------------+

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              |
+------------------------------------------+

Этот пример форматирует текущие дату и время, отображая четырехзначный год и время в 24-часовом формате.

DATE_HISTOGRAM(expr, {calendar_interval='unit_name'}) принимает размер ведра в виде названия единицы измерения и возвращает номер ведра для значения. Значения округляются до ближайшего ведра. Основная функция:

key_of_the_bucket = interval * floor ( value / interval )

Интервалы могут быть указаны с использованием названия единицы, например week, или как одиночная единица, например 1M. Однако множественные единицы, такие как 2d, не поддерживаются в calendar_interval, но разрешены в fixed_interval.

Допустимые интервалы для calendar_interval:

• minute, 1m
• hour, 1h
• day, 1d
• week, 1w (неделя — это интервал между начальным днём недели, часом, минутой, секундой и следующей неделей, но тем же днём и временем недели)
• month, 1M
• year, 1y (год — это интервал между начальным днём месяца, временем и следующим годом, но тем же днём месяца и временем)

Допустимые интервалы для fixed_interval:

• minute, 2m
• hour, 3h
• day, 5d

Используется в агрегировании, FACET и группировке.

Пример:

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'}) принимает набор диапазонов и возвращает номер ведра для значения. Выражение включает значение range_from и исключает значение range_to для каждого диапазона. Диапазон может быть открытым — содержать только значение range_from или только range_to. Разница с функцией RANGE() в том, что значения range_from и range_to могут быть выражены с помощью выражений Date math.

Используется в агрегировании, FACET и группировке.

Пример:

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 позволяет работать с датами и временем непосредственно в ваших поисках. Это особенно полезно для работы с данными, которые изменяются с течением времени. С помощью date math вы можете легко находить записи за определённый период, анализировать тренды данных или управлять временем удаления информации. Это упрощает работу с датами, позволяя добавлять или вычитать время из заданной даты, округлять даты до ближайшей единицы времени и многое другое — всё это прямо в ваших поисковых запросах.

Для использования date math вы начинаете с базовой даты, которая может быть:

• now для текущей даты и времени,
• или конкретная строка даты, оканчивающаяся на ||.

Затем вы можете изменять эту дату с помощью операций, таких как:

• +1y для добавления одного года,
• -1h для вычитания одного часа,
• /m для округления до ближайшего месяца.

Вы можете использовать следующие единицы в операциях:

• s для секунд,
• m для минут,
• h (или H) для часов,
• d для дней,
• w для недель,
• M для месяцев,
• y для лет.

Вот несколько примеров использования date math:

• now+4h — четыре часа от текущего времени.
• now-2d/d — время два дня назад, округлённое до ближайшего дня.
• 2010-04-20||+2M/d — 20 июня 2010 года, округлённое до ближайшего дня.

GEODIST(lat1, lon1, lat2, lon2, [...]) функция вычисляет геодезическое расстояние между двумя точками, заданными их координатами. Обратите внимание, что по умолчанию и широты, и долготы должны быть в радианах, а результат будет в метрах. Вы можете использовать произвольные выражения для любой из четырех координат. Оптимальный путь будет выбран, когда одна пара аргументов напрямую ссылается на пару атрибутов, а другая — является константой.

GEODIST() также принимает необязательный 5-й аргумент, позволяющий легко преобразовывать входные и выходные единицы и выбирать конкретную формулу геодезического расстояния для использования. Полный синтаксис и несколько примеров приведены ниже:

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

Известные опции и их значения:

• in = {deg | degrees | rad | radians}, задаёт единицы входных данных;
• out = {m | meters | km | kilometers | ft | feet | mi | miles | yd | yards | in | inch | cm | centimeters | mm | millimeters | NM | nmi | nauticalmiles}, задаёт единицы выходных данных;
• method = {adaptive | haversine}, задаёт метод вычисления геодезического расстояния.

Метод по умолчанию — "adaptive". Это хорошо оптимизированная реализация, которая всегда точнее и значительно быстрее, чем "haversine".

GEOPOLY2D(lat1,lon1,lat2,lon2,lat3,lon3...) создаёт полигон, используемый с функцией CONTAINS(). Эта функция учитывает кривизну Земли, разбивая полигон на меньшие части, и должна использоваться для больших территорий. Для небольших площадей можно использовать функцию POLY2D(). Функция ожидает, что координаты — это пары широты/долготы в градусах; если используются радианы, результат будет таким же, как у POLY2D().

POLY2D(x1,y1,x2,y2,x3,y3...) создаёт полигон, используемый с функцией CONTAINS(). Этот полигон предполагает плоскую Землю, поэтому его площадь не должна быть слишком большой; для больших территорий следует использовать функцию GEOPOLY2D(), которая учитывает кривизну Земли.

Конкатенирует две или более строк в одну. Аргументы, не являющиеся строками, должны быть явно преобразованы в строку с помощью функции TO_STRING().

CONCAT(TO_STRING(float_attr), ',', TO_STRING(int_attr), ',', title)

LEVENSHTEIN ( pattern, source, {normalize=0, length_delta=0}) возвращает число (расстояние Левенштейна) односимвольных правок (вставок, удалений или замен) между строками pattern и source, необходимых для преобразования pattern в source.

• pattern, source - константная строка, имя строкового поля, имя JSON поля или любое выражение, дающее строку (например, SUBSTRING_INDEX())
• normalize - опция возвращать расстояние в виде числа с плавающей запятой в диапазоне [0.0 - 1.0], где 0.0 — точное совпадение, а 1.0 — максимальное различие. Значение по умолчанию 0, что означает не нормализовать и возвращать результат целым числом.
• length_delta - пропускает вычисление расстояния Левенштейна и возвращает max(strlen(pattern), strlen(source)), если опция установлена, а длина строк отличается более чем на length_delta. Значение по умолчанию 0, что означает вычислять расстояние Левенштейна для любых входных строк. Эта опция может быть полезна при проверке преимущественно похожих строк.

SELECT LEVENSHTEIN('gily', attr1) AS dist, WEIGHT() AS w FROM test WHERE MATCH('test') ORDER BY w DESC, dist ASC;
SELECT LEVENSHTEIN('gily', j.name, {length_delta=6}) AS dist, WEIGHT() AS w FROM test WHERE MATCH('test') ORDER BY w DESC;
SELECT LEVENSHTEIN(title, j.name, {normalize=1}) AS dist, WEIGHT() AS w FROM test WHERE MATCH ('test') ORDER BY w DESC, dist ASC;

Функция REGEX(attr,expr) возвращает 1, если регулярное выражение совпадает со строкой атрибута, и 0 в противном случае. Работает как со строковыми, так и с JSON атрибутами.

SELECT REGEX(content, 'box?') FROM test;
SELECT REGEX(j.color, 'red | pink') FROM test;

Выражения должны соответствовать синтаксису RE2. Для выполнения поиска без учета регистра, например, можно использовать:

SELECT REGEX(content, '(?i)box') FROM test;

Функция SNIPPET() используется для выделения результатов поиска в заданном тексте. Первые два аргумента — это текст для выделения и запрос. Опции могут передаваться функции как третий, четвертый и последующие аргументы. SNIPPET() может получать текст для выделения напрямую из таблицы. В этом случае первым аргументом должно быть имя поля:

SELECT SNIPPET(body, QUERY()) FROM myIndex WHERE MATCH('my.query')

В этом примере выражение QUERY() возвращает текущий полнотекстовый запрос. SNIPPET() также может выделять неиндексированный текст:

mysql  SELECT id, SNIPPET('text to highlight', 'my.query', 'limit=100') FROM myIndex WHERE MATCH('my.query')

Дополнительно, его можно использовать для выделения текста, полученного из других источников с помощью пользовательской функции (UDF):

SELECT id, SNIPPET(myUdf(id), 'my.query', 'limit=100') FROM myIndex WHERE MATCH('my.query')

В данном контексте myUdf() — это функция, определённая пользователем (UDF), которая извлекает документ по его ID из внешнего источника хранения. Функция SNIPPET() является "post limit" функцией, что означает, что вычисление снитпетов откладывается до тех пор, пока не будет подготовлен весь окончательный набор результатов, и даже после применения оператора LIMIT. Например, если используется LIMIT 20,10, то SNIPPET() будет вызвана не более 10 раз.

Важно отметить, что SNIPPET() не поддерживает ограничения по полям. Для этой функциональности используйте HIGHLIGHT().

SELECT SUBSTRING_INDEX('www.w3schools.com', '.', 2) FROM test;
SELECT SUBSTRING_INDEX(j.coord, ' ', 1) FROM test;
SELECT          SUBSTRING_INDEX('1.2 3.4', ' ',  1);  /* '1.2' */
SELECT          SUBSTRING_INDEX('1.2 3.4', ' ', -1);  /* '3.4' */
SELECT sint (   SUBSTRING_INDEX('1.2 3.4', ' ',  1)); /* 1 */
SELECT sint (   SUBSTRING_INDEX('1.2 3.4', ' ', -1)); /* 3 */
SELECT double ( SUBSTRING_INDEX('1.2 3.4', ' ',  1)); /* 1.200000 */
SELECT double ( SUBSTRING_INDEX('1.2 3.4', ' ', -1)); /* 3.400000 */

UPPER(string) преобразует аргумент в верхний регистр, LOWER(string) — в нижний регистр.

Результат также может быть приведён к числовому типу, но только если строковый аргумент может быть преобразован в число. Числовые значения могут быть приведены с помощью произвольных функций (BITINT, DOUBLE и т.п.).

SELECT upper('www.w3schools.com', '.', 2); /* WWW.W3SCHOOLS.COM  */
SELECT double (upper ('1.2e3')); /* 1200.000000 */
SELECT integer (lower ('12345')); /* 12345 */