# Python to dynamically build hierarchical JSON from query

**URL:** <https://forum.inductiveautomation.com/t/python-to-dynamically-build-hierarchical-json-from-query/40375>\
**Category:** Ignition Early Access\
**Created:** [November 5, 2020, 8:55pm UTC](https://forum.inductiveautomation.com/t/python-to-dynamically-build-hierarchical-json-from-query/40375 "2020-11-05T20:55:04Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![danieltalbot](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/danieltalbot/32/11201_2.png) [@danieltalbot](https://forum.inductiveautomation.com/u/danieltalbot)\
**Post date:** [November 5, 2020, 8:55pm UTC](https://forum.inductiveautomation.com/t/python-to-dynamically-build-hierarchical-json-from-query/40375/1 "2020-11-05T20:55:04Z")

</div>

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:

```auto
[
	{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: []
	]}
]

```

---

<div class="post-metadata">

**Author:** ![danieltalbot](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/danieltalbot/32/11201_2.png) [@danieltalbot](https://forum.inductiveautomation.com/u/danieltalbot)\
**Post date:** [November 6, 2020, 12:30am UTC](https://forum.inductiveautomation.com/t/python-to-dynamically-build-hierarchical-json-from-query/40375/2 "2020-11-06T00:30:50Z")

</div>

_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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/6/26ba9f450a5044b8b19098f13496a8a6aa4865d2.png)

_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…

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

```

origial:

```auto
	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

```

---

<div class="post-metadata">

**Author:** ![John\_Leonard](https://avatars.discourse-cdn.com/v4/letter/j/dc4da7/32.png) [@John\_Leonard](https://forum.inductiveautomation.com/u/John_Leonard)\
**Post date:** [August 2, 2023, 7:37pm UTC](https://forum.inductiveautomation.com/t/python-to-dynamically-build-hierarchical-json-from-query/40375/3 "2023-08-02T19:37:17Z")

</div>

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!