While using mongodb function in clickhouse ref – https://clickhouse.com/docs/en/sql-reference/table-functions/mongodb, to read a collection in the following format
{
_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'
}
The query I am using is of the format
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;
The result looks like this
bag_id|display_name|delivery_slot.date|
------+------------+------------------+
403747|Bag |[] |
403754|Bag |[] |
403755|Bag |[] |
403756|Bag |[] |
403757|Bag |[] |
403758|Bag |[] |
403759|Bag |[] |
Please help in suggesting a schema that i can use in the MongoDB function to read the data properly from MongoDB using clickhouse.
Expexted result
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 |
While the actual result I get is empty arrays in deliver_slot.date.