sqlite - How can I create a hierarchical json response in FLASK

Question

I have a single table in database like database table. I want to search a child from database and return a hierarchical JSON to a front end in order to create a tree. How can I do that in FLASK. My expected JSON for mat should be like expected JSON

question from:https://stackoverflow.com/questions/65854960/how-can-i-create-a-hierarchical-json-response-in-flask

Answer 1

Since you have tagged your question with flask, this post assumes you are using Python as well. To format your database values in JSON string, you can query the db and then use recursion:

import sqlite3, collections
d = list(sqlite3.connect('file.db').cursor().execute("select * from values"))
def get_tree(vals):
  _d = collections.defaultdict(list)
  for a, *b in vals:
    _d[a].append(b)
  return [{'name':a, **({} if not (c:=list(filter(None, b))) else {'children':get_tree(b)})} for a, b in _d.items()]

---

import json
print(json.dumps(get_tree(d), indent=4))

Output:

[
  {
    "name": "AA",
    "children": [
        {
            "name": "BB",
            "children": [
                {
                    "name": "EE",
                    "children": [
                        {
                            "name": "JJ",
                            "children": [
                                {
                                    "name": "EEV"
                                },
                                {
                                    "name": "FFW"
                                }
                            ]
                        },
                        {
                            "name": "KK",
                            "children": [
                                {
                                    "name": "HHX"
                                }
                            ]
                        }
                    ]
                }
            ]
        },
        {
            "name": "CC",
            "children": [
                {
                    "name": "FF",
                    "children": [
                        {
                            "name": "LL",
                            "children": [
                                {
                                    "name": "QQY"
                                }
                            ]
                        },
                        {
                            "name": "MM",
                            "children": [
                                {
                                    "name": "RRV"
                                }
                            ]
                        }
                    ]
                },
                {
                    "name": "GG",
                    "children": [
                        {
                            "name": "NN",
                            "children": [
                                {
                                    "name": "SSW"
                                }
                            ]
                        }
                    ]
                }
            ]
        },
        {
            "name": "DD",
            "children": [
                {
                    "name": "HH",
                    "children": [
                        {
                            "name": "OO",
                            "children": [
                                {
                                    "name": "TTZ"
                                }
                            ]
                        }
                    ]
                },
                {
                    "name": "II",
                    "children": [
                        {
                            "name": "PP",
                            "children": [
                                {
                                    "name": "UUW"
                                }
                             ]
                         }
                      ]
                  }
              ]
          }
      ]
   }
]