How to convert the dataframe column type from string to (array and struct) in spark

Question

I have a Dataframe with the following schema, where 'name' is a string type and the value is a complex JSON with Array and struct.

Basically with string datatype i couldn't able to parse the data and write into rows. So I am trying to convert datatype and apply explode to parse the data.

Current:
root
|--id: string (nullable = true)
|--partitionNo: string (nullable = true)
|--name: string (nullable = true)

After conversion:

Expected:
root
|id: string (nullable = true)
|partitionNo: string (nullable = true)
|name: array (nullable = true)
|     |-- element: struct (containsNull = true) 
|     |    |-- extension: array (nullable = true)
|     |    |    |-- element: struct (containsNull = true)
|     |    |    |    |-- url: string (nullable = true)
|     |    |    |    |-- valueMetadata: struct (nullable = true)
|     |    |    |    |-- modifiedDateTime: string (nullable = true)
|     |    |    |    |-- code: string (nullable = true)
|     |    |-- lastName: string (nullable = true)
|     |    |-- firstName: array (nullable = true)
|     |    |    |-- element: string (containsNull = true)

mck · Accepted Answer · 2021-01-08 07:49:04Z

1

You can use from_json, but you need to provide a schema, which can be automatically inferred using a spaghetti code... because from_json only accepts a schema in the form of lit:

val df2 = df.withColumn(
    "name",
    from_json(
        $"name",
        // the lines below generate the schema
        lit(
            df.select(
                schema_of_json(
                    lit(
                        df.select($"name").head()(0)
                    )
                )
            ).head()(0)
        )
        // end of schema generation
    )
)

edited Jan 8, 2021 at 7:49

answered Jan 8, 2021 at 7:43

mck

42.7k13 gold badges44 silver badges62 bronze badges

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

2 Comments

timothyzhang Over a year ago

I think from_json could support a schema as DataType.

mck Over a year ago

@timothyzhang yes, but I don't want to type it manually for a complex schema...

Collectives™ on Stack Overflow

How to convert the dataframe column type from string to (array and struct) in spark

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