title: How to join multiple JSON columns in Sqlite
body: <p>I am looking for the Sqlite syntax to join multiple JSON columns, in the same table. I create a table like:-</p>
  
  <pre><code>SQL = "CREATE TABLE BranchStock (branch VARCHAR, oldstock JSON, newstock JSON)"
  ....
  </code></pre>
  
  <p>I then insert a few items like:-</p>
  
  <pre><code>INSERT INTO BranchStock VALUES('Melbourne','[{"catnumber":"ABC123","instock":5},{"catnumber":"BCD321","instock":3}]','[{"catnumber":"ABC123","instock":1},{"catnumber":"BCD321","instock":5}]');
  INSERT INTO BranchStock VALUES('Sydney','[{"catnumber":"ABC123","instock":7},{"catnumber":"XYZ567","instock":0}]','[{"catnumber":"ABC123","instock":5},{"catnumber":"XYZ567","instock":3}]');
  ....
  </code></pre>
  
  <p>Now I'm looking for the search sql to list which branches have the catnumber='ABC123' newstock or oldstock, and the instock amounts. Perhaps something like this:-</p>
  
  <pre><code>SELECT * FROM BranchStock 
     JOIN json_each(oldstock) ON json_valid(oldstock)
     JOIN json_each(newstock) ON json_valid(newstock)
     WHERE json_extract(value, '$.name')='ABC123');
  </code></pre>
  
  <p>The above works for one JOIN, but when I implement the second JOIN I get a syntax error.</p>
  
  <p>Does anyone know the correct syntax?
  Thanks in advance.</p>