Python - Convert Json to table structure-CodePudding

I have a JSON with the following structure below into a list in a python variable. I'd like to extract this JSON value as a table. My question is, how can I extract it from the list and how can I change it into a table?

Once I have converted it, I will insert the output into a Postgres table.

JSON structure

['
{
    "_id": {
        "$Col1": "XXXXXXX2443"
    },
    "col2": false,
    "col3": "359335050111111",
    "startedAt": {
        "$date": 1633309625000
    },
    "endedAt": {
        "$date": 1633310213000
    },
    "col4": "YYYYYYYYYYYYYYYYYY",
    "created_at": {
        "$date": 1633310846935
    },
    "updated_at": {
        "$date": 1633310846935
    },
    "__v": 0
}
']

Desired output:

CodePudding user response：

You can convert the Json to a dictionary in python using json.load.
This dictionary can be converted to a dataframe using Pandas.Dataframe.
You can export this dataframe as .csv using pandas.Dataframe.to_csv to be consumed in Postgres.

Note: This requires Pandas library to be installed. Or else,you can simply install Anaconda (if you are using any other IDE) and most frequently used packages come installed with it.

CodePudding user response：

Use the code below.

I have used PrettyTable module for printing in a table like structure. Use this - https://www.geeksforgeeks.org/how-to-make-a-table-in-python/ for table procedure.

Also, all the headers and values will be stored in headers and values variable.

import json
from prettytable import PrettyTable

value = ['''
       {
           "_id": {
               "$Col1": "XXXXXXX2443"
           },
           "col2": false,
           "col3": "359335050111111",
           "startedAt": {
               "$date": 1633309625000
           },
           "endedAt": {
               "$date": 1633310213000
           },
           "col4": "YYYYYYYYYYYYYYYYYY",
           "created_at": {
               "$date": 1633310846935
           },
           "updated_at": {
               "$date": 1633310846935
           },
           "__v": 0
       }''']

dictionary = json.loads(value[0])
headers = []
values = []
for key in dictionary:
    head = key
    value = ""
    if type(dictionary[key]) == type({}):
        for key2 in dictionary[key]:
            head  = "/"   key2
            value = dictionary[key][key2]
            headers.append(head)
            values.append(value)

    else:
        value = dictionary[key]
        headers.append(head)
        values.append(value)

print(headers)
print(values)
myTable = PrettyTable(headers)

myTable.add_row(values)
print(myTable)

Output

['_id/$Col1', 'col2', 'col3', 'startedAt/$date', 'endedAt/$date', 'col4', 'created_at/$date', 'updated_at/$date', '__v']
['XXXXXXX2443', False, '359335050111111', 1633309625000, 1633310213000, 'YYYYYYYYYYYYYYYYYY', 1633310846935, 1633310846935, 0]

 ------------- ------- ----------------- ----------------- --------------- -------------------- ------------------ ------------------ ----- 
|  _id/$Col1  |  col2 |       col3      | startedAt/$date | endedAt/$date |        col4        | created_at/$date | updated_at/$date | __v |
 ------------- ------- ----------------- ----------------- --------------- -------------------- ------------------ ------------------ ----- 
| XXXXXXX2443 | False | 359335050111111 |  1633309625000  | 1633310213000 | YYYYYYYYYYYYYYYYYY |  1633310846935   |  1633310846935   |  0  |
 ------------- ------- ----------------- ----------------- --------------- -------------------- ------------------ ------------------ -----