Apache Drill结合MongoDB：连接两个集合时遇到问题

一、先排查当前JOIN失败的常见原因 #

• 字段类型不匹配：这是最常见的触发错误的原因，比如courierrecords.customer_id是字符串类型，而customers.customerId是MongoDB原生的ObjectId类型，直接用=比较会因为类型不兼容失败。先执行以下语句确认字段类型：

-- 查看courierrecords的customer_id类型和示例值
SELECT customer_id, typeof(customer_id) FROM courierrecords LIMIT 5;

-- 查看customers的customerId类型和示例值
SELECT customerId, typeof(customerId) FROM customers LIMIT 5;

• 集合引用路径错误：确认在Drill中是否使用了完整的集合路径，比如mongodb.your_database_name.courierrecords，直接写集合名可能导致Drill无法定位到目标集合。
• 缺失关联字段：若某个集合中存在没有customer_id或customerId的文档，会干扰JOIN逻辑，可先过滤空值再尝试：

SELECT c.shipmentReferenceNumber1, cust.name
FROM courierrecords c
JOIN customers cust ON c.customer_id = cust.customerId
WHERE c.customer_id IS NOT NULL AND cust.customerId IS NOT NULL;

二、用ObjectId实现集合连接的具体写法 #

根据两个字段的类型差异，分三种场景处理：

1. customer_id是字符串（存储ObjectId的字符串形式），customerId是ObjectId #

将字符串转换为ObjectId后再匹配：

SELECT c.shipmentReferenceNumber1, cust.name
FROM courierrecords c
JOIN customers cust ON TO_OBJECTID(c.customer_id) = cust.customerId;

2. customer_id是ObjectId，customerId是字符串 #

将ObjectId转换为字符串后再匹配：

SELECT c.shipmentReferenceNumber1, cust.name
FROM courierrecords c
JOIN customers cust ON TO_STRING(c.customer_id) = cust.customerId;

3. 两者都是ObjectId #

直接用等号关联即可，注意确认字段名完全一致：

SELECT c.shipmentReferenceNumber1, cust.name
FROM courierrecords c
JOIN customers cust ON c.customer_id = cust.customerId;

三、调试技巧 #

如果仍无法解决，用LEFT JOIN找出匹配失败的数据，精准定位问题：

SELECT c.shipmentReferenceNumber1, c.customer_id, cust.name, cust.customerId
FROM courierrecords c
LEFT JOIN customers cust ON TO_OBJECTID(c.customer_id) = cust.customerId
WHERE cust.name IS NULL;

这条语句会列出所有在courierrecords中存在、但找不到对应customers的数据，可检查这些customer_id是否在customers集合中存在，或格式是否一致。

内容的提问来源于stack exchange，提问作者Jay