In hive table i am getting json data in one of the column. Requirement is to parse the data and create new table only with "milestoneUpdates" part.
below is the data. the first key of json we are getting different for every row. in below json we have "5300" next row it will be "5301"
{
"5300": {
"shipFromSiteName": "5300",
"shipToSiteName": "10957805",
"milestoneName": "5300",
"departTime": "2020-11-17T00:46:45.000+0000",
"creationDate": "2020-11-17T09:40:49.361+0000",
"milestoneUpdates": {
"IT-2020-11-18T22:44:02.000+0000": {
"key": "IT-2020-11-18T22:44:02.000+0000",
"status": "In transit",
"statusCode": "IT-2020-11-18T22:44:02.000+0000",
"message": "In transit at FEDEX_FACILITY. Address: AUSTELL, GA 30106, United States",
"timestamp": "2020-11-18T22:44:02.000+0000"
},
"IT-2020-11-18T10:31:37.000+0000": {
"key": "IT-2020-11-18T10:31:37.000+0000",
"status": "In transit",
"statusCode": "IT-2020-11-18T10:31:37.000+0000",
"message": "In transit at FEDEX_FACILITY. Address: CONLEY, GA 30288-1103, United States",
"timestamp": "2020-11-18T10:31:37.000+0000"
},
"OC-2020-11-13T21:48:05.000+0000": {
"key": "OC-2020-11-13T21:48:05.000+0000",
"status": "Shipment information sent to FedEx",
"statusCode": "OC-2020-11-13T21:48:05.000+0000",
"message": "Shipment information sent to FedEx at CUSTOMER. Address: city?, code?, country?",
"timestamp": "2020-11-13T21:48:05.000+0000"
},
"PU-2020-11-17T00:46:45.000+0000": {
"key": "PU-2020-11-17T00:46:45.000+0000",
"status": "Picked up",
"statusCode": "PU-2020-11-17T00:46:45.000+0000",
"message": "Picked up at PICKUP_LOCATION. Address: COVINGTON, GA 30014-4958, United States",
"timestamp": "2020-11-17T00:46:45.000+0000"
},
"IT-2020-11-24T12:05:39.000+0000": {
"key": "IT-2020-11-24T12:05:39.000+0000",
"status": "In transit",
"statusCode": "IT-2020-11-24T12:05:39.000+0000",
"message": "In transit at FEDEX_FACILITY. Address: CICERO, IL 60804, United States",
"timestamp": "2020-11-24T12:05:39.000+0000"
},
"IT-2020-11-24T18:30:04.000+0000": {
"key": "IT-2020-11-24T18:30:04.000+0000",
"status": "In transit",
"statusCode": "IT-2020-11-24T18:30:04.000+0000",
"message": "In transit at FEDEX_FACILITY. Address: ST PAUL, MN 55104, United States",
"timestamp": "2020-11-24T18:30:04.000+0000"
},
"IT-2020-11-19T17:30:18.000+0000": {
"key": "IT-2020-11-19T17:30:18.000+0000",
"status": "In transit",
"statusCode": "IT-2020-11-19T17:30:18.000+0000",
"message": "In transit at FEDEX_FACILITY. Address: CHICAGO, IL 60617, United States",
"timestamp": "2020-11-19T17:30:18.000+0000"
},
"IT-2020-11-21T19:28:52.000+0000": {
"key": "IT-2020-11-21T19:28:52.000+0000",
"status": "In transit",
"statusCode": "IT-2020-11-21T19:28:52.000+0000",
"message": "In transit at FEDEX_FACILITY. Address: FOREST VIEW, IL 60638, United States",
"timestamp": "2020-11-21T19:28:52.000+0000"
},
"PTASTOP_ETA_UPDATED-2020-11-24T02:00:00.000+00001606183200000": {
"key": "PTASTOP_ETA_UPDATED-2020-11-24T02:00:00.000+00001606183200000",
"status": "PTA from Carrier ESTIMATED_DELIVERY",
"statusCode": "PTASTOP_ETA_UPDATED-2020-11-24T02:00:00.000+00001606183200000",
"message": "Estimated Delivery",
"timestamp": "2020-11-24T02:00:00.000+0000"
}
}
},
"10957805": {
"shipFromSiteName": "5300",
"shipToSiteName": "10957805",
"milestoneName": "10957805",
"creationDate": "2020-11-17T09:40:49.361+0000",
"predictedTimeOfArrival": "2020-11-24T02:00:00.000+0000",
"milestoneUpdates": {
}
}
}
question from:
https://stackoverflow.com/questions/65932065/pyspark-complex-json-parse 
