Explode function to flatten the JSON

David_Billa
New Contributor III

‎01-11-2025 09:41 AM - edited ‎01-11-2025 09:49 AM

I've the DDL as below.

 

Create or replace table test 
(
prices ARRAY<STRUCT<Ord:STRING:,Vndr:STRING,Prc:STRING>>
)
using delta
location "path"

 

Now I want to flatten the JSON and I've tried as below but it's throwing an error, "[UNRESOLVED.COLUMN.WITH_SUGGESTION]'

 

select explode(output.prices) as prices
from table

 

If I use 'select prices.ord from table' then I see the value as ["5"] with square brackets. I don't want to see the square brackets in the result.

Any help?

0 Kudos

Walter_C
Databricks Employee
Databricks Employee

‎01-11-2025 09:47 AM

Do you get the same result if you remove the output.?

View solution in original post

hari-prasad
Valued Contributor II

‎01-11-2025 11:54 PM

Hi @David_Billa ,

You can use following from_json function in spark which can convert struct into individual column. Refer this link https://spark.apache.org/docs/3.4.0/api/python/reference/pyspark.sql/api/pyspark.sql.functions.from_....

Also, you can use dot notation to access individual column from struct, like df.withColumn('Ord', col('prices.Ord')).

 

Regards,
Hari Prasad



Regards,
Hari Prasad
0 Kudos