How to create a view containing a struct field with null values

1

I want to create a view that has a struct field with null values. I tried the following command

CREATE OR REPLACE VIEW myspace.testview AS ( SELECT CAST(NULL AS STRUCT<name: VARCHAR, id: INT>) as col1 )

This gave me an error Validation of view sql failed. Unknown identifier 'ROW'
n investigating further i found this which says that dremio does not have struct literals. SO instead as suggested in that link i tried the following
CREATE OR REPLACE VIEW myspace.testview AS (SELECT CONVERT_FROM('{"name":null,"id":null}','json') as col1).
This does not give me an error but it gives a completly empty struct column like below.
{}
The convert_from function seems to drop columns if it has a null value because when i give dummy values for name and id inside the json string in the above query i get the expected struct column but instead of null values i get the dummy values, which i do not want. The output i got was
{"name":"aaaa","id":"null"}

How can i create a struct type column with a specified schema but containing null values in Dremio? I am using latest version of dremio - 24.2

2

Dremio support for struct does allow null values. Normally, the struct’s schema comes from the table schema. When using CONVERT_FROM to derive the struct schema from a JSON string, you have to be careful about having a consistent schema that doesn’t change from row to row. For example, the last select * below will have to re-learn the struct schema due to “newcol”:

create table LocalTestData.json_data as
select json from (values ('{"name":"helloworld","id":123}'),('{"name":null,"id":null,"newcol":1}')) as t (json);

create view dp.test as
select convert_from(json, 'json') from LocalTestData.json_data;

select * from dp.test;