get sub categories under main category connected by another table

For an example the current database has a category table, sub category table and a data table where each row has a relevant category_id and sub category id as foreign keys. There are can be multiple data rows which belong to the same category and sub category. Categories and sub categories are not directly related but through the data table. Requirement is to generate the json response where all categories are returned with subcategories under them. Required response json is as below.

{
    "categories":[
        {
            "id": 1,
            "name": "category one",
            "sub_categories": [
                {
                    "id": 1,
                    "name": "name 1"
                },
                {
                    "id": 2,
                    "name": "name 2"
                }
            ]
        }]
}

Using the below sql guery( JSON_OBJECT and JSON_ARRAYAGG ) above response can be generated.

    cursor = connection.cursor()
    sql = "select JSON_OBJECT('id',p.`id`,'name',`p`.`name`, 'slug', `p`.`name` " \
          "'subcategory', " \
          "(select JSON_ARRAYAGG(" \
          " JSON_OBJECT('id',`m`.`id`,'name',`m`.`name`)" \
          ")`subcategory`" \
          "  from data t inner join subcategories m on `m`.`id`=`t`.`subcategory_id`" \
          " where `t`.`category_id` = `p`.`id` and `t`.`deleted_at` is null )" \
          ")`categories`" \
          " from categories p;"
    cursor.execute(sql)
    result = cursor.fetchall()
    categories = json.loads(json.dumps(result, default=convert_object_to_string))
    category_data=[]
    for row in categories:
        category_data.append(json.loads(row[0]))

I want a better solution and also only the distinct values should be returned