Relationalize transforms the nested JSON into key-value pairs at the outermost level of the JSON document. The transformed data maintains a list of the original keys from the nested JSON separated by periods. Let’s look at how Relationalize can help you with a sample use case. Suppose that the developers of a video game want to use a data warehouse like Amazon Redshift to run reports on player behavior based on data that is stored in JSON.

Sample 1 shows example user data from the game. The player named “user1” has characteristics such as race, class, and location in nested JSON data.

Further down, the player’s arsenal information includes additional nested JSON data. If the developers want to ETL this data into their data warehouse, they might have to resort to nested loops or recursive functions in their code. You can then write the data to a database or to a data warehouse. You can also write it to delimited text files, such as in comma-separated value (CSV) format, or columnar file formats such as Optimized Row Columnar (ORC) format. You can use either of these format types for long-term storage in Amazon S3. # Read the JSON file as python dictionaryĭata = read_json(filename=r'article.json')ĭataframe = pandas.Storing the transformed files in S3 provides the additional benefit of being able to query this data using Amazon Athena or Amazon Redshift Spectrum.

Looking for a all column data in a tabular format file encountered an error') But looking for a generic function which would be able to convert any nested JSON file to CSV.īut json_normalize and flaten modules only provide a single row at the end with all the column data in it. Tried using json_normalize(), flatten module as well.

Python code to convert json to csv