Dataframe schema change based on filtered values while reading JSON

user_1449874 0
Viewed 139

I have a case where I am trying to read a json file consisting an overall structure

overall json file schema:

root
 |-- event: string (nullable = true)
 |-- eventid: string (nullable = true)
 |-- property1: struct (nullable = true)
 |    |-- sub_property1: string (nullable = true)
 |    |-- sub_property2: string (nullable = true)
 |-- property2: struct (nullable = true)
 |    |-- sub_property1: string (nullable = true)
 |    |-- sub_property2: string (nullable = true)
 |    |-- sub_property3: string (nullable = true)

Now depending on the type of event the properties might be populated or not. For event = 'facebook_login' the schema would be

facebook_login schema:

root
 |-- event: string (nullable = true)
 |-- eventid: string (nullable = true)
 |-- property1: struct (nullable = true)
 |    |-- sub_property1: string (nullable = true)
 |-- property2: struct (nullable = true)
 |    |-- sub_property1: string (nullable = true)
 |    |-- sub_property3: string (nullable = true)

and when event = 'google_login' the schema would be

google_login schema:

root
 |-- event: string (nullable = true)
 |-- eventid: string (nullable = true)
 |-- property1: struct (nullable = true)
 |    |-- sub_property2: string (nullable = true)
 |-- property2: struct (nullable = true)
 |    |-- sub_property2: string (nullable = true)
 |    |-- sub_property3: string (nullable = true)

The problem I am facing is when I read this file and try to filter events it gives the same schema as the overall file schema (of course giving null/missing values for missing properties)

json_df = df.read.json(json_file_path)
fb_login_df = json_df.filter("event='facebook_login'")
google_login_df = json_df.filter("event='google_login'")
fb_login_df.printSchema()
google_login_df.printSchema() # same schema output for both

Is there a way we can achieve this ? to have different schema structures based on the filtered value ?

P.S : I was thinking having custom schemas defined for each event type but that would not scale since there are thousands of different event types in the json file

pyspark apache-spark-sql apache-spark python json
2 Answers

give the schema when you read the json:

for a try.json which contains this:

[{"event":"a","eventid":"mol","property1":{"sub1":"ex ","sub2":"ni"},"property2":{"sub1":"exe","sub2":"ad","sub3":"qui"}},{"event":"s","eventid":"cul","property1":{"sub1":"et ","sub2":"ame"},"property2":{"sub1":"o","sub2":"q","sub3":"m"}}]

you can do:

from pyspark.sql.types import StructType,StructField, StringType, IntegerType
structureSchema1 = StructType([
StructField('event', StringType(), True),
        StructField('eventid', StringType(), True),
        StructField('property1', StructType([
             StructField('sub1', StringType(), True)
             ])),
        StructField('property2', StructType([
             StructField('sub1', StringType(), True),
             StructField('sub3', StringType(), True)
             ]))])
structureSchema2 = StructType([
        StructField('event', StringType(), True),
        StructField('eventid', StringType(), True),
        StructField('property1', StructType([
             StructField('sub2', StringType(), True)
             ])),
        StructField('property2', StructType([
             StructField('sub2', StringType(), True),
             StructField('sub3', StringType(), True)
             ]))])
df1 = spark.read.schema(structureSchema1).json("./try.json")
df2 = spark.read.schema(structureSchema2).json("./try.json")
user_101748660

I suggest reading the data in as text. 1 row = 1 event. Filter the data. (Google/Facebook) Use [from_json][1] to create the schema as needed. You will have to store the data in it's own table as you can't mix schema's.

user_135351200
Related