title: Generate Oracle JSON from CLOB datatype column
body: <p>Requirement is to generate JSON from clob data type column.
  environment version Oracle 12.2</p>
  <p>I have a table with fields id (number data type) and details (clob type) like below</p>
  <pre><code>ID   - details 
  
  100  - 134332:10.0, 1481422:1.976, 1483734:1.688, 2835036:1.371
  
  101  - 134331:0.742, 319892:0.734, 1558987:0.7, 2132090:0.697
  </code></pre>
  <p>eg output:</p>
  <pre><code>{
     &quot;pId&quot;:100,
     &quot;cid&quot;:[
        {
           &quot;cId&quot;:134332,
           &quot;wt&quot;:&quot;10.0&quot;
        },
        {
           &quot;cId&quot;:1481422,
           &quot;wt&quot;:&quot;1.976&quot;
        },
        {
           &quot;cId&quot;:1483734,
           &quot;wt&quot;:&quot;1.688&quot;
        },
        {
           &quot;cId&quot;:2835036,
           &quot;wt&quot;:&quot;1.371&quot;
        }
     ]
  }
  </code></pre>
  <p>please help with oracle SQL query to generate output.</p>