Extract Schema from nested Json-String column in Pyspark

Question

Assuming I have the following table:

body
{"Day":1,"vals":[{"id":"1", "val":"3"}, {"id":"2", "val":"4"}]}

My goal is to write down the schema in Pyspark for this nested json column. I've tried the following two things:

schema = StructType([
  StructField("Day", StringType()),
  StructField(
  "vals",
  StructType([
    StructType([
      StructField("id", StringType(), True),
      StructField("val", DoubleType(), True)
    ])
    StructType([
      StructField("id", StringType(), True),
      StructField("val", DoubleType(), True)
    ])
  ])
  )
])

Here I get the error that of

'StructType' object has no attribute 'name'

Another approach was to declare the nested Arrays as ArrayType:

schema = StructType([
  StructField("Day", StringType()),
  StructField(
  "vals",
  ArrayType(
    ArrayType(
        StructField("id", StringType(), True),
        StructField("val", DoubleType(), True)
      , True)
    ArrayType(
        StructField("id", StringType(), True),
        StructField("val", DoubleType(), True)
      , True)
    , True)
  )
])

Here I get the following error:

takes from 2 to 3 positional arguments but 5 were given

Which propably comes from the array only taking the Sql type as an argument.

Can anybody tell me what their approach would be to create the schema, since I'm a super newbie to the whole topic..

Guillermo Garcia · Accepted Answer · 2021-11-08 14:13:03Z

1

This is the structure you are looking for:

Data = [
    (1, [("1","3"), ("2","4")])
  ]

schema = StructType([
        StructField('Day', IntegerType(), True),
        StructField('vals', ArrayType(StructType([
            StructField('id', StringType(), True),
            StructField('val', StringType(), True)
            ]),True))
         ])
df = spark.createDataFrame(data=Data,schema=schema)
df.printSchema()
df.show(truncate=False)

This will get you the next output:

root
 |-- Day: integer (nullable = true)
 |-- vals: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- id: string (nullable = true)
 |    |    |-- val: string (nullable = true)

+---+----------------+
|Day|vals            |
+---+----------------+
|1  |[{1, 3}, {1, 3}]|
+---+----------------+

edited Nov 8, 2021 at 14:13

answered Nov 3, 2021 at 17:46

Guillermo Garcia

5836 silver badges12 bronze badges

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

2 Comments

Afrobeta Over a year ago

thank you for your answer! I saw that I made a mistake when creating the example table. Both Structypes belong to the same Structfield "vals". I still didn't really find a solution for this..

Guillermo Garcia Over a year ago

My bad, I thought you were missing just the name for the second nested StructType. Check my new answer! Hope it helps

Collectives™ on Stack Overflow

Extract Schema from nested Json-String column in Pyspark

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related