Extracting data from nested json file

Question

This is the schema of the data and wanted to extract 'from' in this. Tried using the df3 =df.select(df.transcript.data.from.alias("Type")) and getting invalid syntax error.

How to extract this.

root
 |-- contactId: long (nullable = true)
 |-- mediaLegId: string (nullable = true)
 |-- transcript: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- action: string (nullable = true)
 |    |    |-- data: struct (nullable = true)
 |    |    |    |-- chatId: string (nullable = true)
 |    |    |    |-- customerInfo: struct (nullable = true)
 |    |    |    |    |-- customerIdentifierToken: string (nullable = true)
 |    |    |    |    |-- customerIdentifierType: string (nullable = true)
 |    |    |    |    |-- customerName: string (nullable = true)
 |    |    |    |    |-- initialQuestion: string (nullable = true)
 |    |    |    |-- entryPoint: string (nullable = true)
 |    |    |    |-- from: string (nullable = true)
 |    |    |    |-- lang: string (nullable = true)
 |    |    |    |-- parkDuration: long (nullable = true)
 |    |    |    |-- parkNote: string (nullable = true)
 |    |    |    |-- participant: struct (nullable = true)
 |    |    |    |    |-- disconnectReason: string (nullable = true)
 |    |    |    |    |-- displayName: string (nullable = true)
 |    |    |    |    |-- participantId: string (nullable = true)
 |    |    |    |    |-- preferences: struct (nullable = true)
 |    |    |    |    |    |-- language: string (nullable = true)
 |    |    |    |    |-- state: string (nullable = true)
 |    |    |    |    |-- userName: string (nullable = true)
 |    |    |    |-- reconnected: boolean (nullable = true)
 |    |    |    |-- relatedData: string (nullable = true)
 |    |    |    |-- text: string (nullable = true)
 |    |    |    |-- timestamp: long (nullable = true)
 |    |    |    |-- transcriptText: string (nullable = true)
 |    |    |    |-- transferNote: string (nullable = true)

| | | |-- transcriptText: string (nullable = true) | | | |-- transferNote: string (nullable = true)

can you share sample data

Ghost
– Ghost

2020-01-28 12:04:01 +00:00
Commented Jan 28, 2020 at 12:04 — Ghost
– Ghost, Commented Jan 28, 2020 at 12:04

Ghost · Accepted Answer · 2020-01-28 12:19:30Z

1

Try using it like this

from pyspark.sql import functions as F

df.select(F.explode("transcript").alias('transcript')).select('transcript.*').select("data.*").select("from").show()

answered Jan 28, 2020 at 12:19

Ghost

5204 silver badges10 bronze badges

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

4 Comments

namuduri sujith Over a year ago

Thanks for the response Amit this works if I just need only that column, is there any way that I can use that in combination with other attributes as well? Tried this sample1_2 =df.select(df.contactId.alias("ContactId"),\ df.select(F.explode("transcript").alias('transcript')).select('transcript.*').select("data.*").select("from"),\ df.transcript.data.participant.participantId.alias("participantId"),\ df.transcript.data.text.alias("text") ).toDF('contactId','Type','participantId','Transcript')

namuduri sujith Over a year ago

Got this error : TypeError: Invalid argument, not a string or column: DataFrame[from: string] of type <class 'pyspark.sql.dataframe.DataFrame'>. For column literals, use 'lit', 'array', 'struct' or 'create_map' function.

Ghost Over a year ago

can you provide sample data?

Ghost Over a year ago

Try this : df.select("contactId", F.explode("transcript").alias('transcript')).select("contactId", 'transcript.*').select("contactId", "data.*").select("contactId", "from", "text", "participant.*").select("contactId", "from", "text", "participantId").show()

Collectives™ on Stack Overflow

Extracting data from nested json file

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related