title: Oracle 12C - JSON_TABLE - How to return other columns in the table when json column is null
body: <p>There is a table which has a CLOB JSON column:</p>
  <pre><code> CREATE TABLE STUDENT
  (
    NAME VARCHAR2(32 BYTE),
    ADDRESS VARCHAR2(30 BYTE),
    PAYMENT_JS       CLOB
  );
  
  ALTER TABLE STUDENT ADD
  CONSTRAINT CK_PAYMENT_JS
    CHECK (&quot;PAYMENT_JS&quot; IS JSON (LAX));
  </code></pre>
  <p>PAYMENT_JS is a HUGECLOB which has JSON data of the below format:</p>
  <pre><code>{
  &quot;pays&quot;: [{
          &quot;payCode&quot;: {
              &quot;code&quot;: &quot;OTHER&quot;
          },
          &quot;payValue&quot;: {
              &quot;amount&quot;: 0.0,
              &quot;currencyCode&quot;: &quot;USD&quot;
          }
      },
      {
          &quot;payCode&quot;: {
              &quot;code&quot;: &quot;COST_OF_STUDY&quot;
          },
          &quot;payValue&quot;: {
              &quot;amount&quot;: 2395.29,
              &quot;currencyCode&quot;: &quot;USD&quot;
          }
      }]
  }
  </code></pre>
  <p>I'm using JSON_TABLE to get data from PAYMENT_JS column and other columns in the table:</p>
  <pre><code>select 
  e.name AS stud_name,
  e.address AS stud_address,
  j.code
  FROM
  STUDENT e,
  JSON_TABLE ( payment_js,'$.pays[*]' 
  COLUMNS (
  code VARCHAR2 ( 100 CHAR ) PATH '$.payCode.code' ,
  amount NUMBER ( 10,2 ) PATH '$.payValue.amount' ,
  currencycode VARCHAR2 ( 4 CHAR ) PATH '$.payValue.currencyCode' 
  )
  )  
  j
  </code></pre>
  <p>But when the PAYMENT_JS column doesn't have data, those rows' NAME and ADDRESS are also not getting retrieved.</p>
  <p>How do we retrieve data for other columns in the table when the JSON column is empty?</p>
  <p>I tried NULL ON EMPTY but doesnt work:</p>
  <pre><code>select 
  e.name AS stud_name,
  e.address AS stud_address,
  j.code
  FROM
  STUDENT e,
  JSON_TABLE ( payment_js,'$.pays[*]' 
  COLUMNS (
  code VARCHAR2 ( 100 CHAR ) PATH '$.payCode.code' NULL ON EMPTY,
  amount NUMBER ( 10,2 ) PATH '$.payValue.amount' NULL ON EMPTY,
  currencycode VARCHAR2 ( 4 CHAR ) PATH '$.payValue.currencyCode'  NULL ON EMPTY
  )
  )
  j 
  </code></pre>
  <p>My output should look like this:</p>
  <pre><code>stud_name, stud_address,code
  -----------------------------
  Maria, 1 Avenue NY,OTHER
  David, 2 Avenue NY,COST_OF_STUDY
  Kavya, 3 Avenue NY
  </code></pre>
  <p>Here, the JSON column is empty for Kavya.</p>
  <p>But Oracle returns the below output for the query:</p>
  <pre><code>stud_name, stud_address,code
  -----------------------------
  Maria, 1 Avenue NY,OTHER
  David, 2 Avenue NY,COST_OF_STUDY
  </code></pre>
  <p>I'm using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0</p>