JSON to Table

../../../../../_images/json2table.svg

Convert a JSON file to a Table

There are two kinds of tables that can be created:

  • Single row – where the JSON structure is simply flattened
  • Multiple rows - where the JSON structure is recursively expanded to create several rows

If a single-row table is created, there is an option to minimize the column names to remove unnecessary path information from the JSON keys.

For example from the JSON:

{
    "version":"1.0",
    "software":"sfd",
    "items" : {
        "a":"1",
        "b":"2",
         "c":"3"
    }
}

we can create the following single-row table

version    software    items.a    items.b    items.c
----------------------------------------------------
1.0        sfd         1          2          3

and the column names can be minimized to

version    software    a    b    c
-------------------------------------
1.0        sfd         1    2    3

If a multiple rows-table is created, the recursive algorithm might identify keys and therefore columns that are lacking some values. One can choose to fill in the missing values with a empty string, a nan string or mask the value.

For example from the JSON:

{
    "version":"1.0",
    "software":"sfd",
    "items" : [
        {
            "a":"1",
            "b":"2",
            "c":"3"
        },
        {
            "a":"67",
            "b":"77",
            "d":"97"
        }
    ]
}

we can create the following multiple-rows table

version    software    a    b    c    d
-------------------------------------------
1.0        sfd         1    2    3    ---
1.0        sfd         67   77   ---  97

where the c column is masked in the second row and the d column is masked in the first row.

If the algorithm that creates tnhe multi-row table fails to produce the desired table, it might be worth using other nodes to remove, select or split the JSON structure on some key.

Input ports:
input:

json

Input JSON object

Output ports:
output:

table

Output table

Configuration:
(no label) (table_kind)
What kind of table to create
Minimize colum names (minimize_col_names)
Create column names that are minimal
Use zero-like values instead of masks (nomask)
When unchecked data cells that are missing will be masked. When checked such cells are instead assigned 0, 0.0, False, “”, etc. depending on the type of the value column.
class node_json2table.JsonToTable[source]