{
‘id’ :1,
‘name’: ‘Yurivanno Gani’,
‘title’: ‘Area Manager’,
‘children’: [
{ ‘id’ :2,‘name’: ‘Riva’, ‘title’: ‘Agen’,
‘children’: [{ ‘id’ :3,‘name’: ‘Andra’, ‘title’: ‘Agen’ }]
},
{ ‘id’ :4,‘name’: ‘Yasin’, ‘title’: ‘Agen’,
‘children’: [
{ ‘id’ :5,‘name’: ‘Masdar’, ‘title’: ‘Agen’ },
{ ‘id’ :6,‘name’: ‘Dita’, ‘title’: ‘Agen’,
‘children’: [
{ ‘id’ :7,‘name’: ‘Lintang’, ‘title’: ‘Agen’ },
{ ‘id’ :8,‘name’: ‘Rahmat’, ‘title’: ‘UE Agen’ }
]
}
]
},
{ ‘id’ :9,‘name’: ‘Rosyid’, ‘title’: ‘Agen’ },
{ ‘id’ :10,‘name’: ‘Andi’, ‘title’: ‘Agen’ }
]
};
Could you please wrap your code in [code]...[/code]?
{
'id' :1,
'name': 'Yurivanno Gani',
'title': 'Area Manager',
'children': [
{ 'id' :2,'name': 'Riva', 'title': 'Agen',
'children': [{ 'id' :3,'name': 'Andra', 'title': 'Agen' }]
},
{ 'id' :4,'name': 'Yasin', 'title': 'Agen',
'children': [
{ 'id' :5,'name': 'Masdar', 'title': 'Agen' },
{ 'id' :6,'name': 'Dita', 'title': 'Agen',
'children': [
{ 'id' :7,'name': 'Lintang', 'title': 'Agen' },
{ 'id' :8,'name': 'Rahmat', 'title': 'UE Agen' }
]
}
]
},
{ 'id' :9,'name': 'Rosyid', 'title': 'Agen' },
{ 'id' :10,'name': 'Andi', 'title': 'Agen' }
]
};What structure do the DB tables have you want to create this JSON from? What SQL query do you use to get an unlimited result?
If agencylevel_parent = “Y” then is Top Parent
For attribute id, name and title join using profile table
OK, but how do you get unlimited nesting from this using SQL?
I dont know, Thats why i ask it how to create unlimited nested json using golang 
Take a look at https://play.golang.org/p/czRv7U2sZs
package main
import (
"encoding/json"
"log"
)
// An Employee can have many children.
type Employee struct {
ID int
Name string
Title string
Children []Employee
}
var j = `{
"id" :1,
"name": "Yurivanno Gani",
"title": "Area Manager",
"children": [
{ "id" :2,"name": "Riva", "title": "Agen",
"children": [{ "id" :3,"name": "Andra", "title": "Agen" }]
},
{ "id" :4,"name": "Yasin", "title": "Agen",
"children": [
{ "id" :5,"name": "Masdar", "title": "Agen" },
{ "id" :6,"name": "Dita", "title": "Agen",
"children": [
{ "id" :7,"name": "Lintang", "title": "Agen" },
{ "id" :8,"name": "Rahmat", "title": "UE Agen" }
]
}
]
},
{ "id" :9,"name": "Rosyid", "title": "Agen" },
{ "id" :10,"name": "Andi", "title": "Agen" }
]
}`
func main() {
bytes := []byte(j)
e := &Employee{}
err := json.Unmarshal(bytes, e)
if err != nil {
log.Fatal(err)
}
log.Println(e)
}Output:
2009/11/10 23:00:00 &{1 Yurivanno Gani Area Manager [{2 Riva Agen [{3 Andra Agen []}]} {4 Yasin Agen [{5 Masdar Agen []} {6 Dita Agen [{7 Lintang Agen []} {8 Rahmat UE Agen []}]}]} {9 Rosyid Agen []} {10 Andi Agen []}]}Thanks for your answer, but the point is how to output like that from query sql ?
I’m working on an example now…
To build this example I had to start by creating a database. I used sqlite3 with an in memory db and seeded it with the data shown in the example json.
The interesting function is getEmployee. It is, as requested, unlimited. That means that if the database contains a cycle (that is some employee it its own parent, or grandparent, etc.,) it will never return.
Also, be aware the error handing is good for prototyping, but should not be done this way in production as the program is killed immediately when an error is encountered.
I used the same employee type as @lutzhorn.
Now, on with the code:
package main
import (
"database/sql"
"encoding/json"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
type employee struct {
ID int `json:"id"`
Name string `json:"name"`
Title string `json:"title"`
Children []employee `json:"children,omitempty"`
}
func main() {
log.SetFlags(log.Lshortfile)
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
log.Fatalln(err)
}
seedDb(db)
e := getEmployee(db, 1)
b, err := json.MarshalIndent(e, "", " ")
if err != nil {
log.Fatalln(err)
}
os.Stdout.Write(b)
}
func seedDb(db *sql.DB) {
_, err := db.Exec("create table profiles (id integer primary key, name, title, parent integer references profiles(id));")
if err != nil {
log.Fatalln(err)
}
records := [][]interface{}{
{1, "Yurivanno Gani", "Area Manager", nil},
{2, "Riva", "Agen", 1},
{3, "Andra", "Agen", 2},
{4, "Yasin", "Agen", 1},
{5, "Masdar", "Agen", 4},
{6, "Dita", "Agen", 4},
{7, "Lintang", "Agen", 6},
{8, "Rahmat", "UE Agen", 6},
{9, "Rosyid", "Agen", 1},
{10, "Andi", "Agen", 1},
}
for _, record := range records {
_, err := db.Exec("insert into profiles (id, name, title, parent) values (?, ?, ?, ?)", record...)
if err != nil {
log.Fatalln(err)
}
}
}
func getEmployee(db *sql.DB, id int) employee {
var e employee
err := db.QueryRow("select id, name, title from profiles where id = ?", id).Scan(&e.ID, &e.Name, &e.Title)
if err != nil {
log.Fatalln(err)
}
var children []int
rows, err := db.Query("select id from profiles where parent = ?", id)
if err != nil {
log.Fatalln(err)
}
for rows.Next() {
var child int
err := rows.Scan(&child)
if err != nil {
log.Fatalln(err)
}
children = append(children, child)
}
for _, childID := range children {
e.Children = append(e.Children, getEmployee(db, childID))
}
return e
}
and the output:
{
"id": 1,
"name": "Yurivanno Gani",
"title": "Area Manager",
"children": [
{
"id": 2,
"name": "Riva",
"title": "Agen",
"children": [
{
"id": 3,
"name": "Andra",
"title": "Agen"
}
]
},
{
"id": 4,
"name": "Yasin",
"title": "Agen",
"children": [
{
"id": 5,
"name": "Masdar",
"title": "Agen"
},
{
"id": 6,
"name": "Dita",
"title": "Agen",
"children": [
{
"id": 7,
"name": "Lintang",
"title": "Agen"
},
{
"id": 8,
"name": "Rahmat",
"title": "UE Agen"
}
]
}
]
},
{
"id": 9,
"name": "Rosyid",
"title": "Agen"
},
{
"id": 10,
"name": "Andi",
"title": "Agen"
}
]
}
I think that matches the data from the example json (though the formatting is different). I used jsonMarshalIndent to make it easier to read. Use json.Marshal in production to save on bytes.
1 Like
Youre so genius, thanks your code it works
First unmarshal the json like @lutzhorn showed, then do something like:
func insertEmployee(db *sql.DB, e employee, parent *int) {
var err error
if parent != nil {
_, err = db.Exec("insert into profiles (id, name, title, parent) values (?, ?, ?, ?)", e.ID, e.Name, e.Title, parent)
} else {
_, err = db.Exec("insert into profiles (id, name, title) values (?, ?, ?)", e.ID, e.Name, e.Title)
}
if err != nil {
log.Fatalln(err)
}
for _, child := range e.Children {
insertEmployee(db, child, &e.ID)
}
}
The sql depends heavily on how your database is designed. What I used here works with my previous example.
You also need to deal with employees that are already in the database (e.g., should they be updated, replaced, skipped, etc).
A general pattern in dealing with data that refers to itself, like you have here, is to use functions that call themselves. Both the data and the functions are examples of recursion.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.