使用ClickHouse MongoDB函数读取嵌套对象异常，求正确Schema配置

问题场景 #

使用ClickHouse的mongodb表函数读取MongoDB集合时，嵌套对象delivery_slot的字段返回空数组，无法正确获取对应值。

MongoDB文档结构

{
  _id: ObjectId('63aaa3c9972e2b2adee20408'),
  bag_id: 403747,
  delivery_slot: {
    slot: 'By 07:00 AM',
    upper_bound: '2023-01-04T07:50:27+00:00',
    lower_bound: '2023-01-02T07:50:27+00:00',
    date: '2023-01-04T07:50:27+00:00',
    type: 'order_window'
  },
  display_name: 'Bag'
}

原查询语句

SELECT 
    bag_id, 
    display_name,
    delivery_slot.date
FROM mongodb(
    '<IP>:<host>', 
    'db', 
    'collection',
    'username',
    'password',
    'bag_id UInt64, 
     display_name String, 
     delivery_slot Nested( slot String, lower_bound String, upper_bound String, date String, type String)', 
    'connectTimeoutMS=10000'
) 
LIMIT 100;

实际错误结果

bag_id|display_name|delivery_slot.date|
------+------------+------------------+
403747|Bag         |[]                |
403754|Bag         |[]                |
403755|Bag         |[]                |
403756|Bag         |[]                |
403757|Bag         |[]                |
403758|Bag         |[]                |
403759|Bag         |[]                |

预期结果

bag_id|display_name|delivery_slot.date|
------+------------+------------------+
403747|Bag         |2023-01-04T07:50:27+00:00              |
403754|Bag         |2023-01-04T07:50:27+00:00              |
403755|Bag         |2023-01-04T07:50:27+00:00                |
403756|Bag         |2023-01-04T07:50:27+00:00                |
403757|Bag         |2023-01-04T07:50:27+00:00                |
403758|Bag         |2023-01-04T07:50:27+00:00               |
403759|Bag         |2023-01-04T07:50:27+00:00               |

解决方案 #

问题根源是Schema中错误地将delivery_slot定义为Nested类型。ClickHouse的Nested类型对应MongoDB的数组形式嵌套结构，而你的MongoDB文档里delivery_slot是单个嵌套对象，因此需要改用Tuple类型或直接展开嵌套字段。

方式1：使用Tuple类型映射单个嵌套对象

SELECT 
    bag_id, 
    display_name,
    delivery_slot.date
FROM mongodb(
    '<IP>:<host>', 
    'db', 
    'collection',
    'username',
    'password',
    'bag_id UInt64, 
     display_name String, 
     delivery_slot Tuple(slot String, lower_bound String, upper_bound String, date String, type String)', 
    'connectTimeoutMS=10000'
) 
LIMIT 100;

方式2：直接展开嵌套字段（适合仅需部分字段的场景）

SELECT 
    bag_id, 
    display_name,
    delivery_slot_date
FROM mongodb(
    '<IP>:<host>', 
    'db', 
    'collection',
    'username',
    'password',
    'bag_id UInt64, 
     display_name String, 
     delivery_slot_date String alias `delivery_slot.date`', 
    'connectTimeoutMS=10000'
) 
LIMIT 100;

如果需要完整获取嵌套对象的所有字段，可展开全部：

SELECT 
    bag_id, 
    display_name,
    delivery_slot_slot,
    delivery_slot_lower_bound,
    delivery_slot_upper_bound,
    delivery_slot_date,
    delivery_slot_type
FROM mongodb(
    '<IP>:<host>', 
    'db', 
    'collection',
    'username',
    'password',
    'bag_id UInt64, 
     display_name String, 
     delivery_slot_slot String alias `delivery_slot.slot`,
     delivery_slot_lower_bound String alias `delivery_slot.lower_bound`,
     delivery_slot_upper_bound String alias `delivery_slot.upper_bound`,
     delivery_slot_date String alias `delivery_slot.date`,
     delivery_slot_type String alias `delivery_slot.type`', 
    'connectTimeoutMS=10000'
) 
LIMIT 100;

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