Type errors when using convert_from

[sp2barrels]

Hi All - wondering if anyone has seen this and what a useful workaround would be.

I’ve noticed that convert from does not correctly convert JSON strings into objects with correct typing, an example:

Query:
select (convert_from('{name: "mike", type: 124, action: "view", adminUser: false}', 'json'))

Query result:
{"name": "mike", "type": "124", "action": "view", "adminUser": "false"}

In this case, you can see both Booleans and Ints are converted to varchar.

I encountered this when trying to insert data into a semistruct field with table definition:

create table lakehouse.test.interaction_logs (
    id varchar(128), 
    interaction array(struct<"name": varchar, "type": int, "action": varchar, "adminUser": boolean>)
);

And insert statement:

Insert into lakehouse.test.interaction_logs VALUES
 ('b86a3524-ebd9-4886-af7a-859a8879c369', convert_from('[{name: "view-document", type: 124, action: "view", adminUser: false}]', 'json'))

Dremio returns the following error message:

Table schema(id::varchar, interaction::list<struct<name::varchar, type::int32, action::varchar, adminUser::boolean>>) doesn’t match with query schema(id::varchar, interaction::list<struct<name::varchar, type::varchar, action::varchar, adminUser::varchar>>)

[sp2barrels]

@balaji.ramaswamy Would you happen to have a workaround for this?

[balaji.ramaswamy]

@sp2barrels I get Boolean

SELECT "EXPR$0", nested_0."EXPR$0".adminUser AS adminUser
FROM (
  select (convert_from('{name: "mike", type: 124, action: "view", adminUser: false}', 'json'))
) nested_0;