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