Convert JSON table to JSON tree

Question

I have the results of an SQL query in JSON format

value = [
    {"Machine": "Mach 1", "Device": "Dev a", "Identifier": "HMI 1"},
    {"Machine": "Mach 1", "Device": "Dev a", "Identifier": "HMI 2"},
    {"Machine": "Mach 1", "Device": "Dev b", "Identifier": "HMI 3"},
    {"Machine": "Mach 1", "Device": "Dev c", "Identifier": "HMI 5"},
    {"Machine": "Mach 2", "Device": "Dev c", "Identifier": "HMI 6"},
    {"Machine": "Mach 2", "Device": "Dev d", "Identifier": "HMI 7"},
    {"Machine": "Mach 3", "Device": "Dev e", "Identifier": "HMI 8"}
]

I'm trying to generate a tree of the form:

Tree to be generated
[ ]- Mach 1
 +[ ]- Dev a
 |  +-- HMI 2
 |  +-- HMI 3
 +[ ]- Dev c
    +-- HMI 5
[ ]- Mach 2
 +[ ]- Dev c
 |  +-- HMI 6
 +[ ]- Dev d
 |  +-- HMI 7
 +[ ]- Dev e
    +-- HMI 8

The output of the function is to be used by Inductive Automation's Perspective Tree component which expects it in the format:

items = [
  {
    "label": "Mach 1",
    "expanded": true,
    "data": "",
    "items": [
      {
        "label": "Dev a",
        "expanded": true,
        "data": "",
        "items": [
          {
            "label": "HMI 1",
            "expanded": true,
            "data": {
              "Identifier": "HMI1",
              "Device": "Dev a",
              "Machine": "Mach 1"
            },
            "items": []
          },
          {
            "label": "HMI 2",
            "expanded": true,
            "data": {
              "Identifier": "HMI2",
              "Device": "Dev a",
              "Machine": "Mach 1"
            },
            "items": []
          }

        ]
      },
      {
        "label": "Dev b",
        "expanded": true,
        "data": "",
        "items": [
          {
            "label": "HMI 3",
            "expanded": true,
            "data": {
              "Identifier": "HMI3",
              "Device": "Dev b",
              "Machine": "Mach 1"
            },
            "items": []
          }
        ]
      }

    ]
  },

…

I have created some linear Python code for a tree depth of three but I'd like to modify it to work automatically with tree depth from 1 to 6 (or so) returned by the SQL query. (The sample input and output above is three-level.) Unfortunately I can't figure out how to modify this to work with recursion for a variable number of columns.

Figure 1. The results of my lazy code (available on request).

Can anyone suggest an approach using Python - the script language of the Ignition application I'm using?

Many thanks.

You speak about 6 levels. How does that work with the given data? — trincot
– trincot, Commented Jan 30, 2022 at 18:19
Thanks for the interest. The sample data hs three levels. I have coded manually for that but it doesn't scale well if I want to be able to make a more flexible tree-generating script. (I'm trying to make a general-purpose script that will generate a tree for various web-based dashboards. The number of tree levels will vary by application.) I thought that this would be a problem that had been solved many times but many hours of web searching has failed to find anything suitable. — Transistor
– Transistor, Commented Jan 30, 2022 at 18:31

trincot · Accepted Answer · 2022-01-30 19:24:35Z

1

You would need to provide the order in which the keys should be used to drill down in the hierarchy. This is good practice, as the order of the keys in a dictionary might not represent the desired order.

Once you have these keys as a list, you could use it to iteratively dig deeper into the hierarchy.

def makeForest(values, levels):
    items = []  # The top level result array
    paths = {}  # Objects keyed by path
    root = { "items": items }  # Dummy: super root of the forest
    for data in values:
        parent = root
        path = ""
        for key in levels:
            label = data[key]
            path += repr([label])
            node = paths.get(path, None)
            if not node:
                node = {
                    "label": data[key],
                    "expanded": True,
                    "data": "",
                    "items": []
                }
                paths[path] = node
                parent["items"].append(node)
            parent = node
        parent["data"] = data
    return items

# Example use:
value = [{"Machine": "Mach 1", "Device": "Dev a", "Identifier": "HMI 1"},{"Machine": "Mach 1", "Device": "Dev a", "Identifier": "HMI 2"},{"Machine": "Mach 1", "Device": "Dev b", "Identifier": "HMI 3"},{"Machine": "Mach 1", "Device": "Dev c", "Identifier": "HMI 5"},{"Machine": "Mach 2", "Device": "Dev c", "Identifier": "HMI 6"},{"Machine": "Mach 2", "Device": "Dev d", "Identifier": "HMI 7"},{"Machine": "Mach 3", "Device": "Dev e", "Identifier": "HMI 8"}]
forest = makeForest(value, ["Machine", "Device", "Identifier"])
print(forest)

edited Jan 30, 2022 at 19:24

answered Jan 30, 2022 at 18:45

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Transistor Over a year ago

Thanks very much for that and the output appears to be exactly what I want but I'm afraid you missed the python tag! I'll edit to make it clearer. I'll also see if I can figure out how to Pythonize your answer.

trincot Over a year ago

Oh, yes, I will do the conversion to Python. Hold on.

trincot Over a year ago

Updated to python.

Transistor Over a year ago

What a guy! It's great to be multi-lingual. I'm an automation engineer and work with a wide variety of systems so I never get to be really good at any of them. This seems to work as required. I'll check it out, try to understand it, and report back. Thank you again.

Collectives™ on Stack Overflow

Convert JSON table to JSON tree

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related