topic Re: when and otherwise issue in Data Engineering

when and otherwise issue

SailajaB — Wed, 23 Feb 2022 16:49:14 GMT

Hi,

Here in our scenario we are reading json files as input and it contains nested structure. Few of the attributes are array type struct. Where we need to change name of nested ones. So we created a new structure and doing cast.

We are facing below problem while doing cast

For ex : test is a arry type struct

{"test":[{"nestedattr1":"df","columnfield":"er"}]

we need above one as

{"test":[{"nestedAttr1":"df","columnField":"er"}]

So we defined a new structure and applying cast but when we are receiving test as an empty array {"test":[]} the casting is getting failed. So we are trying to apply below code but its not working

df = df.withColumn("test",when(size(df.test)>0,col("test").cast(newteststruct)).otherwise(df.test))

error : cannot resolve '`test`' due to data type mismatch: cannot cast array<string> to array<struct>

Please add your comment to avoid this issue

Re: when and otherwise issue

AmanSehgal — Thu, 24 Feb 2022 03:53:02 GMT

Can you provide the structure that you're using?

Also, a more elaborate sample input and output.

Re: when and otherwise issue

SailajaB — Thu, 24 Feb 2022 08:58:49 GMT

Hi,

Thank you for the reply..

We are using below structure to change/cast the array type struct with nested new names

newteststruct= ArrayType(StructType([

StructField(""nestedAttr1" ,StringType()),

StructField("columnField" ,StringType())]))

Input will come from other source in json format and we are reading into databricks as df.

So here we are applying the schema level transformations as per business to get output in target schema.

So while casting we are facing an issue where when we get an empty array through the i/p extract.

Thank you

Re: when and otherwise issue

AmanSehgal — Thu, 24 Feb 2022 11:36:05 GMT

Is it possible for you to replace {"test":[]} with {"test":[{"nestedattr1":"","columnfield":""}]} ?

Because I think THEN and ELSE expressions should have same type.

Re: when and otherwise issue

SailajaB — Thu, 24 Feb 2022 11:51:39 GMT

Because I think THEN and ELSE expressions should have same type. I think yes

But we don't need to convert {"test":[]} to {"test":[{"nestedattr1":"","columnfield":""}]}

If we get test as an empty array we should avoid this conversion

If we get test as {"test":[{"nestedattr1":"df","columnfield":"er"}] then we have to proceed with conversion

Is there any way to achieve this? mostly at schema level instead of at each column level

Re: when and otherwise issue

SailajaB — Fri, 25 Feb 2022 14:10:08 GMT

We used below condition to resolve the issue

if dict(df.dtypes)['test'] != 'array<string>':

df = df.withColumn("test",col("test").cast(newteststruct))

Thank you