remove null array field from dataframe while converting it to JSON

Question

Is there any method where i can create a json from a spark dataframe by not using those fields which are null:

Lets suppose i have a data frame:

+-------+----------------+

|   name|       hit_songs|

+-------+----------------+

|beatles|[help, hey jude]|

|  romeo|      [eres mia]|

| juliet|      null      |

+-------+----------------+

i want to convert it into a json like:

[{
name: "beatles",
hit_songs: [help, hey jude]
},
{
name: "romeo",
hit_songs: [eres mia]
},
{
name: "juliet"
}
]

i dont want the field hit_songs in the json_object if its value is null

Check this stackoverflow.com/questions/48261746/… stackoverflow.com/questions/47830915/… — Mohd Bilal
– Mohd Bilal, Commented May 16, 2020 at 10:29

notNull · Accepted Answer · 2020-05-16 12:37:28Z

0

Use to_json function for this case.

df=spark.createDataFrame([("beatles",["help","hey juude"]),("romeo",["eres mia"]),("juliet",None)],["name","hit_songs"])

from pyspark.sql.functions import *

df.groupBy(lit(1)).\
agg(collect_list(to_json(struct('name','hit_songs'))).alias("json")).\
drop("1").\
show(10,False)
#+-------------------------------------------------------------------------------------------------------------------+
#|json                                                                                                               |
#+-------------------------------------------------------------------------------------------------------------------+
#|[{"name":"beatles","hit_songs":["help","hey juude"]}, {"name":"romeo","hit_songs":["eres mia"]}, {"name":"juliet"}]|
#+-------------------------------------------------------------------------------------------------------------------+

#using toJSON function.
df.groupBy(lit(1)).\
agg(collect_list(struct('name','hit_songs')).alias("json")).\
drop("1").\
toJSON().\
collect()
#[u'{"json":[{"name":"beatles","hit_songs":["help","hey juude"]},{"name":"romeo","hit_songs":["eres mia"]},{"name":"juliet"}]}']

answered May 16, 2020 at 12:37

notNull

31.8k4 gold badges41 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shruti Gusain Over a year ago

I am sorry, this did not work, as i am further doing the repartition. Me along with my team have moved to a different approach. Can you help me on the other solution. Your previous solution worked so i can rely on you for this too: stackoverflow.com/questions/61838445/…

notNull Over a year ago

@ShrutiGusain, repartition doesn't cause any issues.. and I'm not sure what error you are getting using to_json function.

Shruti Gusain Over a year ago

when i am converting my dataframe using tojson it shows the value as { name: "juliet", hit_songs: null } which is causing null exception in api of our application

notNull Over a year ago

to_json function doesn't preserve null's, if you have null string in your dataframe convert to null then convert back to json object. as in my example i have hit_songs as null and hit_songs is not part of juliet json object.

Collectives™ on Stack Overflow

remove null array field from dataframe while converting it to JSON

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related