If processing data, it is no longer possible to transform it into JSON. Follows the error message below.
Using CONVERT_FROM(*, ‘JSON’) is only supported against string literals and direct table references of types VARCHAR and VARBINARY.
OBS.: Even transforming the data to the requested formats in the error message, this error still continues.
Are you able to provide us with the query profile where the type=VARCHAR in JSON format and still Dremio is not able to convert to JSON?
An example of the problem is attached.
I have a JSON array, but I need to remove those with null values. When handling, a formatting error occurs.
87362496-98ea-4022-8dbc-4d52fa15c298.zip (7.8 KB)
Are the values actual null values or having a value of null as string? I see empty text, see screenshot of my repro, works with nulls
All data are strings. I have attached the document that clearly demonstrates the problem I am facing. I just need to transform the “Array” field into JSON, but if there is a null field, it is a problem.
Follow the CSV in the attached example in ZIP.
Example-Problem-JSON.zip (289 Bytes)
It looks like the double quotes in JSON string is not the right one, I replaced it with the right one and then you will hit the below error
UNSUPPORTED_OPERATION ERROR: Null values are not supported in lists by default. Please set
store.json.all_text_modeto true to read lists containing nulls. Be advised that this will treat JSON null values as a string containing the word ‘null’.
Once you set that parameter, the query will work
SELECT oid_id, convert_fromJSON(“array”) JSON_aray FROM “Example-Problem-JSON 2”
{“oid_id”:“1”,“JSON_aray”:[“null”]}
{“oid_id”:“2”,“JSON_aray”:}
{“oid_id”:“3”,“JSON_aray”:[{“$oid”:“12asd2311asd3as”}]}
Example-Problem-JSON_corrected.csv.zip (887 Bytes)
But why does it always give an error when I try to treat the field I want to convert to JSON? For example, I would like to replace the [“null”] fields with empty. However, when doing this, Dremio returns me the following error:
Using CONVERT_FROM(*, ‘JSON’) is only supported against string literals and direct table references of types VARCHAR and VARBINARY.
Woks for me
SQL
SELECT case when “array” = ‘[null]’ then ‘’ else “array” end as “array” FROM “Example-Problem-JSON 2”
Output
{“oid_id”:“1”,“array”:“[null]”,“array0”:“[null]”}
{“oid_id”:“2”,“array”:“”,“array0”:“”}
{“oid_id”:“3”,“array”:“[{"$oid": "12asd2311asd3as"}]”,“array0”:“[{"$oid": "12asd2311asd3as"}]”}
See also attached screenshot
