topic Re: Explode function to flatten the JSON in Data Engineering

Explode function to flatten the JSON

David_Billa — Sat, 11 Jan 2025 17:49:28 GMT

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?

Re: Explode function to flatten the JSON

Walter_C — Sat, 11 Jan 2025 17:47:13 GMT

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

Re: Explode function to flatten the JSON

hari-prasad — Sun, 12 Jan 2025 07:54:34 GMT

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_json.html.

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

Regards,
Hari Prasad