title: Oracle extract json fields
body: <p>I have the below json. Is there any Oracle way to extract all the values under deviceNumber regarless of the previous fields?</p>
  <pre><code>{
     &quot;session&quot;:{
        &quot;sessionStartDate&quot;:&quot;2021-03-21T10:25:51+0000&quot;,
        &quot;interactionDate&quot;:&quot;2021-03-21T10:36:03+0000&quot;,
        &quot;sapGuid&quot;:&quot;XXXXXXXXXXXXXXXXXXXXXXX&quot;,
        &quot;agentId&quot;:&quot;XXXXXXXX&quot;,
        &quot;channel&quot;:&quot;UI5&quot;,
        &quot;bpNumber&quot;:&quot;XXXXXXXXX&quot;,
        &quot;contractAccountNumber&quot;:&quot;XXXXXXXXXX&quot;,
        &quot;contactDirection&quot;:&quot;&quot;,
        &quot;contactMethod&quot;:&quot;Z08&quot;,
        &quot;interactionId&quot;:&quot;XXXXXXXXXX&quot;,
        &quot;isResponsibleForPayingBill&quot;:&quot;Yes&quot;
     },
     &quot;payload&quot;:{
        &quot;agentId&quot;:&quot;XXXXXXX&quot;,
        &quot;contractAccountNumber&quot;:&quot;XXXXXXXXX&quot;,
        &quot;readings&quot;:{
           &quot;XXXXXXX&quot;:{
              &quot;registers&quot;:{
                 &quot;001&quot;:{
                    &quot;isPlausible&quot;:true,
                    &quot;deviceNumber&quot;:&quot;DEVICE_NUMBER&quot;,
                    &quot;register&quot;:&quot;001&quot;,
                    &quot;readingValue&quot;:&quot;08300&quot;,
                    &quot;readingDate&quot;:&quot;2021-03-21T00:00:00.000Z&quot;
                 }
              },
              &quot;errors&quot;:[
                 
              ],
              &quot;bpNumber&quot;:&quot;XXXXXXXX&quot;
           },
           &quot;XXXXXXX&quot;:{
              &quot;registers&quot;:{
                 &quot;001&quot;:{
                    &quot;isPlausible&quot;:true,
                    &quot;deviceNumber&quot;:&quot;DEVICENUMBER&quot;,
                    &quot;register&quot;:&quot;001&quot;,
                    &quot;readingValue&quot;:&quot;02635&quot;,
                    &quot;readingDate&quot;:&quot;2021-03-21T00:00:00.000Z&quot;
                 }
              },
              &quot;errors&quot;:[
                 
              ],
              &quot;bpNumber&quot;:&quot;XXXXX&quot;
           }
        }
     }
  }
  </code></pre>
  <p>I've already tried the below, but neither work:</p>
  <pre><code>JSON_QUERY(data, '$.payload.*.deviceNumber' WITH WRAPPER)
  JSON_QUERY(data, '$.*.deviceNumber' WITH WRAPPER)
  </code></pre>
  <p>My expected result would be</p>
  <pre><code>[&quot;DEVICE_NUMBER&quot;, &quot;DEVICE_NUMBER&quot;]
  </code></pre>
  <p>The json format is variable, so the below solution would not be valid:</p>
  <pre><code>JSON_QUERY(data, '$.payload.readings.*.registers.*.deviceNumber' WITH WRAPPER)
  </code></pre>