ERROR to transform in JSON

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.

@lucasft

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)

Atached Problem Image


Null fields treated and after attempted to convert to JSON.

@lucasft

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)

@lucasft

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_mode to 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.

@lucasft

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