golang实现记录集转化为树状json结构 实现echarts sunburst 旭日图

数据库中的数据记录 

type Node struct {
	Name  string `json:"name"`
	Value int    `json:"value"`
}

type Subtree struct {
	Name     string `json:"name"`
	Children []Node `json:"children"`
}

type Tree struct {
	Name     string    `json:"name"`
	Children []Subtree `json:"children"`
}

type sunburst struct {
	label1, label2 string
}

//一次读取多次循环
func GetTreeJson1(sql string) string {
	sun := make(map[string][]Subtree)
	sunsub := make(map[sunburst][]Node)
	orm := db.GetOrm()
	sqlstr := "select exhtrade as label1,exhtrade2 as label2,exhproject as label3,sum(exhclicknum) as count from tp_exhibitionkey where ektype='展品' and exhid=18238 GROUP BY label1,label2,label3"
	infos, err := orm.QueryString(sqlstr)
	arr1 := make([]Tree, 0, 10)
	if err != nil {
		log.ErrorLog(fmt.Sprintf("[service:Summary:GetTreeJson]: exhtrade QueryString: %v", err))
		return ""
	} else {
		for _, item := range infos {
			label1 := item["label1"]
			label2 := item["label2"]
			label3 := item["label3"]
			count, _ := strconv.Atoi(item["count"])
			sunsub[sunburst{label1, label2}] = append(sunsub[sunburst{label1, label2}], Node{Name: label3, Value: count})
		}
		for key, item := range sunsub {
			burst := sunburst(key)
			sun[burst.label1] = append(sun[burst.label1], Subtree{Name: burst.label2, Children: item})
		}
		for key, item := range sun {
			arr1 = append(arr1, Tree{Name: key, Children: item})
		}
		jsonByte, _ := json.Marshal(arr1)
		jsonStr := string(jsonByte)
		return jsonStr
	}
}

//根据sql语句生成树状json串 之前实现的多次循环查询数据库
func GetTreeJson(sql string) string {
	orm := db.GetOrm()
	sqlstr := fmt.Sprintf("select distinct label1 from (%s) t ", sql)
	infos, err := orm.QueryString(sqlstr)
	arr1 := make([]Tree, 0, 10)
	if err != nil {
		log.ErrorLog(fmt.Sprintf("[service:Summary:GetTreeJson]: exhtrade QueryString: %v", err))
		return ""
	} else {
		for _, item := range infos {
			label1 := item["label1"]
			sqlstr := fmt.Sprintf("select distinct label2 from (%s) t where label1='%s' ", sql, label1)
			infos, _ := orm.QueryString(sqlstr)
			arr2 := make([]Subtree, 0, 10)
			for _, item := range infos {
				label2 := item["label2"]
				sqlstr := fmt.Sprintf("select label3,count from (%s) t where label1='%s' and label2='%s' ", sql, label1, label2)
				infos, _ := orm.QueryString(sqlstr)
				arr3 := make([]Node, 0, 10)
				for _, item := range infos {
					label3 := item["label3"]
					count, _ := strconv.Atoi(item["count"])
					arr3 = append(arr3, Node{Name: label3, Value: count})
				}
				arr2 = append(arr2, Subtree{Name: label2, Children: arr3})
			}
			arr1 = append(arr1, Tree{Name: label1, Children: arr2})
		}
		jsonByte, _ := json.Marshal(arr1)
		jsonStr := string(jsonByte)
		return jsonStr
	}
}

生成的json

[
    {
        "name":"原材料",
        "children":Array[2]
    },
    {
        "name":"工业品",
        "children":Array[2]
    },
    {
        "name":"服务",
        "children":Array[2]
    },
    {
        "name":"消费品",
        "children":[
            {
                "name":"食品饮品",
                "children":[
                    {
                        "name":"调味品添加剂",
                        "value":1
                    },
                    {
                        "name":"食品产业",
                        "value":11
                    },
                    {
                        "name":"食品加工",
                        "value":2
                    },
                    {
                        "name":"饮料",
                        "value":1
                    }
                ]
            }
        ]
    }
]

改进的通过一次读取数据库，避免之前的多次循环查询数据库