Convert JSON table to JSON tree-CodePudding

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

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.

CodePudding user response：

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)