Apache Spark - Nested JSON array to flatten columns

Question

Hello,
I have a JSON which is nested and have Nested arrays. How could I use Apache Spark Python script to flatten it in a columnar manner so that I could use it via AWS Glue and use AWS Athena or AWS redshift to query the data?

Answer 1

It depends on the structure of your JSon file but here I have posted a code that you can refer:

import pandas as pd
from pandas.io.json import json_normalize
import json
with open('user.txt') as f:

json_data = json.load(f)


def flatten_json(y):
out = {}

def flatten(x, name=''):
if type(x) is dict:
for a in x:
flatten(x[a], name + a + '_')
elif type(x) is list:
i = 0
for a in x:
flatten(a, name + str(i) + '_')
i += 1
else:
out[name[:-1]] = x

flatten(y)
return out

flat = flatten_json(json_data)
dt=json_normalize(flat)

dt is your data frame object containing flattened json.