Unkey Streams - remove key from projection

Hi all,

i would like to know if it is possible to unkey a stream
For example i have a source stream

create stream stream_with_key 
(key_column varchar key, no_key_column varchar) 
with (kafka_topic='stream_with_key',value_format='avro',partitions=10,replicas=1);

and a target stream

create stream stream_with_no_key 
(old_key_column varchar, no_key_column varchar) 
with (kafka_topic='stream_with_no_key',value_format='avro',partitions=10,replicas=1);

If i want to insert data with either

insert into STREAM_WITH_NO_KEY 
select 
as_value(key_column) as OLD_KEY_COLUMN,
NO_KEY_COLUMN 
from STREAM_WITH_KEY 
emit changes;

or

insert into STREAM_WITH_NO_KEY 
select 
key_column as OLD_KEY_COLUMN,
NO_KEY_COLUMN 
from STREAM_WITH_KEY 
emit changes;

i receive either a schema mismatch (2nd example) or a missing key from projection (1st example)

The background why we try do this is more complicated :slight_smile:

Thanks and Cheers,
Marcus