To select a json object from mongodb in clickhouse.

[HitendraSinghRathore]

I have a nested object in my mongo db table the column is affiliate_bag_details

{
"test": 12,
"obj": {
 "nested": [1,2,4]
}
}

while fetching the same using the function MongoDB

SELECT 
    bag_id, 
    display_name, 
    affiliate_bag_details
FROM mongodb(
    '<IP>:host', 
    'db', 
    'collection',
    'username',
    'password!2018',
    'affiliate_bag_details String',
    'connectTimeoutMS=10000'
) 
LIMIT 10;

Getting the following error Exception: Type mismatch, expected String, got type id = 3 for column affiliate_bag_details: While executing MongoDB. Can anyone please suggest what is an alternative schema that i can use to perform the above operation?

[HitendraSinghRathore]

Tried using

SELECT 
    bag_id, 
    display_name, 
    affiliate_bag_details.test
FROM mongodb(
    '<IP>:host', 
    'db', 
    'collection',
    'username',
    'password!2018',
    'affiliate_bag_details Nested( test String)',
    'connectTimeoutMS=10000'
) 
LIMIT 10;

This does not throw error but date comes as [] event tho source table has values present.

[kssenii]

Looks like an easy fixable bug: we just need to support struct ElementTraits<Document::Ptr> in https://github.com/ClickHouse/ClickHouse/blob/44c4892a588480288f3f259761ed7a192480748f/src/Processors/Sources/MongoDBSource.cpp#L242-L255

[HitendraSinghRathore]

Will it be able to read JSON objects from mongoDB as is (by mentioning Nested or JSON in schema), or will it still need to read it as String then cast post read?

[jakczer]

Hi @kssenii will it be fixed soon? maybe is it possible to add this hotfix to next release?

[HitendraSinghRathore]

Any updates on this?

[HitendraSinghRathore]

As far as i know there seems to be an active PR for this. Hopefully it will be closed with that

[kssenii]

As far as i know there seems to be an active PR for this.

which one?

Any updates on this?

No, fix was not made yet.

[HitendraSinghRathore]

@kssenii PR link This is the pr.