title: mySQL JSON_TABLE query to return nested data
body: <p>I want to take the data from here:  <a href="https://raw.githubusercontent.com/usnistgov/oscal-content/master/examples/ssp/json/ssp-example.json" rel="nofollow noreferrer">https://raw.githubusercontent.com/usnistgov/oscal-content/master/examples/ssp/json/ssp-example.json</a></p>
  <p>which I've pulled into a mySQL database called &quot;ssp_models&quot; into a JSON column called 'json_data', and I need to retrieve the 'name' and 'type' values from the 'parties' node which is nested 3 levels deep.</p>
  <p>I've been trying to follow this blog-post about how to retrieve nested data:  <a href="https://mysqlserverteam.com/json_table-the-best-of-both-worlds/" rel="nofollow noreferrer">https://mysqlserverteam.com/json_table-the-best-of-both-worlds/</a></p>
  <p>and I am struggling with the nesting selection process.  Obviously this is not the right way:</p>
  <pre><code>SELECT &quot;system-security-plan.*&quot; 
  FROM ssp_models, 
       JSON_TABLE(json_data, '$.metadata[*]' COLUMNS (
            NESTED PATH '$.parties[*]' COLUMNS (
                  name VARCHAR(140)  PATH '$.name',
                  type VARCHAR(140)  PATH '$.type')
       )) parties;
  </code></pre>
  <p>Presently, I get nothing back:</p>
  <p>MySQL returned an empty result set (i.e. zero rows). (Query took 0.0024 seconds.)</p>
  <p>It <em>seems</em> like it should be close to working, as I am pulling everything from the &quot;system-security-plan&quot; node and querying based on the metadata -&gt; parties nodes to retrieve 'name' and 'type'.  What am I missing?</p>
  <p>Any assistance is greatly appreciated.</p>