from_json Pyspark SQL function: default value for not found keys?

Question

I'm using from_json Pyspark SQL function as usual, e.g.:

>>> import pyspark.sql.types as t
>>> from pyspark.sql.functions import from_json
>>> df = sc.parallelize(['{"a":1}', '{"a":1, "b":2}', '{"a":1, "b":2, "c":3}']).toDF(t.StringType())
>>> df.show(3, False)
+---------------------+
|value                |
+---------------------+
|{"a":1}              |
|{"a":1, "b":2}       |
|{"a":1, "b":2, "c":3}|
+---------------------+

>>> schema = t.StructType([t.StructField("a", t.IntegerType()), t.StructField("b", t.IntegerType()), t.StructField("c", t.IntegerType())])
>>> df.withColumn("json", from_json("value", schema)).show(3, False)
+---------------------+---------+
|value                |json     |
+---------------------+---------+
|{"a":1}              |[1,,]    |
|{"a":1, "b":2}       |[1, 2,]  |
|{"a":1, "b":2, "c":3}|[1, 2, 3]|
+---------------------+---------+

Please observe those keys not present in the JSON but specified in the schema have a parsed value of null (or some kind of empty value ?).

How can this be avoided? I mean, is there a way to set a default value to from_json? Or have I to add such a default value in a post-process of the dataframe?

Thanks!

for me it produces the correct output: [1,null,null] for the first row etc. so maybe you have some options set for the from_json which are different from default? — gaw
Hi, @gaw, thanks for answering. I'm aware the behaviour is correct. What I need to know is if there is some way of setting default value for a key not present in the JSON. E.g. how could I set that instead of adding a nullvalue to the list, a 0.0 value must be added. — frb
I think it is not possible directly, the documentation states it has the same option parameters as the normal json data source but there is no option for nullValue or something similar. So probably you need to do it in a post processing step — gaw

howie howie · Accepted Answer · 2019-04-11T20:27:46

You can try

df = self.spark.createDataFrame(['{"a":1}', '{"a":1, "b":2}', '{"a":1, "b":2, "c":3}'], StringType())

df.show(3, False)
df = df.withColumn("a", get_json_object("value", '$.a')) \
       .withColumn("b",when(get_json_object("value", '$.b').isNotNull(), get_json_object("value", '$.b')).otherwise(0)) \
       .withColumn("c",when(get_json_object("value", '$.c').isNotNull(), get_json_object("value", '$.c')).otherwise(0))

df.show(3, False)


+---------------------+
|value                |
+---------------------+
|{"a":1}              |
|{"a":1, "b":2}       |
|{"a":1, "b":2, "c":3}|
+---------------------+

+---------------------+---+---+---+
|value                |a  |b  |c  |
+---------------------+---+---+---+
|{"a":1}              |1  |0  |0  |
|{"a":1, "b":2}       |1  |2  |0  |
|{"a":1, "b":2, "c":3}|1  |2  |3  |
+---------------------+---+---+---+

from_json Pyspark SQL function: default value for not found keys?

1 Answers