I am trying to pull out some fields from a JSONn string into a dataframe. I can achieve this by put each field in a dataframe then join all the dataframes like below. But is there some easier way to do this? Because this is just an simplified example and I have a lot more fields to extract in my project.
from pyspark.sql import Row
s = '{"job_id":"123","settings":{"task":[{"taskname":"task1"},{"taskname":"task2"}]}}'
json_object = json.loads(s)
# json_object
job_id_l = [Row(job_id=json_object['job_id'])]
job_id_df = spark.createDataFrame(job_id_l)
# display(job_id_df)
tasknames = []
for t in json_object['settings']["task"]:
tasknames.append(Row(taskname=t["taskname"]))
tasknames_df = spark.createDataFrame(tasknames)
# display(tasknames_df)
job_id_df.crossJoin(tasknames_df).display()
Result:
job_id taskname
123 task1
123 task2
You don't even have to explicitly define the schema here and instead, you may simply use schema_of_json
as follows:
from pyspark.sql import SparkSession
from pyspark.sql.functions import from_json, col, explode, schema_of_json, lit
spark = SparkSession.builder.getOrCreate()
s = '{"job_id":"123","settings":{"task":[{"taskname":"task1"},{"taskname":"task2"}]}}'
schema = schema_of_json(lit(s))
result_df = (
spark.createDataFrame([s], "string")
.select(from_json(col("value"), schema).alias("data"))
.select("data.job_id", explode("data.settings.task.taskname").alias("taskname"))
)
result_df.show()
# +------+--------+
# |job_id|taskname|
# +------+--------+
# | 123| task1|
# | 123| task2|
# +------+--------+
As you mentioned there are a lot more fields - this will take away some work from your hands.
An easier way is to mirroring the schema with JSON string s
and using from_json
as follows:
from pyspark.sql import SparkSession
from pyspark.sql.functions import from_json, col, explode
spark = SparkSession.builder.getOrCreate()
s = '{"job_id":"123","settings":{"task":[{"taskname":"task1"},{"taskname":"task2"}]}}'
schema = "struct<job_id:string, settings:struct<task:array<struct<taskname:string>>>>"
result_df = (
spark.createDataFrame([s], "string")
.select(from_json(col("value"), schema).alias("data"))
.select("data.job_id", explode("data.settings.task.taskname").alias("taskname"))
)
result_df.show()
# +------+--------+
# |job_id|taskname|
# +------+--------+
# | 123| task1|
# | 123| task2|
# +------+--------+