Python to dynamically build hierarchical JSON from query

Ignition Early Access
1

I have a table with three columns: ID, Name, Parent_ID
How might I use python to format the results of a SELECT * query in JSON so that each result is nested inside its parent?

Desired Sample Output:

[
	{ID: ID, Name: Name, Parent_ID: Parent_ID, Items: [
		{ID: ID, Name: Name, Parent_ID: Parent_ID, Items: []}, 
		{ID: ID, Name: Name, Parent_ID: Parent_ID, Items: [
			ID: ID, Name: Name, Parent_ID: Parent_ID, Items: [],
			ID: ID, Name: Name, Parent_ID: Parent_ID, Items: []
		]}
	]},
	{ID: ID, Name: Name, Parent_ID: Parent_ID, Items: [
		ID: ID, Name: Name, Parent_ID: Parent_ID, Items: []
	]}
]
2

edit 1
I got really excited when it seemed like this was working exactly as intended. But I quickly realized that only every other new record that I add ends up visible in the tree…
image

edit 2
Fixed it by taking out this bit, which was intended to remove the results from the list to iterate through once they’ve been placed in the output. I wonder if it makes a difference with really big datasets…

		for count, x in enumerate(reversed(removedObjects)):
			del json[x]
			del removedObjects[count]

origial:

	def getItems(parent_id, json, removedObjects):
		for count, x in enumerate(reversed(removedObjects)):
			del json[x]
			del removedObjects[count]
		output = []
		count = 0
		for object in json:
			if object['parent_id'] == parent_id:
				removedObjects.append(count)
				output.append({
							  "label": object['name'],
							  "expanded": False,
							  "data": object['path'],
							  "items": getItems(object['id'], json, removedObjects)
							})
			count += 1
		return output
	data = list(value)
	output = []
	removed = []
	count = 0
	lookup = []		
	for object in data:
		id = object['id']
		parent_id = object['parent_id']
		name = object['name']
		path = object['path']
		if parent_id is None:
			removed.append(count)
			output.append({
						  "label": name,
						  "expanded": False,
						  "data": path,
						  "items": getItems(id, data, removed)
						})
		count += 1
	return output
1 Like
3

Hi Daniel, could you perhaps post exactly where your "path" is coming from and how you have the getItems function nested in the "transform" function (if in fact it is nested). Apologies if this is a dumb question, I'm definitely a newbie when it comes to Ignition. Thanks in advance!

1 Like