title: How to return nested json in one row using JSON_TABLE
body: <p>I am struggling with the following query. I have json and it is splitting the duration and distance elements over two rows. I need them in one and cannot see where to change this.</p>
  <p>The following will just work from SQL*Plus etc (Oracle 20.1)</p>
  <pre><code>select * 
  from json_table(
  '{
     &quot;destination_addresses&quot; : [ &quot;Page St, London SW1P 4BG, UK&quot; ],
     &quot;origin_addresses&quot; : [ &quot;Corbridge Drive, Luton, LU2 9UH, UK&quot; ],
     &quot;rows&quot; : [
        {
           &quot;elements&quot; : [
              {
                 &quot;distance&quot; : {
                    &quot;text&quot; : &quot;88 km&quot;,
                    &quot;value&quot; : 87773
                 },
                 &quot;duration&quot; : {
                    &quot;text&quot; : &quot;1 hours 25 mins&quot;,
                    &quot;value&quot; : 4594
                 },
                 &quot;status&quot; : &quot;OK&quot;
              }
           ]
        }
     ],
     &quot;status&quot; : &quot;OK&quot;
  }', 
      '$' COLUMNS
        (
           origin_addresses      varchar2(1000 char) path '$.origin_addresses[*]',
           destination_addresses varchar2(1000 char) path '$.destination_addresses[*]',
           nested path '$.rows.elements.distance[*]' 
           COLUMNS(
              distance_text  varchar2(100) path '$.text',
              distance_value varchar2(100) path '$.value'
                  ),
           nested path '$.rows.elements.duration[*]' 
           COLUMNS(
              duration_text varchar2(100) path '$.text',
              duration_value varchar2(100) path '$.value'
                  )                  
        )
  );
  </code></pre>
  <p>Mathguy: I don't have influence over the JSON that Google returns but origin and destinations are arrays it is just this search is from A to Z and not A,B,C to X,Y,Z for example which would return 9 results instead of 1</p>
  <p><a href="https://i.stack.imgur.com/snRJ0.png" rel="nofollow noreferrer">See results here</a></p>