title: how do you read a mySQL column defined as json (a list) into a Java program as a list?
body: <p>I have a list of lists, each of which contains two strings.  (Sort of like a tuple, but really just a list of two things.) This is what one of these looks like:</p>
  <pre><code>[[&quot;SLQ&quot;, &quot;fee&quot;], [&quot;MER&quot;, &quot;fie&quot;], [&quot;RMG&quot;, &quot;fo&quot;], [&quot;ATN&quot;, &quot;fum&quot;] ...etc. ]
  </code></pre>
  <p>I've managed to insert this list of lists into a mySQL table; into a column defined as 'json'. Here's the description of the table in mySQL: (note the field 'tuplelist'.)</p>
  <p>mysql&gt; describe tuplelist;<br />
  key_tuplelist    | int       | NO   | PRI   | NULL | auto_increment<br />
  key_alternate    | int       | NO   |       | NULL |<br />
  abbr             | varchar(5)| NO   |       | NULL |<br />
  chapter          | int       | NO   |       | NULL |<br />
  verse            | int       | NO   |       | NULL |<br />
  tuplelist        | json      | NO   |       | NULL |<br />
  date_modified    | datetime  | NO   |       | NULL | on update CURRENT_TIMESTAMP</p>
  <p>8 rows in set (0.01 sec)</p>
  <p>When using python, I can insert into the mySQL table utilizing json.dumps for the 'tuplelist' column:</p>
  <pre><code>import json
  
  * * *
  
  ## -------------------------------------------------------------------
  ## insert to tuplelist table
  
      SQL=sql.SQLinsertjson(&quot;tuplelist&quot;,keyverse,abbr,chapter,verse,json.dumps(tuplelist,ensure_ascii=False))
      with sql(dbconfig) as cursor: 
          cursor.execute(SQL)
          run.add('tuplelist rows written', 1)
      
  </code></pre>
  <p>Previous to utilizing json, I was writing the list to the table as a String.  That meant I had to retrieve it as a String and parse it back into a List by splitting the String, getting rid of the brackets, apostrophes, etc.</p>
  <p>Again in python, after querying the table, I can use json.loads when pulling back the column I want defined as a List.  Here's the query in the python script...</p>
  <pre><code>## -------------------------------------------------------------------
  ## query tuplelist table
  
  SQL=sql.SQLquery(&quot;tuplelist&quot;)
  
  with sql(dbconfig) as cursor: 
      cursor.execute(SQL)
      tuples=cursor.fetchall()
  
      print (SQL)
      print (str(len(tuples)), &quot;tuplelist rows returned&quot;)
  </code></pre>
  <p>system output:</p>
  <pre><code>$ select key_tuplelist, key_verse, abbr, chapter, verse, tuplelist from tuplelist where key_verse &gt; 1
  $ 4464 tuplelist rows returned
  </code></pre>
  <p>... and here's my grab of the column as a list. By invoking json.loads on the column defined as 'json' in the table, I've got myself a python List upon which I can immediately iterate.</p>
  <pre><code>for row in tuples:
  
      tablekey  =row[0]
      tuplelist = json.loads(row[6])    
      
      for tup in tuplelist:
      &lt;do stuff...&gt;
  </code></pre>
  <p>I want to be able to do the same thing in a Java gui I'm writing that accesses the same table.  At this point - in Java - I've only been able to read the list column in as a String and parse the String into a List using the following awkward code:</p>
  <pre><code>public void getTuples() {   
      Select sel     = new Select();   # &lt;--- a class containing the various mySQL methods I'm using...
      ResultSet rs = sel.tuples( &lt;select criteria&gt; ) ;                
          
      try {
          while (rs.next()) {
           
              String   key        = rs.getString(&quot;key_verse&quot;);
              String   abbr       = rs.getString(&quot;abbr&quot;);
              Integer  chapter    = rs.getInt(&quot;chapter&quot;);
              Integer  verse      = rs.getInt(&quot;verse&quot;);
              String   tuples     = rs.getString(&quot;tuplelist&quot;);                                    
               
              // I WANT THE ABOVE LINE TO BE SOMETHING LIKE...
  
              List     tuples     = rs.json.loads(&quot;tuplelist&quot;);                                   
  
              // ...AND AS SUCH, BE ABLE TO GET RID OF THE FOLLOWING THREE LINES:
  
              tuples = tuples.replaceAll(&quot;\\[\\[&quot;, &quot;&quot;).replaceAll(&quot;\\]\\]&quot;,&quot;&quot;);
              String splitter=&quot;\\], \\[&quot;;             
              String [] tuplelist = tuples.split(splitter);
              
              tuplex.add(new Quintet&lt;String, String, Integer, Integer, String[]&gt;(key,abbr,chapter,verse,tuplelist));
                 
          }
      } 
      catch (SQLException e1) {
    
          e1.printStackTrace();
  
      }
  }
  </code></pre>
  <p>I've found advice on how to read in Json data and use references built into the Json structure in order to read portions of the Json data, but this seems way beyond what I'm trying to do.  I just want to use Java to query a table containing a Json field - a simple list of lists - and read in the json list as a Java List, ArrayList, what have you...</p>