# • 连接 **警告：此功能处于测试阶段。请谨慎使用。** Manticore Search中的表连接允许您通过匹配相关列将两个表中的文档结合起来。此功能支持更复杂的查询，并增强了跨多个表的数据检索能力。 ## 通用语法 ### SQL ```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](/manticoresearch-manual-zh/5-sou-suo/intro.md#General-syntax) 部分。 当通过JSON属性的值进行连接时，您需要使用 `int()` 或 `string()` 函数显式指定值的类型。 ```sql SELECT ... ON left_table.json_attr.string_id = string(right_table.json_attr.string_id) ``` ```sql SELECT ... ON left_table.json_attr.int_id = int(right_table.json_attr.int_id) ``` ### JSON ```json POST /search { "index": "table_name", "query": { }, "join": [ { "type": "inner" | "left", "table": "joined_table_name", "query": { }, "on": [ { "left": { "table": "left_table_name", "field": "field_name", "type": "" }, "operator": "eq", "right": { "table": "right_table_name", "field": "field_name" } } ] } ], "options": { ... } } on.type: { int | string } ``` 请注意，在使用JSON属性连接两个表时，`left` 操作数部分中有一个 `type` 字段。允许的值为 `string` 和 `int`。 ## 连接类型 Manticore Search 支持两种类型的连接： 1. **INNER JOIN**：仅返回两个表中都有匹配项的行。例如，该查询在 `orders` 和 `customers` 表之间执行一个 INNER JOIN，仅包含具有匹配客户的订单。 ```sql 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; ``` ```json 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"}] } ``` ```sql +---------+-------------------+----------------+-------------------+ | 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) ``` ```json { "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" } } ] } } ``` 2. **LEFT JOIN**：返回左表中的所有行以及右表中匹配的行。如果没有匹配项，则右表的列返回 NULL 值。例如，该查询使用 LEFT JOIN 检索所有客户及其对应的订单。如果没有对应的订单，则会显示 NULL 值。结果按照客户的电子邮件进行排序，并且只选择客户的姓名和订单数量。 ```sql SELECT name, orders.quantity FROM customers LEFT JOIN orders ON orders.customer_id = customers.id ORDER BY email ASC; ``` ```json 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 属性中的保修详情对结果进行分面处理。 ```sql 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; ``` ```json 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 时，请注意以下几点： 1. **字段选择**：在 JOIN 中从两个表中选择字段时，不要为左表的字段加前缀，但要为右表的字段加前缀。例如： ```sql SELECT field_name, right_table.field_name FROM ... ``` 2. **JOIN 条件**：在 JOIN 条件中始终显式指定表名： ```sql JOIN ON table_name.some_field = another_table_name.some_field ``` 3. **带有 JOIN 的表达式**：当使用结合两个连接表字段的表达式时，给表达式的结果起别名： ```sql SELECT *, (nums2.n + 3) AS x, x * n FROM nums LEFT JOIN nums2 ON nums2.id = nums.num2_id ``` 4. **对别名表达式进行过滤**：在 WHERE 子句中，不能对涉及两个表字段的表达式别名进行过滤。 5. **在 MVA 中使用 ANY**：在 JOIN 中使用多值属性的 `ANY()` 函数时，为连接表中的多值属性起别名： ```sql 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 来结合多个索引的数据并执行复杂的查询。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://vergil-lai.gitbook.io/manticoresearch-manual-zh/5-sou-suo/joining.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.