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
@balaji.ramaswamy and all, good afternoon.
I’m having an issue similar to this.
I have this json file (attached) that has the following structure.
json_example.zip (2.4 KB)
I need to parse it extracting the following information:
“DATA_ORDER”.“value”.“subscription”.“catalogOfferCode”.
As it is a nested JSON, so I’ve tried the following approach.
SQL Error: VALIDATION ERROR: 'CONVERT_FROMJSON' does not accept the supplied operand types: ANY
Checking the error, as it is returning ANY, I tried to convert to utf8 and extract the JSON again.
SQL Error: VALIDATION ERROR: Using CONVERT_FROM(*, 'JSON') is only supported against string literals and direct table references of types VARCHAR and VARBINARY.
ErrorOrigin: COORDINATOR
[Error Id: eef65012-b404-4397-b2ec-dadd9fe91953 on dremio-master-0.dremio-cluster-pod.-dev-dremions.svc.cluster.local:31010]
Guys,
what could be my error on it please? Any idea to guide me?
Weslley
@weslleybarboza How exactly is the JSON file coming in? In your query it’s specified in a WITH clause. However, If you use the actual JSON file instead (attached), you can query it with:
SELECT convert_from(weslleybarboza.DATA_ORDER."value",'JSON').subscription.catalogOfferCode
FROM weslleybarboza AS weslleybarboza
weslleybarboza.json.zip (2.6 KB)
HI @lenoyjacob, thanks for your reply.
My context is:
CREATE or REPLACE VIEW "data_product".bronze.glx."workflow-query".integration_context AS
select * from "src-mdb-system"."db_stg_workflow-query".integration_context;
