how to flatten a nested .json file.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "45a25fb4-04c7-4f36-ba89-30daa461e893",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "df6 = spark.read.format(\"json\").load(\"dbfs:/FileStore/shared_uploads/abhishekangne7@gmail.com/resturant_json_data.json\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "2a2705e9-0a4c-427d-9636-a4a3d4407b71",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.types import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "9ca22d3e-5a35-4deb-a282-af1db99bdcb3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-------+--------------------+-------------+-------------+-------------+------+\n|code|message|         restaurants|results_found|results_shown|results_start|status|\n+----+-------+--------------------+-------------+-------------+-------------+------+\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|[{{{17066603}, b9...|         6835|           20|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|[{{{17093124}, b9...|         8680|           20|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|[{{{17580142}, b9...|          943|           20|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|[{{{17284158}, b9...|          257|           20|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|[{{{17678233}, b9...|          358|           20|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|[{{{17375047}, b9...|          641|           20|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|                  []|            0|            0|            1|  NULL|\n|NULL|   NULL|[{{{17616590}, b9...|         1613|           20|            1|  NULL|\n+----+-------+--------------------+-------------+-------------+-------------+------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df6.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "7e4db690-09fb-40e5-8ce6-2f07581d5b5e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- code: long (nullable = true)\n |-- message: string (nullable = true)\n |-- restaurants: array (nullable = true)\n |    |-- element: struct (containsNull = true)\n |    |    |-- restaurant: struct (nullable = true)\n |    |    |    |-- R: struct (nullable = true)\n |    |    |    |    |-- res_id: long (nullable = true)\n |    |    |    |-- apikey: string (nullable = true)\n |    |    |    |-- average_cost_for_two: long (nullable = true)\n |    |    |    |-- cuisines: string (nullable = true)\n |    |    |    |-- currency: string (nullable = true)\n |    |    |    |-- deeplink: string (nullable = true)\n |    |    |    |-- establishment_types: array (nullable = true)\n |    |    |    |    |-- element: string (containsNull = true)\n |    |    |    |-- events_url: string (nullable = true)\n |    |    |    |-- featured_image: string (nullable = true)\n |    |    |    |-- has_online_delivery: long (nullable = true)\n |    |    |    |-- has_table_booking: long (nullable = true)\n |    |    |    |-- id: string (nullable = true)\n |    |    |    |-- is_delivering_now: long (nullable = true)\n |    |    |    |-- location: struct (nullable = true)\n |    |    |    |    |-- address: string (nullable = true)\n |    |    |    |    |-- city: string (nullable = true)\n |    |    |    |    |-- city_id: long (nullable = true)\n |    |    |    |    |-- country_id: long (nullable = true)\n |    |    |    |    |-- latitude: string (nullable = true)\n |    |    |    |    |-- locality: string (nullable = true)\n |    |    |    |    |-- locality_verbose: string (nullable = true)\n |    |    |    |    |-- longitude: string (nullable = true)\n |    |    |    |    |-- zipcode: string (nullable = true)\n |    |    |    |-- menu_url: string (nullable = true)\n |    |    |    |-- name: string (nullable = true)\n |    |    |    |-- offers: array (nullable = true)\n |    |    |    |    |-- element: string (containsNull = true)\n |    |    |    |-- photos_url: string (nullable = true)\n |    |    |    |-- price_range: long (nullable = true)\n |    |    |    |-- switch_to_order_menu: long (nullable = true)\n |    |    |    |-- thumb: string (nullable = true)\n |    |    |    |-- url: string (nullable = true)\n |    |    |    |-- user_rating: struct (nullable = true)\n |    |    |    |    |-- aggregate_rating: string (nullable = true)\n |    |    |    |    |-- rating_color: string (nullable = true)\n |    |    |    |    |-- rating_text: string (nullable = true)\n |    |    |    |    |-- votes: string (nullable = true)\n |-- results_found: long (nullable = true)\n |-- results_shown: long (nullable = true)\n |-- results_start: string (nullable = true)\n |-- status: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "df6.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "42725405-76a0-4691-8f7b-b334d77636d0",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- code: long (nullable = true)\n |-- message: string (nullable = true)\n |-- results_found: long (nullable = true)\n |-- results_shown: long (nullable = true)\n |-- results_start: string (nullable = true)\n |-- status: string (nullable = true)\n |-- new_restaurants: struct (nullable = true)\n |    |-- restaurant: struct (nullable = true)\n |    |    |-- R: struct (nullable = true)\n |    |    |    |-- res_id: long (nullable = true)\n |    |    |-- apikey: string (nullable = true)\n |    |    |-- average_cost_for_two: long (nullable = true)\n |    |    |-- cuisines: string (nullable = true)\n |    |    |-- currency: string (nullable = true)\n |    |    |-- deeplink: string (nullable = true)\n |    |    |-- establishment_types: array (nullable = true)\n |    |    |    |-- element: string (containsNull = true)\n |    |    |-- events_url: string (nullable = true)\n |    |    |-- featured_image: string (nullable = true)\n |    |    |-- has_online_delivery: long (nullable = true)\n |    |    |-- has_table_booking: long (nullable = true)\n |    |    |-- id: string (nullable = true)\n |    |    |-- is_delivering_now: long (nullable = true)\n |    |    |-- location: struct (nullable = true)\n |    |    |    |-- address: string (nullable = true)\n |    |    |    |-- city: string (nullable = true)\n |    |    |    |-- city_id: long (nullable = true)\n |    |    |    |-- country_id: long (nullable = true)\n |    |    |    |-- latitude: string (nullable = true)\n |    |    |    |-- locality: string (nullable = true)\n |    |    |    |-- locality_verbose: string (nullable = true)\n |    |    |    |-- longitude: string (nullable = true)\n |    |    |    |-- zipcode: string (nullable = true)\n |    |    |-- menu_url: string (nullable = true)\n |    |    |-- name: string (nullable = true)\n |    |    |-- offers: array (nullable = true)\n |    |    |    |-- element: string (containsNull = true)\n |    |    |-- photos_url: string (nullable = true)\n |    |    |-- price_range: long (nullable = true)\n |    |    |-- switch_to_order_menu: long (nullable = true)\n |    |    |-- thumb: string (nullable = true)\n |    |    |-- url: string (nullable = true)\n |    |    |-- user_rating: struct (nullable = true)\n |    |    |    |-- aggregate_rating: string (nullable = true)\n |    |    |    |-- rating_color: string (nullable = true)\n |    |    |    |-- rating_text: string (nullable = true)\n |    |    |    |-- votes: string (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "df6.select(\"*\",explode(\"restaurants\").alias(\"new_restaurants\"))\\\n",
    "    .drop(\"restaurants\").printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "5faa0170-8ff3-4250-b362-05ac9208bc29",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n |-- code: long (nullable = true)\n |-- message: string (nullable = true)\n |-- results_found: long (nullable = true)\n |-- results_shown: long (nullable = true)\n |-- results_start: string (nullable = true)\n |-- status: string (nullable = true)\n |-- new_restaurants: struct (nullable = true)\n |    |-- restaurant: struct (nullable = true)\n |    |    |-- R: struct (nullable = true)\n |    |    |    |-- res_id: long (nullable = true)\n |    |    |-- apikey: string (nullable = true)\n |    |    |-- average_cost_for_two: long (nullable = true)\n |    |    |-- cuisines: string (nullable = true)\n |    |    |-- currency: string (nullable = true)\n |    |    |-- deeplink: string (nullable = true)\n |    |    |-- establishment_types: array (nullable = true)\n |    |    |    |-- element: string (containsNull = true)\n |    |    |-- events_url: string (nullable = true)\n |    |    |-- featured_image: string (nullable = true)\n |    |    |-- has_online_delivery: long (nullable = true)\n |    |    |-- has_table_booking: long (nullable = true)\n |    |    |-- id: string (nullable = true)\n |    |    |-- is_delivering_now: long (nullable = true)\n |    |    |-- location: struct (nullable = true)\n |    |    |    |-- address: string (nullable = true)\n |    |    |    |-- city: string (nullable = true)\n |    |    |    |-- city_id: long (nullable = true)\n |    |    |    |-- country_id: long (nullable = true)\n |    |    |    |-- latitude: string (nullable = true)\n |    |    |    |-- locality: string (nullable = true)\n |    |    |    |-- locality_verbose: string (nullable = true)\n |    |    |    |-- longitude: string (nullable = true)\n |    |    |    |-- zipcode: string (nullable = true)\n |    |    |-- menu_url: string (nullable = true)\n |    |    |-- name: string (nullable = true)\n |    |    |-- offers: array (nullable = true)\n |    |    |    |-- element: string (containsNull = true)\n |    |    |-- photos_url: string (nullable = true)\n |    |    |-- price_range: long (nullable = true)\n |    |    |-- switch_to_order_menu: long (nullable = true)\n |    |    |-- thumb: string (nullable = true)\n |    |    |-- url: string (nullable = true)\n |    |    |-- user_rating: struct (nullable = true)\n |    |    |    |-- aggregate_rating: string (nullable = true)\n |    |    |    |-- rating_color: string (nullable = true)\n |    |    |    |-- rating_text: string (nullable = true)\n |    |    |    |-- votes: string (nullable = true)\n |-- res_id: long (nullable = true)\n\n"
     ]
    }
   ],
   "source": [
    "df6.select(\"*\",explode(\"restaurants\").alias(\"new_restaurants\"))\\\n",
    "    .drop(\"restaurants\").\\\n",
    "        select(\"*\",\"new_restaurants.restaurant.R.res_id\").printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "23c60e1d-177c-48f3-a7e7-19f4aaeb4bca",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----------------------+------------------------------------+\n|res_id  |establishment_types_new|name                                |\n+--------+-----------------------+------------------------------------+\n|17066603|NULL                   |The Coop                            |\n|17059541|NULL                   |Maggiano's Little Italy             |\n|17064405|NULL                   |Tako Cheena by Pom Pom              |\n|17057797|NULL                   |Bosphorous Turkish Cuisine          |\n|17057591|NULL                   |Bahama Breeze Island Grille         |\n|17064266|NULL                   |Hawkers Asian Street Fare           |\n|17060516|NULL                   |Seasons 52 Fresh Grill              |\n|17060320|NULL                   |Raglan Road Irish Pub and Restaurant|\n|17059060|NULL                   |Hillstone                           |\n|17059012|NULL                   |Hollerbach's Willow Tree Café       |\n|17060869|NULL                   |Texas de Brazil                     |\n|17061231|NULL                   |The Ravenous Pig                    |\n|17058534|NULL                   |Earl of Sandwich                    |\n|17057925|NULL                   |Café Tu Tu Tango                    |\n|17064031|NULL                   |Tibby's New Orleans Kitchen         |\n|17061237|NULL                   |Cevíche Tapas Bar & Restaurant      |\n|17061253|NULL                   |Ethos Vegan Kitchen                 |\n|17061296|NULL                   |Pom Pom's Teahouse and Sandwicheria |\n|17061205|NULL                   |Yellow Dog Eats                     |\n|17057397|NULL                   |'Ohana                              |\n+--------+-----------------------+------------------------------------+\nonly showing top 20 rows\n\n"
     ]
    }
   ],
   "source": [
    "df6.select(\"*\",explode(\"restaurants\").alias(\"new_restaurants\"))\\\n",
    "    .drop(\"restaurants\")\\\n",
    "        .select(\"*\",\"new_restaurants.restaurant.R.res_id\",\n",
    "               explode_outer(\"new_restaurants.restaurant.establishment_types\").alias(\"establishment_types_new\"),\n",
    "               (\"new_restaurants.restaurant.name\")).drop(\"new_restaurants\", \"code\",\"message\",\"results_found\",\"results_shown\",\"results_start\",\"status\").show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "cb1cf7fb-6534-4073-a9f9-a2039e670881",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "environmentMetadata": null,
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 4
   },
   "notebookName": "how to flatten a nested .json file",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}