topic Notebook to read complex JSON array in Fabric platform

Notebook to read complex JSON array

ToddChitt — Mon, 08 Apr 2024 14:43:24 GMT

Hello all. I am trying to learn PySpark from this website:

https://sparkbyexamples.com/pyspark/pyspark-alias-column-examples/

Good info, but I am stuck. I borrowed the simple JSON code that looks like this:

[

{ "RecordNumber": 2, "Zipcode": 704 },
{ "RecordNumber": 10, "Zipcode": 709 }
]

And I can read that in a data frame. But unfortunately, my data has an array name at the top, like this:

{ "data": [
{ "RecordNumber": 2, "Zipcode": 704 },
{ "RecordNumber": 10, "Zipcode": 709 }

] }

I read these two items into 2 data frames, then do two selects in PySpark:

dfJSON1 = df1.select( col("RecordNumber"), col("Zipcode"))

dfJSON2 = df2.select( col("data.RecordNumber"), col("data.Zipcode"))

dfJSON1.show()

dfJSON2.show()

The two results:

What am I missing to get the second data frame to show two records, similar to the first?

This can't be that hard. What am I missing?

Thanks in advance.

Re: Notebook to read complex JSON array

Expiscornovus — Mon, 08 Apr 2024 15:56:57 GMT

Hi ToddChitt,

Wouldn't it be possible to use a couple of SQL functions like explode and col for this?

I found that suggested approach in this blog: https://medium.com/towards-data-engineering/transforming-json-to-lakehouse-tables-with-microsoft-fabric-notebooks-a-step-by-step-guide-d14458b1c284

Below is an example based on your json code in one of my test notebooks.

# Apply transformation to the dataframe from pyspark.sql.functions import col, explode exploded_df = df.select(explode(col("data")).alias("data")) tf_df = exploded_df.select( col("data.RecordNumber").alias("RecordNumber"), col("data.Zipcode").alias("Zipcode") ) display(tf_df) dfJSON1 = tf_df.select( col("RecordNumber"), col("Zipcode")) dfJSON1.show()

Re: Notebook to read complex JSON array

ToddChitt — Mon, 08 Apr 2024 16:34:33 GMT

Expiscornovus Thanks for the quick response.

Your sample code worked great. Now it's up to me to figure out how to shred the multi-level nested arrays in my actual JSON documents.

I will check out that blog and try to learn a little more about PySpark.

Thanks

Re: Notebook to read complex JSON array

Anonymous — Thu, 11 Apr 2024 08:06:23 GMT

Glad to know you got some insights over your query. Please continue using Fabric Community on your further queries.