Forum Discussion

ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
2 years ago
Solved

PySpark Notebook to process complex JSON

Hello. I am using a PySpark notebook in Fabric to process incoming JSON files. The Notebook reads the JSON file into a base dataframe, then from there parse it out into two other dataframes that get ...
data engineering
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ToddChitt ,

    I tried to do some repro around your case, it is working perfectly fine.
    Can you please find the code below,

     

     

     

    Sample Json:

    [
  {
    "id": "00000001-0000-0000-0000-000000000000",
    "positionData": {
      "manager": {
        "id": "00000002-0000-0000-0000-000000000000",
        "employeeNumber": "1234"
      }
    }
  },
  {
    "id": "00000001-0000-0000-0000-000000000000",
    "positionData": {
      "manager": null
    }
  }
]



    Sample Code:

    from pyspark.sql.types import StructType, StructField, StringType, ArrayType
from pyspark.sql.functions import col, coalesce, lit

# Define the schema for the nested objects
schema = StructType([
    StructField("id", StringType(), True),
    StructField("positionData", StructType([
        StructField("manager", StructType([
            StructField("id", StringType(), True),
            StructField("employeeNumber", StringType(), True)
        ]), True)
    ]), True)
])

# Read JSON data with multiline option and schema
df = spark.read.option("multiline", "true").json("Files/testing.json", schema=schema)

df = df.withColumn("ManagerId", coalesce(col("positionData.manager.id"), lit(None)))

display(df)

     
    Please try this and let me know if you have further queries.

ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
2 years ago

I have also tried defining a function like this:

# Function to handle null values in various fields
def handle_nulls(entity_name, field_name😞
    try:
        #return when(col(f"{entity_name}").isNull(), None).otherwise(col(f"{entity_name}.{field_name}"))
        return  coalesce(when(col(f"{entity_name}").isNull(), lit("NULL Value string")).otherwise(None), col(f"{entity_name}.{field_name}"))
    except: return None
And then call that function for a column like this:
handle_nulls("positionData.manager", "id").alias("ManagerId"),
But that generates the same error, which I don't really understand because of the try/except block. Maybe I'm not structuring that portion properly. I would think that if there is an error inside the TRY, then the EXCEPT takes over. 
What am I missing on this one?
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ToddChitt ,

    I tried to do some repro around your case, it is working perfectly fine.
    Can you please find the code below,

     

     

     

    Sample Json:

    [
  {
    "id": "00000001-0000-0000-0000-000000000000",
    "positionData": {
      "manager": {
        "id": "00000002-0000-0000-0000-000000000000",
        "employeeNumber": "1234"
      }
    }
  },
  {
    "id": "00000001-0000-0000-0000-000000000000",
    "positionData": {
      "manager": null
    }
  }
]



    Sample Code:

    from pyspark.sql.types import StructType, StructField, StringType, ArrayType
from pyspark.sql.functions import col, coalesce, lit

# Define the schema for the nested objects
schema = StructType([
    StructField("id", StringType(), True),
    StructField("positionData", StructType([
        StructField("manager", StructType([
            StructField("id", StringType(), True),
            StructField("employeeNumber", StringType(), True)
        ]), True)
    ]), True)
])

# Read JSON data with multiline option and schema
df = spark.read.option("multiline", "true").json("Files/testing.json", schema=schema)

df = df.withColumn("ManagerId", coalesce(col("positionData.manager.id"), lit(None)))

display(df)

     
    Please try this and let me know if you have further queries.