CODEWITHSUNDEEP
pythonjsondictionarytree
Transistor
Transistor

Reputation: 213

Convert JSON table to JSON tree

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.

enter image description here

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.

Upvotes: 0

Views: 528

Answers (1)

trincot
trincot

Reputation: 349956

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)

Upvotes: 1

Related Questions