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

ClickHouse/src/Processors/Sources/MongoDBSource.cpp

Lines 242 to 255 in 44c4892

	case ValueType::vtString:
	{
	if (value.type() == Poco::MongoDB::ElementTraits<ObjectId::Ptr>::TypeId)
	{
	std::string string_id = value.toString();
	assert_cast<ColumnString &>(column).insertData(string_id.data(), string_id.size());
	break;
	}
	else if (value.type() == Poco::MongoDB::ElementTraits<String>::TypeId)
	{
	String string = static_cast<const Poco::MongoDB::ConcreteElement<String> &>(value).value();
	assert_cast<ColumnString &>(column).insertData(string.data(), string.size());
	break;
	}

[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?