You can translate the question and the replies:

MongoDB Custom Wrapper - Type Conversion (Object, String)

Hi everyone, I have some issues with data type conversion while using the MongoDB Custom Wrapper. The following example Data illustrates the issue. ``` /* 1 */ { "_id" : ObjectId("5f22f0c2754d8395a9b0acb9"), "Id" : "00Qi000000Jr44XEAR", "Name" : "Kristen Akin", "Address" : { "city" : null, "country" : "USA", "state" : "CA", "stateCode" : null, "street" : null } } /* 2 */ { "_id" : ObjectId("5f22f0d5754d8395a9b0af25"), "Id" : "00Qi000000Jr44XEAR2", "Name" : "Kristen Akin", "Address" : {} } /* 3 */ { "_id" : ObjectId("5f22f0e0754d8395a9b0b07d"), "Id" : "00Qi000000Jr44XEAR3", "Name" : "Kristen Akin" } /* 4 */ { "_id" : ObjectId("5f23d1a7754d8395a9cc84cd"), "Id" : "00Qi000000Jr44XEAR4", "Name" : "Kristen Akin", "Address" : "" } ``` The data is not perfectly clean. "Adress" is usually a subdocument/object but can also be missing. The Custom Wrapper works fine with example 1-3 and outputs the following table: | id | name | address | city| country| state |statecode| street | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | | 00Qi000000Jr44XEAR | Kristen Akin | [Register]... | - | USA | CA | - | - | 00Qi000000Jr44XEAR2 | Kristen Akin | [Register]... | - | - |- | - |- | 00Qi000000Jr44XEAR3 | Kristen Akin | - | - | - | -|-|- but fails ony the empty String in example 4 with the following error message: > Finished with error: Error executing query. Total time 0.032 seconds. >P_DS_MONGODB_DATA_TEST [PROJECTION] [ERROR] >DS_MONGODB_DATA_TEST [BASE] [ERROR] >DS_MONGODB_DATA_TEST [CUSTOM WRAPPER] [ERROR] Received exception with message 'MongoDB wrapper error. >There is an error building VDP column 'Address', with type 2002. null' Is there a way to catch this error and just return null values as above? I know that the string at Adress is technically wrong and it´s bad practice to have different data types for the same field in a JSON but I have no controll of the service which is sending the data so I can´t fix it at the source + the real document is much more complex and this happens at other subdocuments too. PS: I already tried the JSON-Data Source from Variable Trick mentioned here https://community.denodo.com/answers/question/details?questionId=9060g00000009xUAAQ&title=Reading+JSON+form+a+database+column but this one gave me the same error. I´m currently using the following "FIELDS"-Parameter value: ``` "Id": VARCHAR, "Name": VARCHAR, "Address": \{ "city": VARCHAR, "country": VARCHAR, "state": VARCHAR, "stateCode": VARCHAR, "street": VARCHAR \} ``` Thanks in advance. Best regards. Florian

3 Answers

Hi, The error you are getting is because the address column is defined as ‘register’ type but in the fourth record, the value is a 'string'. In such cases, as the data is not clean you can create the base view with 'text' type for the address column instead of a 'register'. As mentioned in the document [Denodo MongoDB Custom Wrapper - User Manual](https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20MongoDB%20Custom%20Wrapper%20-%20User%20Manual), under the “Schemas” section: -Default is VARCHAR. -If documents in the same collection specify different types for fields with the same name, using VARCHAR (text in VDP) for that column will automatically perform the required conversions to show data from those documents. Once the base view has been created, you can perform desired parsings using the [Text Processing Functions](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/text_processing_functions) over it by creating derived views. Hope this helps!
Denodo Team
03-08-2020 17:35:07 -0400
code
Dear Denodo Team, thanks for your Answer, but this does not really solve the problem. If I use VARCHAR for the Column, I get back the following for the adress column (in Above Order of the documents): | adress | | -------- | | Document{{city=null, country=USA, state=CA, stateCode=null, street=null}}| | Document{{}}| | <null> | | <empty string> | Afterwards I can use ``` case WHEN (((ds_mongodb_data_test.address = '' OR ds_mongodb_data_test.address = ' ') OR ds_mongodb_data_test.address is null )) THEN NULL ELSE ds_mongodb_data_test.address END ``` To replace the empty string with null, which would be json compliant. BUT the Problem is > Document{{city=null, country=USA, state=CA, stateCode=null, street=null}} is not JSON compliant. 1. The double-quotes are missing, 2. Document{} needs to be replaced etc. Otherwise I can´t use https://community.denodo.com/answers/question/details?questionId=9060g00000009xUAAQ&title=Reading+JSON+form+a+database+column the JSON-from-Variable-Way to parse this column and I only get the following error message > DS_VAR_JSON_ADDRESS#0 [JSON ROUTE] [PARSE_ERROR] Received exception with message 'Unrecognized token 'Document': was expecting (JSON String, Number, Array, Object or token 'null', 'true' or 'false') at [Source: (BufferedReader); line: 1, column: 9]' The following snipped formats the address VARCHAR, but stil the double quotes are missing and performance is pretty bad because of the nested functions: ``` case WHEN ((ds_mongodb_data_test.address = '' OR ds_mongodb_data_test.address = ' ' OR ds_mongodb_data_test.address is null )) THEN NULL ELSE replace(substring(replace(ds_mongodb_data_test.address, 'Document', ''), 1), '}}', '}') END ``` Is there a way to get back a valid json-STRING from the mongodb connector, even If VARCHAR is selected? Then I hast have to repalce empty strings with null and can parse the JSON. Thanks in advance. Best regards. Florian
user
04-08-2020 05:34:30 -0400
Hi, In order to query a view the data should be in a valid format. If you want the output as "register", it should be defined as "register" type like you did in your first comment. When the data is of different types for the field with the same name, the only way to query the view is using "Varchar" as mentioned in the previous comment. Since in this case when "Varchar" is used, the data in the column returned is not in JSON format which you want, I would recommend you to clean the data before importing into VDP. Hope this helps!
Denodo Team
07-08-2020 17:21:05 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here