Forum Discussion
How to reference a JSON array element with no name using a Notebook or OPENROWSET?
Hi Richtpt ,
Thanks for reaching out to the Microsoft Fabric Community forum.If you just want to see the data in the same nested format as it appears in the JSON, you can run:
df.select("PRCCollection.Modifiers").show()Since both PRCCollection and Modifiers are arrays, Spark will display the result as something like [[US]]. That simply means there is an array inside another array.
If you’d prefer to flatten the structure and extract the actual value, you can use explode() to expand each array level:
from pyspark.sql.functions import explode df2 = df.withColumn("prc", explode("PRCCollection")) \ .withColumn("Modifier", explode("prc.Modifiers")) df2.select("Modifier").show()Here, the first explode() opens up the PRCCollection array, and the second one expands the Modifiers array inside it. The output will then show the value directly (for example US) instead of the nested [[US]] structure.
For reference this is the output of df.schema() for my data
I hope this information helps. Please do let us know if you have any further queries.
Thank you
Hi Richtpt ,
Thanks for reaching out to the Microsoft Fabric Community forum.
If you just want to see the data in the same nested format as it appears in the JSON, you can run:
df.select("PRCCollection.Modifiers").show()Since both PRCCollection and Modifiers are arrays, Spark will display the result as something like [[US]]. That simply means there is an array inside another array.
If you’d prefer to flatten the structure and extract the actual value, you can use explode() to expand each array level:
from pyspark.sql.functions import explode
df2 = df.withColumn("prc", explode("PRCCollection")) \
.withColumn("Modifier", explode("prc.Modifiers"))
df2.select("Modifier").show()Here, the first explode() opens up the PRCCollection array, and the second one expands the Modifiers array inside it. The output will then show the value directly (for example US) instead of the nested [[US]] structure.
For reference this is the output of df.schema() for my data
I hope this information helps. Please do let us know if you have any further queries.
Thank you
Thanks, that helps a lot and I am able to see the Modifiers values.
That code helped a lot, but I still wasn't getting what I wanted. Probably because I'm extremely new to PySpark (as in this is my first time using it). I finally asked Grok how to flatten this. I got back several responses including this one that worked perfect. Wanted to share to close this up. Thanks much for the help!
from pyspark.sql.functions import col, explode_outer, explode
df = spark.read.json("Files/test.json")
df_deep = (
df
.select("*", explode_outer("PRC0Collection").alias("PR"))
.select(
"*",
col("PR.*"),
explode_outer(col("PR.Modifiers")).alias("Modifier")
)
.drop("PRC0Collection", "PR")
.drop("PRC0Collection", "Modifiers")
)
display(df_deep)btw, I forgot to post my json earlier. Here it is:
{ "AGAG_ID": "PHKETCHMC", "AGIP_CAP_IND": "", "CSPI_HIOS_ID_NVL": "73836AK09999", "CSPI_ID": "AMT01234", "GRGR_CK": 12345, "IPCD_ID": "R9999", "PRC0Collection": [ { "Cache_ID": "1462da8b", "Exp_Date": "12/31/9999 00:00:00.000", "Modifiers": [ "US" ], "PSCD_ID": "11", "Price": 16336.44, "Type": "negotiated" }, { "Cache_ID": "156eab34", "Exp_Date": "12/31/9999 00:00:00.000", "Modifiers": [ "UQ" ], "PSCD_ID": "11", "Price": 24499.76, "Type": "negotiated" } ], "PRPR_ID": "P00000099999", "SEDF_CAP_IND": "N" }Hi Richtpt ,
We really appreciate your efforts and for letting us know the update on the issue.Please continue using fabric community forum for your further assistance.
Thank you
