title: Oracle json query nested array
body: <p>I would like to query this <a href="https://gist.github.com/gpietro/889a608342307bdb309f52b7794429c5" rel="nofollow noreferrer">json</a> example and select the questions.title and questions.answers[<em>].references[</em>].id (a string joined with "," is fine).</p>
  
  <p>So far I end up with the following, using json_table, with no success with reference ids:</p>
  
  <pre><code>select key_v, title, refs
  from ASSESSMENT a,
    json_table (ASSESSMENT , '$'
    columns (key_v FOR ORDINALITY,
      nested path '$."questions".*'
        columns (
            title path '$."title"',
            refs path '$.answers[*].references."eocnurse:interventions"[*].id',
        )));
  </code></pre>
  
  <p><strong>Desired result based on the example</strong></p>
  
  <p>"Cambiamento dello stato cognitivo" - "247, 77253"</p>
  
  <p>"Stato della circolazione" - "81, 24853608585"</p>
  
  <p>"Sonno e riposo" - "24853608585"</p>
  
  <p>The version of Oracle:
  Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production</p>
  
  <p><strong>UDPATE</strong></p>
  
  <p>I managed to modify the <a href="https://gist.github.com/gpietro/11f07d3b171118befeb02c8a4da32812" rel="nofollow noreferrer">json</a> data structure to be compliant with <strong>JSONpath</strong>.</p>
  
  <p>With the updated json structure this is the solution to my problem:</p>
  
  <pre><code>select JT.*
  from ASSESSMENT a,
    json_table (ASSESSMENT , '$.questions[*]'
    columns (
      "Title" PATH '$.title',
      "Refs" VARCHAR2(500 CHAR) FORMAT JSON WITH WRAPPER PATH '$.answers[*].references."eocnurse:interventions"[*].id'
    ))
    "JT";
  </code></pre>