Rename a column with dot in the name in my nested json file

Question

A bit new to pyspark programming:

Having a dataframe df in my databricks notebook:

|-- result: struct (nullable = true)
 |    |-- approver: struct (nullable = true)
 |    |    |-- display_value: string (nullable = true)
 |    |    |-- link: string (nullable = true)
 |    |-- comments: string (nullable = true)
 |    |-- document_id: struct (nullable = true)
 |    |    |-- display_value: string (nullable = true)
 |    |    |-- link: string (nullable = true)
 |    |-- group.assignment_group: struct (nullable = true)
 |    |    |-- display_value: string (nullable = true)
 |    |    |-- link: string (nullable = true)
 |    |-- source_table: string (nullable = true)
 |    |-- state: string (nullable = true)
 |    |-- sys_created_on: string (nullable = true)
 |    |-- sys_updated_on: string (nullable = true)

How to rename group.assignment_group to group_assignment_group because the dot is preventing the code to recognize the column when i try to flatten the file?

I am stuck on the following code and not sure how to expand the str_schema to account for the schema i have?

str_schema = "result<struct<approver:struct<display_value:string,link:string>>"

df.select(col("array_field").cast(str_schema)).printSchema()

Can you show us how your column `array_field` looks like exactly, using some sample data? — arudsekaberne, May 16 '23 at 06:06
In the mean while, I am providing the solution for you first question. — arudsekaberne, May 16 '23 at 06:06
Does this answer your question? [Rename nested field in spark dataframe](https://stackoverflow.com/questions/43004849/rename-nested-field-in-spark-dataframe) — ARCrow, May 17 '23 at 17:41

score 0 · Answer 1 · answered May 16 '23 at 06:08

Renaming the nested column which has "." in it:

from pyspark.sql.functions import struct

df \
.select("result.*") \
.withColumnRenamed(f"group.assignment_group", "group_assignment_group") \
.withColumn("result", struct("group_assignment_group")) \
.drop("group_assignment_group") \
.printSchema()

Output:

root
 |-- result: struct (nullable = false)
 |    |-- group_assignment_group: struct (nullable = true)
 |    |    |-- display: string (nullable = true)
 |    |    |-- value: string (nullable = true)

score 0 · Answer 2 · answered May 16 '23 at 06:34

You need to flatten it out, rename it and then re-nest it if needed. Here's an example of how you can do it:

# ...your code here

df.printSchema()


# Flatten the DataFrame with renamed column
df_flat = df.selectExpr("result.approver",
                        "result.comments",
                        "result.document_id",
                        "result.`group.assignment_group` as group_assignment_group",
                        "result.source_table",
                        "result.state",
                        "result.sys_created_on",
                        "result.sys_updated_on")

df_flat.printSchema()

# Create the nested DataFrame
df_nested = df_flat.select(
    F.struct(
        F.col("approver"),
        F.col("comments"),
        F.col("document_id"),
        F.col("group_assignment_group"),
        F.col("source_table"),
        F.col("state"),
        F.col("sys_created_on"),
        F.col("sys_updated_on")
    ).alias("result")
)

df_nested.printSchema()

Result:

root
 |-- result: struct (nullable = true)
 |    |-- approver: struct (nullable = true)
 |    |    |-- display_value: string (nullable = true)
 |    |    |-- link: string (nullable = true)
 |    |-- comments: string (nullable = true)
 |    |-- document_id: struct (nullable = true)
 |    |    |-- display_value: string (nullable = true)
 |    |    |-- link: string (nullable = true)
 |    |-- group.assignment_group: struct (nullable = true)
 |    |    |-- display_value: string (nullable = true)
 |    |    |-- link: string (nullable = true)
 |    |-- source_table: string (nullable = true)
 |    |-- state: string (nullable = true)
 |    |-- sys_created_on: string (nullable = true)
 |    |-- sys_updated_on: string (nullable = true)

root
 |-- approver: struct (nullable = true)
 |    |-- display_value: string (nullable = true)
 |    |-- link: string (nullable = true)
 |-- comments: string (nullable = true)
 |-- document_id: struct (nullable = true)
 |    |-- display_value: string (nullable = true)
 |    |-- link: string (nullable = true)
 |-- group_assignment_group: struct (nullable = true)
 |    |-- display_value: string (nullable = true)
 |    |-- link: string (nullable = true)
 |-- source_table: string (nullable = true)
 |-- state: string (nullable = true)
 |-- sys_created_on: string (nullable = true)
 |-- sys_updated_on: string (nullable = true)

root
 |-- result: struct (nullable = false)
 |    |-- approver: struct (nullable = true)
 |    |    |-- display_value: string (nullable = true)
 |    |    |-- link: string (nullable = true)
 |    |-- comments: string (nullable = true)
 |    |-- document_id: struct (nullable = true)
 |    |    |-- display_value: string (nullable = true)
 |    |    |-- link: string (nullable = true)
 |    |-- group_assignment_group: struct (nullable = true)
 |    |    |-- display_value: string (nullable = true)
 |    |    |-- link: string (nullable = true)
 |    |-- source_table: string (nullable = true)
 |    |-- state: string (nullable = true)
 |    |-- sys_created_on: string (nullable = true)
 |    |-- sys_updated_on: string (nullable = true)

Rename a column with dot in the name in my nested json file

2 Answers2