# ERROR to transform in JSON **URL:** \ **Category:** Uncategorized\ **Created:** [November 3, 2020, 3:00pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529 "2020-11-03T15:00:35Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![lucasft](https://avatars.discourse-cdn.com/v4/letter/l/df788c/32.png) [@lucasft](https://community.dremio.com/u/lucasft)\ **Post date:** [November 3, 2020, 3:00pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/1 "2020-11-03T15:00:35Z")
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._ ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 3, 2020, 10:37pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/2 "2020-11-03T22:37:59Z")
@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? > **[How To Share A Query Profile](https://www.dremio.com/tutorials/share-query-profile-dremio/)** > > Tutorial explaining how to share a query profile in Dremio. ---
**Author:** ![lucasft](https://avatars.discourse-cdn.com/v4/letter/l/df788c/32.png) [@lucasft](https://community.dremio.com/u/lucasft)\ **Post date:** [November 5, 2020, 3:10pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/3 "2020-11-05T15:10:14Z")
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](https://community.dremio.com/uploads/short-url/i4W5cH9JNNaFyVWJqeksI0hSmie.zip) (7.8 KB) ---
**Author:** ![lucasft](https://avatars.discourse-cdn.com/v4/letter/l/df788c/32.png) [@lucasft](https://community.dremio.com/u/lucasft)\ **Post date:** [November 5, 2020, 3:15pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/4 "2020-11-05T15:15:47Z")
Atached Problem Image ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/a/ac93a2ba458475cdde029aab84cdc86913b7cc55.png) Null fields treated and after attempted to convert to JSON. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 5, 2020, 6:52pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/5 "2020-11-05T18:52:52Z")
@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](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/9/9d6e04e0644f1cf7d5dd2bf0656b9b37e6a9aa51.png) ---
**Author:** ![lucasft](https://avatars.discourse-cdn.com/v4/letter/l/df788c/32.png) [@lucasft](https://community.dremio.com/u/lucasft)\ **Post date:** [November 6, 2020, 12:17am UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/6 "2020-11-06T00:17:38Z")
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](https://community.dremio.com/uploads/short-url/miOUYNX0OdLA0MThEdEYpdhqXJq.zip) (289 Bytes) ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 6, 2020, 8:24am UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/7 "2020-11-06T08:24:22Z")
@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](https://community.dremio.com/uploads/short-url/uTmQKN0JZwQuDfCQI0RncYlPWhM.zip) (887 Bytes) ![convert-to-JSON](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/b/b706e37bb9dd972489153e0b44ab571d1bd8e87d.png) ---
**Author:** ![lucasft](https://avatars.discourse-cdn.com/v4/letter/l/df788c/32.png) [@lucasft](https://community.dremio.com/u/lucasft)\ **Post date:** [November 6, 2020, 11:28am UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/8 "2020-11-06T11:28:28Z")
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. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [November 6, 2020, 8:49pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/9 "2020-11-06T20:49:39Z")
@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](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/0/09cb6294cdee3739c4dc63fde479958139b06c74.png) ---
**Author:** ![weslleybarboza](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/weslleybarboza/32/6163_2.png) [@weslleybarboza](https://community.dremio.com/u/weslleybarboza)\ **Post date:** [December 4, 2024, 4:23pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/10 "2024-12-04T16:23:51Z")
@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](https://community.dremio.com/uploads/short-url/2IFfQDzBxmI4fxcTAtozvohRLFT.zip) (2.4 KB) ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/1/1ecdad486f22dbdb965f81dcca9174fb76c602af.png) 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 ```auto 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 ```auto 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 ---
**Author:** ![lenoyjacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/lenoyjacob/32/3474_2.png) [@lenoyjacob](https://community.dremio.com/u/lenoyjacob)\ **Post date:** [December 4, 2024, 7:32pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/11 "2024-12-04T19:32:40Z")
@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: ```auto SELECT convert_from(weslleybarboza.DATA_ORDER."value",'JSON').subscription.catalogOfferCode FROM weslleybarboza AS weslleybarboza ``` [weslleybarboza.json.zip](https://community.dremio.com/uploads/short-url/hv4frzBUgR4EObOG8xmAufGCLpg.zip) (2.6 KB) ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/8/8ccec8ff195263f518aff45d472a94dc61dc3faf.png) ---
**Author:** ![weslleybarboza](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/weslleybarboza/32/6163_2.png) [@weslleybarboza](https://community.dremio.com/u/weslleybarboza)\ **Post date:** [December 5, 2024, 8:17am UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/12 "2024-12-05T08:17:10Z")
HI @lenoyjacob, thanks for your reply. My context is: - The source is a view on Dremio pointing to a table from MariaDB ```auto 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](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/1/1b511e8e3954ed6c1086a96fde8fcb53fbed681f.png) ---
**Author:** ![nic](https://avatars.discourse-cdn.com/v4/letter/n/45deac/32.png) [@nic](https://community.dremio.com/u/nic)\ **Post date:** [June 10, 2025, 2:09pm UTC](https://community.dremio.com/t/error-to-transform-in-json/6529/13 "2025-06-10T14:09:39Z")
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) ```auto 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