How to store nested JSON data in MYSQL database

[surya preethaaa]

i have more than 25,000 headwords for d3...that is almost 25,000 set of json..i want to save datas into mysql database and retrieve in json format which i will use in d3..
i dont know how should i store the nested json data into mysql to access it...it will be better if datas in a single table...but no idea,how to use the table for nested json values..
i just used here a sample nested json...how should i store the data in mysql to access it

sample nested JSON
{ "name":"par1", "children":[ { "name":"C 1", "children":[ { "name":"c 11" }, { "name":"c 12" }, { "name":"c 2", "children":[ { "name":"c 21" }, { "name":"c 22" } ] } ]

[Swastik Dey]

Ideally your first step should be to parse the json and store in a java class , then think about storing it in db.

[surya preethaaa]

i thing  , i can use.
http://tools.knowledgewalls.com/mysqltabletojavaclass.
and
http://tools.knowledgewalls.com/onlinejsontojavaobjectconverter
am i rite?

[Swastik Dey]

Probably yes.   There are lot of  ways to parse json

[surya preethaaa]

i used this tool

and the generated java class is

public final class Jj {
    public final String name;
    public final Children children[];

    @JsonCreator
    public jj(@JsonProperty("name") String name, @JsonProperty("children") Children[] children){
        this.name = name;
        this.children = children;
    }

    public static final class Children {
        public final String name;
        public final Children children[];

        @JsonCreator
        public Children(@JsonProperty("name") String name, @JsonProperty(value="children", required=false) Children[] children){
            this.name = name;
            this.children = children;
        }

        public static final class Children {
            public final String name;
            public final Children children[];
    
            @JsonCreator
            public Children(@JsonProperty("name") String name, @JsonProperty("children") Children[] children){
                this.name = name;
                this.children = children;
            }
    
            public static final class Children {
                public final String name;
        
                @JsonCreator
                public Children(@JsonProperty("name") String name){
                    this.name = name;
                }
            }
        }
    }
}

[Paul Clapham]

Okay... but your question is ambiguous. Do you have a database design already set up to contain that data, and you're bogged down in figuring out how to output the data to that database? Or are you confused about how to design a database to contain that data?

[surya preethaaa]

Actually,i have one table where some of the rows are null..

PARENT        CHILD1          CHILD2             CHILD3
paren     c1        c11
paren     c1         c12
paren     c1         c12                 c121
paren     c2         c21
paren     c2         c22
paren     c2         c23

when i use jsp to retrieve the data from the table and display in the json format ..IT gives me the output

[  
   {  
      "name":"paren",
      "children":[  
         {  
            "name":"c1"
         },
         "children":[  
            {  
               "name":"c12"
            },
            {  
               "name":"null"
            },
            "children":[  
               {  
                  "name":"null"
               }
            ]
         },
 
      ]
   },
 
]
},
 
]{  
"name":"paren",
"children":[  
{  
   "name":"c1"
},
"children":[  
   {  
      "name":"c12"
   },
   {  
      "name":"c121"
   },
   "children":[  
      {  
         "name":"null"
      }
   ]
},
 
]
},
 
]
},
 
]

@Swastik  suggest me to do this with SERVLET..i am new to servlet,so felt difficult to use servlet..thats why i tried to modify the table ,to see whether i get my required json format or not

[Bear Bibeault]

Does it really need to be broken down? Will you be joining on the columns? Or do you just need to store and retrieve the text? Why not just store it as a text blob?

[surya preethaaa]

my required json is ,what i posted in the question..i want to get it without broken..i mean
one common parent
children
sub children

i tried to use JSON OBJECT now

<%@page language="java" import="java.sql.*"%>
<%@page import="java.util.*" %>
<%@page contentType="text/html; charset=UTF-8"%>
<%@page import="org.json.simple.JSONArray"%>
<%@page import="org.json.simple.JSONObject"%>
<%@page import="org.json.simple.parser.JSONParser"%>
<%@page import="org.json.simple.parser.ParseException"%>
<%
  
    String sql  = "SELECT * FROM bitab";
 
    try
    {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn=null;
        conn=DriverManager.getConnection("jdbc:mysql://localhost/tree","root","admin321");
        ResultSet rs=null;
        Statement stm1=conn.createStatement();
 
        JSONArray list = new JSONArray();
        rs=stm1.executeQuery(sql);
        while(rs.next())
        {
            JSONObject obj=new JSONObject();
            obj.put("parent", rs.getString("parent"));
            obj.put("children1", rs.getString("child1"));
            obj.put("children2", rs.getString("child2"));
            obj.put("children3", rs.getString("child3"));
 
            list.add(obj);
        }
 
        out.print(list);
    }
    catch(Exception ex)
    {
        out.println("<h1>"+ex+"</g1>");
    }
%>

to get output in json format..
i get valid json

[
   {
      "parent":"paren",
      "children2":"c11",
      "children1":"c1",
      "children3":null
   },
   {
      "parent":"paren",
      "children2":"c12",
      "children1":"c1",
      "children3":null
   },
   {
      "parent":"paren",
      "children2":"c12",
      "children1":"c1",
      "children3":"c121"
   },
   {
      "parent":"paren",
      "children2":"c21",
      "children1":"c2",
      "children3":null
   },
   {
      "parent":"paren",
      "children2":"c22",
      "children1":"c2",
      "children3":null
   },
   {
      "parent":"paren",
      "children2":"c23",
      "children1":"c2",
      "children3":null
   }
]

but totally wrong when compare with my requirement..

[surya preethaaa]

i want nested json format from mysql database which i will use in d3 further

[surya preethaaa]

any suggestion for the problem i stated or a way to iterate thro mysql to avoid null in json and to have
common parent
               children
                         subchildren