Add selected columns from complex exploding dataframe to another dataframe in pyspark

As sample data I have:

{
    "GeneralInformation":{
        "ID":"00001",
        "WebLinksInfo":{
            "LastUpdated":"2019-10-27",
            "WebSite":{
                "Type":"Home Page",
                "text":"https://www.aaaa.com/"
            }
        },
        "TextInfo":{
            "Text":[
                {
                    "Type":"Business",
                    "updated_at":"2018-09-14",
                    "unused_field":"en-US",
                    "Description":"Lorem ipsum dolor sit amet, consectetur adipiscing elit, laborum."
                },
                {
                    "Type":"Financial",
                    "updated_at":"2022-08-26",
                    "unused_field":"en-US",
                    "Description":"Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat."
                }
            ]
        },
        "Advisors":{
            "Auditor":{
                "Code":"AAA",
                "Name":"Aristotle"
            }
        }
    }

}

I have this schema dataframe:

root
 |-- GeneralInformation: struct (nullable = true)
 |    |-- Advisors: struct (nullable = true)
 |    |    |-- Auditor: struct (nullable = true)
 |    |    |    |-- Code: string (nullable = true)
 |    |    |    |-- Name: string (nullable = true)
 |    |-- ID: string (nullable = true)
 |    |-- TextInfo: struct (nullable = true)
 |    |    |-- Text: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- Description: string (nullable = true)
 |    |    |    |    |-- updated_at: string (nullable = true)
 |    |    |    |    |-- Type: string (nullable = true)
 |    |    |    |    |-- unused_field: string (nullable = true)
 |    |-- WebLinksInfo: struct (nullable = true)
 |    |    |-- LastUpdated: string (nullable = true)
 |    |    |-- WebSite: struct (nullable = true)
 |    |    |    |-- text: string (nullable = true)
 |    |    |    |-- Type: string (nullable = true)

I am just interest on extracting ID and TextInfo.Text.Description and updated_at

Right now I extract the ID like this:

    df = spark.read.json('my_file_path')

    new_df = df.select(col('GeneralInformation.ID'))
    new_df = new_df.join(df.select(col('GeneralInformation.TextInfo.Text')))

End up with this schema, but I want to have just ID, Description and Updated_at on root Level, and just 1 record no matter how many descriptions exist on the Text array based on the Type value, so if Type value is 'Business', get description and updated_at for that ID.

root
 |-- ID: string (nullable = true)
 |-- Text: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- Description: string (nullable = true)
 |    |    |-- updated_at: string (nullable = true)
 |    |    |-- Type: string (nullable = true)
 |    |    |-- unused_field: string (nullable = true)

Expected output:

{
    "ID": "00001",
    "Description": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, laborum.",
    "Updated_at": "2018-09-14"
}

For example, if I use:

df = df.withColumn('description', F.when(
    df['Text'][0]['Type'] == 'Business', lit(df['Text'][0]['Description'])))

I get the description out as a new column on the dataframe that I can keep and delete TextInfo, but it won't assure me that it will look on other elements of the Text array.

Try by accessing array with the index.

Example:

from pyspark.sql.functions import *
js_str = """{
    "GeneralInformation":{
        "ID":"00001",
        "WebLinksInfo":{
            "LastUpdated":"2019-10-27",
            "WebSite":{
                "Type":"Home Page",
                "text":"https://www.aaaa.com/"
            }
        },
        "TextInfo":{
            "Text":[
                {
                    "Type":"Business",
                    "updated_at":"2018-09-14",
                    "unused_field":"en-US",
                    "Description":"Lorem ipsum dolor sit amet, consectetur adipiscing elit, laborum."
                },
                {
                    "Type":"Financial",
                    "updated_at":"2022-08-26",
                    "unused_field":"en-US",
                    "Description":"Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat."
                }
            ]
        },
        "Advisors":{
            "Auditor":{
                "Code":"AAA",
                "Name":"Aristotle"
            }
        }
    }

}"""

df = spark.read.json(sc.parallelize([js_str]), multiLine=True)

df.select(col('GeneralInformation.ID'),
          col('GeneralInformation.TextInfo.Text.Description')[0].alias("Description"),
          col('GeneralInformation.TextInfo.Text.updated_at')[0].alias("updated_at")).\
            show(10,False)
#+-----+-----------------------------------------------------------------+----------+
#|ID   |Description                                                      |updated_at|
#+-----+-----------------------------------------------------------------+----------+
#|00001|Lorem ipsum dolor sit amet, consectetur adipiscing elit, laborum.|2018-09-14|
#+-----+-----------------------------------------------------------------+----------+