Recursive Python function to retrieve tree structure table as JSON from a PostgreSQL db with ltree extension

views.py

# The following class would correspond to a Django ViewSet. Nevertheless, you can use any Django framework of your
#  choice or not even a framework at all.
# What I wanted to share with the world is how to convert an existint tree-like SQL table to JSON format, which
#  is way friendlier with our front-end friends.

from django.db import connection
from rest_framework import viewsets
from rest_framework.permissions import IsAuthenticated
from rest_framework.response import Response

class FiltersTreeViewSet(viewsets.ModelViewSet):
    permission_classes = (IsAuthenticated,)

    def list(self, request, **kwargs):
        """
        Retrieve category tree. Only root nodes containing at least product to which the current user is subscribed,
        in any of their ancestors are shown
            :param request
        """

        tree = []
        root_nodes_list = self.root_nodes(client_id)
        for root_node in root_nodes_list:
            tree.append(self._fill_children(root_node))
        return Response(tree)

    def _dictfetchall(self, cursor):
        """Return all rows from a cursor as a dict"""
        columns = [col[0] for col in cursor.description]
        res = [
            dict(zip(columns, row))
            for row in cursor.fetchall()
        ]
        return res

    def _query_to_resultsdict(self, query):
        """Return results from launching a given sql query"""
        with connection.cursor() as cursor:
            cursor.execute(query)
            results = self._dictfetchall(cursor)
        return results

    def root_nodes(self, client_id):
        """Return root nodes containing at least one subscribed product to the client generating the query"""
        query = """
        select
            *
        from
            category_tree ct
        where nlevel(ct.path)=1
        """
        root_nodes = self._query_to_resultsdict(query)
        return root_nodes

    def _fill_children(self, root_node):
      """Recursive function to fill children data of all nodes until last leaf node"""
        children = self.get_children(root_node)
        if children:
            root_node['children'] = [self._fill_children(child) for child in children]
        return root_node

    def get_children(self, node_path):
        query_get_children = "SELECT * FROM category_tree ct where ct.path ~ '%s.*{1}'" % (node_path['path'])
        children_nodes = self._query_to_resultsdict(query_get_children)
        return children_nodes