Forum Discussion
Solved
Reading json from notebook
Hi team, Can you please on the below issue. I have the below json file. ------------------------------------ { "SEMPermID": "139390475563", "Contents":[ { "MetaData": "Sentiment_1227523...
Hi ThiyagarajanLG ,
Can you please try the below code -from pyspark.sql.types import StructType, StructField, StringType, ArrayType from pyspark.sql.functions import col, coalesce, lit, explode # Define the schema schema = StructType([ StructField("SEMPermID", StringType(), True), StructField("Contents", ArrayType( StructType([ StructField("MetaData", StringType(), True), StructField("Payload", ArrayType( StructType([ StructField("FileName", StringType(), True), StructField("ObjectId", StringType(), True), StructField("MimeType", StringType(), True), StructField("Language", StringType(), True), StructField("FileCreated", StringType(), True), ]) ), True), ]) ), True), ]) # Read the JSON data with the defined schema df3 = spark.read.option("multiline", "true").json("Files/testing2.json", schema=schema) # Explode the "Contents" array df3 = df3.withColumn("Contents", explode("Contents")) # Perform transformations to Payload and MetaData df3 = df3.withColumn("Payload", coalesce(col("Contents.Payload"), lit(None))) df3 = df3.withColumn("MetaData", coalesce(col("Contents.MetaData"), lit(None))) # Select only the required columns df3 = df3.select("SEMPermID", "MetaData", "Payload") display(df3)
FYI: In my case provided input json is in testing2.json file.
Hope this is helpful. Please let me know incase of further queries.
Hi ThiyagarajanLG ,
Thanks for using Fabric Community.
As I understand the input json is -
{
"SEMPermID": "139390475563",
"Contents": [
{
"MetaData": "Sentiment_1227523835698.json",
"Payload": [
{
"FileName": "transcript.pdf",
"ObjectId": "823bd00a-b399-4d47-900c-d181c84d3c97",
"MimeType": "application/pdf",
"Language": "en",
"FileCreated": "2024-04-10T16:24:19.011Z"
},
{
"FileName": "transcript.xml",
"ObjectId": "823bd00a-b399-4d47-900c-d181c84d3c88",
"MimeType": "application/pdf",
"Language": "en",
"FileCreated": "2024-04-10T16:24:19.011Z"
}
]
},
{
"MetaData": "Sentiment_1227523835700.json",
"Payload": [
{
"FileName": "transcript.xml",
"ObjectId": "823bd00a-b399-4d47-900c-d181c84d3c79",
"MimeType": "application/pdf",
"Language": "en",
"FileCreated": "2024-04-10T16:24:19.011Z"
}
]
},
{
"MetaData": "Sentiment_1227523835499.json"
}
]
}
Expected table format in lakehouse is with 5 columns (FileName, ObjectId, MimeType, Language, FileCreated)
Please let me if my understanding is correct?
Hi,
No, as i mentioned in my post. The target lake house table will have only 3 columns.
SEMPermID, MetaData and Payload. The Payload column will have the json format values.
Thanks
Rajan
Hi ThiyagarajanLG ,
Can you please try the below code -from pyspark.sql.types import StructType, StructField, StringType, ArrayType from pyspark.sql.functions import col, coalesce, lit, explode # Define the schema schema = StructType([ StructField("SEMPermID", StringType(), True), StructField("Contents", ArrayType( StructType([ StructField("MetaData", StringType(), True), StructField("Payload", ArrayType( StructType([ StructField("FileName", StringType(), True), StructField("ObjectId", StringType(), True), StructField("MimeType", StringType(), True), StructField("Language", StringType(), True), StructField("FileCreated", StringType(), True), ]) ), True), ]) ), True), ]) # Read the JSON data with the defined schema df3 = spark.read.option("multiline", "true").json("Files/testing2.json", schema=schema) # Explode the "Contents" array df3 = df3.withColumn("Contents", explode("Contents")) # Perform transformations to Payload and MetaData df3 = df3.withColumn("Payload", coalesce(col("Contents.Payload"), lit(None))) df3 = df3.withColumn("MetaData", coalesce(col("Contents.MetaData"), lit(None))) # Select only the required columns df3 = df3.select("SEMPermID", "MetaData", "Payload") display(df3)
FYI: In my case provided input json is in testing2.json file.
Hope this is helpful. Please let me know incase of further queries.