How to flatten a nested json array?

by

Here’s a partial solution:

First save your data in the same directory as the script as a JSON file called data.json.

import json
import pandas as pd
from pandas.io.json import json_normalize

with open('data.json') as json_file:
    json_data = json.load(json_file)

new_data = json_data['data']['workbooks']

result = json_normalize(new_data, ['embeddedDatasources', 'upstreamTables'], ['projectName', 'name', 'createdAt', 'updatedAt', 'owner', 'site'], record_prefix='_')

result

Output:

_name _schema _database.name _database.connectionType projectName name createdAt updatedAt owner site
0 table_1 schema_1 testdb redshift TestProject wkb1 2020-12-13T15:38:58Z 2020-12-13T15:38:59Z {‘name’: ‘user1’, ‘username’: ‘John’} {‘name’: ‘site1’}
1 table_2 schema_2 testdb redshift TestProject wkb1 2020-12-13T15:38:58Z 2020-12-13T15:38:59Z {‘name’: ‘user1’, ‘username’: ‘John’} {‘name’: ‘site1’}
2 table_3 schema_3 testdb redshift TestProject wkb1 2020-12-13T15:38:58Z 2020-12-13T15:38:59Z {‘name’: ‘user1’, ‘username’: ‘John’} {‘name’: ‘site1’}
3 table_4 schema_1 testdb redshift TestProject wkb1 2020-12-13T15:38:58Z 2020-12-13T15:38:59Z {‘name’: ‘user1’, ‘username’: ‘John’} {‘name’: ‘site1’}

What next?

I think if you re-structure the data a bit in advance (for example flattening 'database': {'name': 'testdb', 'connectionType': 'redshift'}) you will be able to add more fields to the meta parameter.

As you see in the documentation of json_normalize, the four parameters that are used here are:

  1. data: dict or list of dicts :

    • Unserialized JSON objects.

  2. record_path: str or list of str : default None

    • Path in each object to list of records. If not passed, data will be assumed to be an array of records.

  3. meta: list of paths (str or list of str) : default None

    • Fields to use as metadata for each record in resulting table.

  4. record_prefix: str : default None

    • If True, prefix records with dotted (?) path, e.g. foo.bar.field if path to records is [‘foo’, ‘bar’].

More Related Contents:

  1. Convert Json File to data Frame
  2. How to return a dict as a JSON response from a Flask view?
  3. How to get POSTed JSON in Flask?
  4. How to parse data in JSON format?
  5. Saving utf-8 texts with json.dumps as UTF8, not as \u escape sequence
  6. Python JSON serialize a Decimal object
  7. What’s the best way to parse a JSON response from the requests library?
  8. How do I automatically fix an invalid JSON string?
  9. Django JSONField inside ArrayField
  10. How to flatten a pandas dataframe with some columns as json?
  11. Difference between data and json parameters in python requests package
  12. Deserialize a json string to an object in python
  13. pandas groupby to nested json
  14. How to remove whitespaces and newlines from every value in a JSON file?
  15. TypeError: the JSON object must be str, not ‘bytes’
  16. JSON serialize a dictionary with tuples as key
  17. pandas read_json: “If using all scalar values, you must pass an index”
  18. How can I loop over entries in JSON?
  19. How convert a jpeg image into json file in Google machine learning
  20. Pyspark: Parse a column of json strings
  21. FastAPI is very slow in returning a large amount of JSON data
  22. How can I parse (read) and use JSON?
  23. How can I extract a single value from a nested data structure (such as from parsing JSON)?
  24. JSON object must be str, bytes or bytearray, not dict
  25. TypeError: Object of type ‘bytes’ is not JSON serializable
  26. Python Flask, TypeError: ‘dict’ object is not callable
  27. Looping through a JSON array in Python
  28. Reading an JSON file from S3 using Python boto3
  29. Convert JSON to SQLite in Python – How to map json keys to database columns properly?
  30. How to convert csv to json in python?

Leave a Comment