ERROR to transform in JSON

1

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.

2

@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?

3

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)

4

Atached Problem Image

image
image1440×341 28.8 KB

Null fields treated and after attempted to convert to JSON.

5

@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

convert-json
convert-json2714×606 48.4 KB

6

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)

7

@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)

convert-to-JSON
convert-to-JSON1514×474 38.7 KB

8

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.

9

@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

json
json2030×472 40 KB

1 Like
10

@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)

image
image1906×979 69.6 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.

  1. CONVERT_FROMJSON(j.json).“DATA_ORDER”.“value” convert1
  2. CONVERT_FROMJSON(CONVERT_FROMJSON(j.json).“DATA_ORDER”.“value”) convert2
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.

  1. CONVERT_FROM(CONVERT_FROMJSON(j.json).“DATA_ORDER”.“value”, ‘utf8’) convert3
  2. CONVERT_FROMJSON(CONVERT_FROM(CONVERT_FROMJSON(j.json).“DATA_ORDER”.“value”, ‘utf8’)) convert4
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

11

@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)

image
image1746×490 41.8 KB

12

HI @lenoyjacob, thanks for your reply.
My context is:

  • The source is a view on Dremio pointing to a table from MariaDB
CREATE or REPLACE VIEW "data_product".bronze.glx."workflow-query".integration_context AS
select * from "src-mdb-system"."db_stg_workflow-query".integration_context;
  • The column type is VARCHAR?
    image
13

The data source can’t be a view, it has to be a table. Which is a crazy restriction.

Although the below fails for me regardless (version 25.2)

create table test_json as select CAST('["some_json"]' AS VARCHAR) as col;
select data_type from INFORMATION_SCHEMA.COLUMNS where table_name = 'test_json';
--> CHARACTER VARYING
select col, CONVERT_FROM(col, 'json') as my_array
from test_json
;
--> New field in the schema found. Please reattempt the query. Multiple attempts may be necessary to fully learn the schema.

There seems to be some fundamental problems with the process of non-structured data