• 连接
警告：此功能处于测试阶段。请谨慎使用。
Manticore Search中的表连接允许您通过匹配相关列将两个表中的文档结合起来。此功能支持更复杂的查询，并增强了跨多个表的数据检索能力。
通用语法
SQL
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 部分。
当通过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)JSON
POST /search
{
"index": "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
}请注意，在使用JSON属性连接两个表时，left 操作数部分中有一个 type 字段。允许的值为 string 和 int。
连接类型
Manticore Search 支持两种类型的连接：
INNER JOIN：仅返回两个表中都有匹配项的行。例如，该查询在
orders和customers表之间执行一个 INNER JOIN，仅包含具有匹配客户的订单。
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
{
"index": "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
{
"index": "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": ""
}
}
]
}
}带有分面功能的复杂连接
此查询从 orders 和 customers 表中检索产品、客户姓名、产品价格和产品标签。它执行了一个 LEFT JOIN，确保即使客户没有下订单也会被包含在内。该查询过滤结果，仅包含价格大于 500 的订单，并将产品匹配为“laptop”、“phone”或“monitor”。结果按订单的 id 以升序排列。此外，查询还根据连接的 orders 表的 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;POST /search
{
"index": "customers",
"_source": ["orders.product", "name", "orders.details", "orders.tags"],
"sort": [{"orders.id": "asc"}],
"join": [
{
"type": "left",
"table": "orders",
"on": [
{
"left": {
"table": "customers",
"field": "id"
},
"operator": "eq",
"right": {
"table": "orders",
"field": "customer_id"
}
}
],
"query": {
"range": {
"orders.details.price": {
"gt": 500
}
},
"match": {
"*": "laptop|phone|monitor"
}
}
}
],
"aggs": {
"group_property": {
"terms": {
"field": "orders.details.warranty"
}
}
}
}+----------------+---------------+----------------------+-------------+
| 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 ---{
"took": 0,
"timed_out": false,
"hits": {
"total": 2,
"total_relation": "eq",
"hits": [
{
"_id": 1,
"_score": 1,
"_source": {
"name": "Alice Johnson",
"orders.tags": [
101,
102
],
"orders.details": {
"price": 1200,
"warranty": "2 years"
},
"orders.product": "Laptop"
}
},
{
"_id": 2,
"_score": 1,
"_source": {
"name": "Bob Smith",
"orders.tags": [
103
],
"orders.details": {
"price": 800,
"warranty": "1 year"
},
"orders.product": "Phone"
}
}
]
},
"aggregations": {
"group_property": {
"buckets": [
{
"key": "1 year",
"doc_count": 1
},
{
"key": "2 years",
"doc_count": 1
}
]
}
}
}注意事项和最佳实践
在 Manticore Search 中使用 JOIN 时，请注意以下几点：
字段选择：在 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 子句中，不能对涉及两个表字段的表达式别名进行过滤。
在 MVA 中使用 ANY：在 JOIN 中使用多值属性的
ANY()函数时，为连接表中的多值属性起别名：SELECT *, t2.m AS alias FROM t LEFT JOIN t2 ON t.id = t2.t_id WHERE ANY(alias) IN (3, 5)
通过遵循这些指南，您可以有效地在 Manticore Search 中使用 JOIN 来结合多个索引的数据并执行复杂的查询。
最后更新于