Array of JSON to Dataframe in pyspark

Question

I'm having some issues with reading items from Json file, and having some issues getting the data out of it to columns.

{
   "sample":[
      {
         "value":"Red",
         "id":"1"
      },
      {
         "value":"green",
         "id":"2"
      },
      {
         "value":"orange",
         "id":"3"
      }
   ],
   "scientific_names":"Buxus microphylla",
   "gender":"bushes",
   "examples":"Oleander"
}

I would like to get this JSON objects in a dataframe like

+------------------+-------------------+--------+----------+--+
| sample           | scientific_names  | gender | examples |  |
+------------------+-------------------+--------+----------+--+
| Red,green,orange | Buxus microphylla | bushes | Oleander |  |
+------------------+-------------------+--------+----------+--+
|                  |                   |        |          |  |
+------------------+-------------------+--------+----------+--+
|                  |                   |        |          |  |

Anyone can help me please? Thank you!

pltc · Accepted Answer · 2021-09-16 17:27:18Z

You can simply pass a proper schema and select value column

# a.json
# {...} your full json sample

schema = T.StructType([
    T.StructField('sample', T.ArrayType(T.StructType([
        T.StructField('id', T.StringType()),
        T.StructField('value', T.StringType())
    ]))),
    T.StructField('scientific_names', T.StringType()),
    T.StructField('gender', T.StringType()),
    T.StructField('examples', T.StringType()),
])

(spark
    .read
    .json('a.json', schema=schema, multiLine=True)
    .withColumn('sample', F.col('sample.value'))
    .show(10, False)
)

# Output
# +--------------------+-----------------+------+--------+
# |sample              |scientific_names |gender|examples|
# +--------------------+-----------------+------+--------+
# |[Red, green, orange]|Buxus microphylla|bushes|Oleander|
# +--------------------+-----------------+------+--------+

Collectives™ on Stack Overflow

Array of JSON to Dataframe in pyspark

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related