title: How to split an array of objects in SQL?
body: <p>I have a table in my database which has the following columns:</p>
  <pre><code>ID        Data
  1        [{&quot;na&quot;:false,&quot;answer&quot;:1.5},{&quot;na&quot;:false,&quot;answer&quot;:0.5}]
  2        [{&quot;na&quot;:false,&quot;answer&quot;:0.5},{&quot;na&quot;:false,&quot;answer&quot;:1}]
  3        [{&quot;na&quot;&quot;:false,&quot;answer&quot;:1.5},null]
  
  </code></pre>
  <p>Now, I want to split the data column into two columns in a select statement (which is used elsewhere). The first column Score1 will have the data present in the first object's answer field i.e 1.5 for the ID=1 and the second column Score2 will have the data present in the second object's answer field i.e 0.5 for ID=1, if there is null then the value should be null/0.</p>
  <p>The maximum number of scores will be 2 i.e two objects. So, I will need to extract two columns called Score1 and Score2</p>
  <p>The result of the select statement should be like this:</p>
  <p>select ID, 'some magic happens here' as Score1, 'some magic again' as Score2 from score;</p>
  <pre><code>ID       Score1      Score2
  1        1.5         0.5
  2        0.5         1
  3        1.5         0/null
  
  </code></pre>
  <p>Any help is appreciated. Thank you</p>