Hi team, Can you please on the below issue. I have the below json file.------------------------------------{"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"}]}---------------------------- I have to insert the json file into a datalakehouse table as below format. Basically i need three records to be inserted into lakehouse. I used pyspark to read a json, but it does not helped to get the below format. ThanksRajan

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.

Reading json from notebook | Microsoft Fabric Community

Forum Discussion

Related content

Notebook to read complex JSON array

notebook reading .csv

Read view data using notebook

Read Notebook cell string from another notebook

PySpark Notebook to process complex JSON