Hello @sathyasai ,
Use notebook to make this simple , as multiple Dataflow will be needed for different table write. Here is the sample code which you can use, this will work for any number of levels and will write to different tables for each level.
Please mark the question as solved and give kudos if this is helpful.
json_path = "path_to_json_file"
df = spark.read.option("multiline", "true").json(json_path)
# Define a function to flatten and write each level
def flatten_and_write(df, level, parent_field=None):
"""
Flattens a specific level of the DataFrame and writes it to a Delta table.
Args:
df (DataFrame): Input DataFrame to process.
level (int): Current hierarchy level.
parent_field (str): Field to explode or flatten.
Returns:
DataFrame: Flattened DataFrame for the next level.
"""
if parent_field:
# Explode or select nested fields
df = df.select(
*[col(c) for c in df.columns if c != parent_field], # Keep all other columns
explode(col(parent_field)).alias(f"level{level}_item") # Explode nested field
)
# Write the current level to a table
table_name = f"table_name_level{level}" # Name tables dynamically based on level
df.write.format("delta").mode("overwrite").saveAsTable(table_name)
return df
#Loop through levels and flatten iteratively
current_df = df # Start with the original DataFrame
nested_fields = ["nested_array", "nested_item.deep_nested_field"] # Replace with actual nested fields
for level, field in enumerate(nested_fields, start=1):
current_df = flatten_and_write(current_df, level, parent_field=field)
