Объединения таблиц в Manticore Search позволяют комбинировать документы из двух таблиц, сопоставляя связанные столбцы. Эта функциональность обеспечивает более сложные запросы и улучшенный поиск данных по нескольким таблицам.
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)
| [..фильтры по атрибутам правой таблицы]
}
Для получения дополнительной информации о параметрах select обратитесь к разделу SELECT.
При объединении по значению из JSON-атрибута необходимо явно указать тип значения с помощью функции int() или string().
SELECT ... ON left_table.json_attr.string_id = string(right_table.json_attr.string_id)
SELECT ... ON left_table.json_attr.int_id = int(right_table.json_attr.int_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
}
Обратите внимание, что в разделе операнда left есть поле type, которое следует использовать при объединении двух таблиц с использованием json-атрибутов. Допустимые значения — string и int.
Manticore Search поддерживает два типа объединений:
- INNER JOIN: Возвращает только строки, где есть совпадение в обеих таблицах. Например, запрос выполняет INNER JOIN между таблицами
orders и customers, включая только заказы, у которых есть соответствующие клиенты.
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;
POST /search
{
"table": "orders",
"join": [
{
"type": "inner",
"table": "customers",
"query": {
"query_string": "maple"
},
"on": [
{
"left": {
"table": "orders",
"field": "customer_id"
},
"operator": "eq",
"right": {
"table": "customers",
"field": "id"
}
}
]
}
],
"_source": ["product", "customers.email", "customers.name", "customers.address"],
"sort": [{"customers.email": "asc"}]
}
+---------+-------------------+----------------+-------------------+
| product | customers.email | customers.name | customers.address |
+---------+-------------------+----------------+-------------------+
| Laptop | alice@example.com | Alice Johnson | 123 Maple St |
| Tablet | alice@example.com | Alice Johnson | 123 Maple St |
+---------+-------------------+----------------+-------------------+
2 rows in set (0.00 sec)
{
"took": 0,
"timed_out": false,
"hits": {
"total": 2,
"total_relation": "eq",
"hits": [
{
"_id": 1,
"_score": 1,
"_source": {
"product": "Laptop",
"customers.email": "alice@example.com",
"customers.name": "Alice Johnson",
"customers.address": "123 Maple St"
}
},
{
"_id": 3,
"_score": 1,
"_source": {
"product": "Tablet",
"customers.email": "alice@example.com",
"customers.name": "Alice Johnson",
"customers.address": "123 Maple St"
}
}
]
}
}
- LEFT JOIN: Возвращает все строки из левой таблицы и совпадающие строки из правой таблицы. Если совпадения нет, для столбцов правой таблицы возвращаются значения NULL. Например, этот запрос извлекает всех клиентов вместе с их соответствующими заказами с помощью LEFT JOIN. Если соответствующего заказа нет, появятся значения NULL. Результаты сортируются по электронной почте клиента, и выбираются только имя клиента и количество заказов.
SELECT
name, orders.quantity
FROM customers
LEFT JOIN orders
ON orders.customer_id = customers.id
ORDER BY email ASC;
POST /search
{
"table": "customers",
"_source": ["name", "orders.quantity"],
"join": [
{
"type": "left",
"table": "orders",
"on": [
{
"left": {
"table": "orders",
"field": "customer_id"
},
"operator": "eq",
"right": {
"table": "customers",
"field": "id"
}
}
]
}
],
"sort": [{"email": "asc"}]
}
+---------------+-----------------+-------------------+
| name | orders.quantity | @int_attr_email |
+---------------+-----------------+-------------------+
| Alice Johnson | 1 | alice@example.com |
| Alice Johnson | 1 | alice@example.com |
| Bob Smith | 2 | bob@example.com |
| Carol White | 1 | carol@example.com |
| John Smith | NULL | john@example.com |
+---------------+-----------------+-------------------+
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": "alice@example.com",
"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": "alice@example.com",
"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": "bob@example.com",
"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": "carol@example.com",
"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": "john@example.com",
"orders.id": 0,
"orders.customer_id": 0,
"orders.quantity": 0,
"orders.order_date": "",
"orders.tags": [],
"orders.details": null,
"orders.product": ""
}
}
]
}
}
Одна из мощных возможностей объединений таблиц в Manticore Search — это возможность выполнять полнотекстовый поиск одновременно по левой и правой таблицам. Это позволяет создавать сложные запросы, фильтрующие данные на основе текстового содержимого в нескольких таблицах.
Вы можете использовать отдельные функции MATCH() для каждой таблицы в вашем JOIN-запросе. Запрос фильтрует результаты на основе текстового содержимого в обеих таблицах.
SELECT t1.f, t2.f
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE MATCH('hello', t1) AND MATCH('goodbye', t2);
POST /search
{
"table": "t1",
"query": {
"query_string": "hello"
},
"join": [
{
"type": "left",
"table": "t2",
"query": {
"query_string": "goodbye"
},
"on": [
{
"left": {
"table": "t1",
"field": "id"
},
"operator": "eq",
"right": {
"table": "t2",
"field": "id"
}
}
]
}
],
"_source": ["f", "t2.f"]
}
+-------------+---------------+
| f | t2.f |
+-------------+---------------+
| hello world | goodbye world |
+-------------+---------------+
1 row in set (0.00 sec)
{
"took": 1,
"timed_out": false,
"hits": {
"total": 1,
"total_relation": "eq",
"hits": [
{
"_id": 2,
"_score": 1680,
"t2._score": 1680,
"_source": {
"f": "hello world",
"t2.f": "goodbye world"
}
}
]
}
}
В JSON API запросах полнотекстовый поиск по таблицам структурируется иначе, чем в SQL:
Запрос основной таблицы: Поле "query" на корневом уровне применяется к основной таблице (указанной в "table").
Запрос объединённой таблицы: Каждое определение объединения может включать собственное поле "query", которое применяется конкретно к этой объединённой таблице.
POST /search
{
"table": "t1",
"query": {
"query_string": "hello"
},
"join": [
{
"type": "left",
"table": "t2",
"query": {
"match": {
"*": "goodbye"
}
},
"on": [
{
"left": {
"table": "t1",
"field": "id"
},
"operator": "eq",
"right": {
"table": "t2",
"field": "id"
}
}
]
}
]
}
{
"took": 1,
"timed_out": false,
"hits": {
"total": 1,
"total_relation": "eq",
"hits": [
{
"_id": 1,
"_score": 1680,
"t2._score": 1680,
"_source": {
"f": "hello world",
"t2.id": 1,
"t2.f": "goodbye world"
}
}
]
}
}
1. Запрос только к основной таблице: Возвращает все совпадающие строки из основной таблицы. Для несопоставленных записей объединённой таблицы (LEFT JOIN) SQL возвращает значения NULL, а JSON API — значения по умолчанию (0 для чисел, пустые строки для текста).
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE MATCH('database', t1);
POST /search
{
"table": "t1",
"query": {
"query_string": "database"
},
"join": [
{
"type": "left",
"table": "t2",
"on": [
{
"left": {
"table": "t1",
"field": "id"
},
"operator": "eq",
"right": {
"table": "t2",
"field": "id"
}
}
]
}
]
}
+------+-----------------+-------+------+
| id | f | t2.id | t2.f |
+------+-----------------+-------+------+
| 3 | database search | NULL | NULL |
+------+-----------------+-------+------+
1 row in set (0.00 sec)
{
"took": 0,
"timed_out": false,
"hits": {
"total": 1,
"total_relation": "eq",
"hits": [
{
"_id": 3,
"_score": 1680,
"t2._score": 0,
"_source": {
"f": "database search",
"t2.id": 0,
"t2.f": ""
}
}
]
}
}
2. Запрос к объединённой таблице действует как фильтр: Когда у объединённой таблицы есть запрос, возвращаются только записи, удовлетворяющие как условию объединения, так и условию запроса.
POST /search
{
"table": "t1",
"query": {
"query_string": "database"
},
"join": [
{
"type": "left",
"table": "t2",
"query": {
"query_string": "nonexistent"
},
"on": [
{
"left": {
"table": "t1",
"field": "id"
},
"operator": "eq",
"right": {
"table": "t2",
"field": "id"
}
}
]
}
]
}
{
"took": 0,
"timed_out": false,
"hits": {
"total": 0,
"total_relation": "eq",
"hits": []
}
}
3. Тип JOIN влияет на фильтрацию: INNER JOIN требует выполнения условий объединения и запроса, тогда как LEFT JOIN возвращает совпадающие строки левой таблицы даже при несоответствии условий правой таблицы.
При использовании полнотекстового поиска с объединениями учитывайте следующие моменты:
-
Поиск по конкретным таблицам:
- SQL: Каждая функция
MATCH() должна указывать, в какой таблице искать: MATCH('term', table_name)
- JSON: Используйте корневое поле
"query" для основной таблицы и поле "query" внутри каждого определения объединения для объединённых таблиц
-
Гибкость синтаксиса запроса: JSON API поддерживает синтаксис как "query_string", так и "match" для полнотекстовых запросов
-
Влияние на производительность: Полнотекстовый поиск по обеим таблицам может повлиять на производительность запроса, особенно при больших объёмах данных. Рекомендуется использовать соответствующие индексы и размеры пакетов.
-
Обработка NULL/значений по умолчанию: При LEFT JOIN, если нет совпадающей записи в правой таблице, оптимизатор запроса решает, сначала ли оценивать полнотекстовые условия или условия фильтрации, исходя из производительности. SQL возвращает значения NULL, а JSON API — значения по умолчанию (0 для чисел, пустые строки для текста).
-
Поведение фильтрации: Запросы к объединённым таблицам действуют как фильтры — они ограничивают результаты записями, удовлетворяющими и условиям объединения, и условиям запроса.
-
Поддержка полнотекстовых операторов: Все операторы полнотекстового поиска поддерживаются в JOIN-запросах, включая фразы, близость, поиск по полям, NEAR, кворум и расширенные операторы.
-
Вычисление оценки релевантности: Каждая таблица поддерживает собственный балл релевантности, доступный через table_name.weight() в SQL или table_name._score в JSON-ответах.
Опираясь на предыдущие примеры, рассмотрим более продвинутый сценарий, где мы комбинируем объединения таблиц с фасетированием и полнотекстовым поиском по нескольким таблицам. Это демонстрирует всю мощь возможностей JOIN в Manticore с комплексной фильтрацией и агрегацией.
Этот запрос демонстрирует полнотекстовый поиск по таблицам customers и orders, объединённый с фильтрацией по диапазону и фасетным поиском. Он ищет клиентов с именами "Alice" или "Bob" и их заказы, содержащие "laptop", "phone" или "tablet" с ценами выше $500. Результаты упорядочены по ID заказа и фасетированы по условиям гарантии.
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 | tablet', orders)
AND MATCH('alice | bob', customers)
ORDER BY orders.id ASC
FACET orders.details.warranty;
POST /search
{
"table": "customers",
"query": {
"bool": {
"must": [
{
"range": {
"orders.details.price": {
"gt": 500
}
},
"query_string": "alice | bob"
]
}
},
"join": [
{
"type": "left",
"table": "orders",
"query": {
"query_string": "laptop | phone | tablet"
},
"on": [
{
"left": {
"table": "customers",
"field": "id"
},
"operator": "eq",
"right": {
"table": "orders",
"field": "customer_id"
}
}
]
}
],
"_source": ["orders.product", "name", "orders.details.price", "orders.tags"],
"sort": [{"orders.id": "asc"}],
"aggs": {
"warranty_facet": {
"terms": {
"field": "orders.details.warranty"
}
}
}
}
+-----------------+---------------+----------------------+-------------+
| orders.product | name | orders.details.price | orders.tags |
+-----------------+---------------+----------------------+-------------+
| Laptop Computer | Alice Johnson | 1200 | 101,102 |
| Smart Phone | Bob Smith | 800 | 103 |
+-----------------+---------------+----------------------+-------------+
2 rows in set (0.00 sec)
+-------------------------+----------+
| orders.details.warranty | count(*) |
+-------------------------+----------+
| 2 years | 1 |
| 1 year | 1 |
+-------------------------+----------+
2 rows in set (0.00 sec)
{
"took": 0,
"timed_out": false,
"hits": {
"total": 3,
"total_relation": "eq",
"hits": [
{
"_id": 1,
"_score": 1,
"orders._score": 1565,
"_source": {
"name": "Alice Johnson",
"orders.tags": [
101,
102
],
"orders.product": "Laptop Computer"
}
},
{
"_id": 2,
"_score": 1,
"orders._score": 1565,
"_source": {
"name": "Bob Smith",
"orders.tags": [
103
],
"orders.product": "Smart Phone"
}
},
{
"_id": 1,
"_score": 1,
"orders._score": 1565,
"_source": {
"name": "Alice Johnson",
"orders.tags": [
101,
104
],
"orders.product": "Tablet Device"
}
}
]
},
"aggregations": {
"warranty_facet": {
"buckets": [
{
"key": "2 years",
"doc_count": 1
},
{
"key": "1 year",
"doc_count": 2
}
]
}
}
}
Для запросов в объединении можно задать отдельные параметры: для левой и правой таблиц. Синтаксис — OPTION(<table_name>) для SQL-запросов и один или несколько подобъектов в "options" для JSON-запросов.
Вот пример, как задать разные веса полей для полнотекстового запроса по правой таблице. Чтобы получить веса совпадений через SQL, используйте выражение <table_name>.weight().
В JSON-запросах этот вес представлен как <table_name>._score.
SELECT product, customers.email, customers.name, customers.address, customers.weight()
FROM orders
INNER JOIN customers
ON customers.id = orders.customer_id
WHERE MATCH('maple', customers)
OPTION(customers) field_weights=(address=1500);
POST /search
{
"table": "orders",
"options": {
"customers": {
"field_weights": {
"address": 1500
}
}
},
"join": [
{
"type": "inner",
"table": "customers",
"query": {
"query_string": "maple"
},
"on": [
{
"left": {
"table": "orders",
"field": "customer_id"
},
"operator": "eq",
"right": {
"table": "customers",
"field": "id"
}
}
]
}
],
"_source": ["product", "customers.email", "customers.name", "customers.address"]
}
+---------+-------------------+----------------+-------------------+--------------------+
| product | customers.email | customers.name | customers.address | customers.weight() |
+---------+-------------------+----------------+-------------------+--------------------+
| Laptop | alice@example.com | Alice Johnson | 123 Maple St | 1500680 |
| Tablet | alice@example.com | Alice Johnson | 123 Maple St | 1500680 |
+---------+-------------------+----------------+-------------------+--------------------+
2 rows in set (0.00 sec)
{
"took": 0,
"timed_out": false,
"hits": {
"total": 2,
"total_relation": "eq",
"hits": [
{
"_id": 1,
"_score": 1,
"customers._score": 15000680,
"_source": {
"product": "Laptop",
"customers.email": "alice@example.com",
"customers.name": "Alice Johnson",
"customers.address": "123 Maple St"
}
},
{
"_id": 3,
"_score": 1,
"customers._score": 15000680,
"_source": {
"product": "Tablet",
"customers.email": "alice@example.com",
"customers.name": "Alice Johnson",
"customers.address": "123 Maple St"
}
}
]
}
}
При выполнении объединений таблиц Manticore Search обрабатывает результаты пакетами для оптимизации производительности и использования ресурсов. Вот как это работает:
-
Как работает пакетная обработка:
- Сначала выполняется запрос к левой таблице, и результаты накапливаются в пакет.
- Этот пакет затем используется как входные данные для запроса к правой таблице, который выполняется как единая операция.
- Такой подход минимизирует количество запросов к правой таблице, повышая эффективность.
-
Настройка размера пакета:
- Размер пакета можно настроить с помощью опции поиска
join_batch_size.
- Также он настраивается в разделе
searchd конфигурационного файла.
- Размер пакета по умолчанию —
1000, но вы можете увеличить или уменьшить его в зависимости от задачи.
- Установка
join_batch_size=0 полностью отключает пакетную обработку, что может быть полезно для отладки или специфических сценариев.
-
Особенности производительности:
- Больший размер пакета может улучшить производительность за счёт уменьшения количества запросов к правой таблице.
- Однако большие пакеты могут потреблять больше памяти, особенно при сложных запросах или больших данных.
- Экспериментируйте с разными размерами пакетов, чтобы найти оптимальный баланс между производительностью и использованием ресурсов.
Для дальнейшей оптимизации операций объединения Manticore Search использует механизм кэширования запросов, выполняемых по правой таблице. Вот что важно знать:
Распределённые таблицы, состоящие только из локальных таблиц, поддерживаются как слева, так и справа в запросах с объединением. Однако распределённые таблицы, включающие удалённые таблицы, не поддерживаются.
При использовании JOIN в Manticore Search учитывайте следующие моменты:
-
Выбор полей: При выборе полей из двух таблиц в JOIN не используйте префикс для полей левой таблицы, но используйте префикс для полей правой таблицы. Например:
SELECT field_name, right_table.field_name FROM ...
-
Условия JOIN: Всегда явно указывайте имена таблиц в условиях JOIN:
JOIN ON table_name.some_field = another_table_name.some_field
-
Выражения с JOIN: При использовании выражений, объединяющих поля из обеих таблиц, задавайте псевдоним для результата выражения:
SELECT *, (nums2.n + 3) AS x, x * n FROM nums LEFT JOIN nums2 ON nums2.id = nums.num2_id
-
Фильтрация по псевдонимам выражений: Нельзя использовать псевдонимы выражений, включающих поля из обеих таблиц, в условии WHERE.
-
JSON-атрибуты: При объединении по JSON-атрибутам необходимо явно приводить значения к нужному типу:
-- Правильно:
SELECT * FROM t1 LEFT JOIN t2 ON int(t1.json_attr.id) = t2.json_attr.id
-- Неправильно:
SELECT * FROM t1 LEFT JOIN t2 ON t1.json_attr.id = t2.json_attr.id
-
Обработка NULL: Можно использовать условия IS NULL и IS NOT NULL для объединённых полей:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.name IS NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.name IS NOT NULL
-
Использование ANY с MVA: При использовании функции ANY() с мультизначными атрибутами в JOIN задавайте псевдоним для мультизначного атрибута из присоединённой таблицы:
SELECT *, t2.m AS alias
FROM t
LEFT JOIN t2 ON t.id = t2.t_id
WHERE ANY(alias) IN (3, 5)
Следуя этим рекомендациям, вы сможете эффективно использовать JOIN в Manticore Search для объединения данных из нескольких индексов и выполнения сложных запросов.