How to split an array of objects in SQL?

I have a table in my database which has the following columns:

ID        Data
1        [{"na":false,"answer":1.5},{"na":false,"answer":0.5}]
2        [{"na":false,"answer":0.5},{"na":false,"answer":1}]
3        [{"na"":false,"answer":1.5},null]

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.

The maximum number of scores will be 2 i.e two objects. So, I will need to extract two columns called Score1 and Score2

The result of the select statement should be like this:

select ID, ‘some magic happens here’ as Score1, ‘some magic again’ as Score2 from score;

ID       Score1      Score2
1        1.5         0.5
2        0.5         1
3        1.5         0/null

Any help is appreciated. Thank you

Answer

If you use SQL Server 2016+, you may use JSON_VALUE() to parse the stored JSON and extract the values. Note, that the result from JSON_VALUE() is a single text value of type nvarchar(4000).

Table:

SELECT *
INTO JsonTable
FROM (VALUES
   (1,        '[{"na":false,"answer":1.5},{"na":false,"answer":0.5}]'),
   (2,        '[{"na":false,"answer":0.5},{"na":false,"answer":1}]'),
   (3,        '[{"na":false,"answer":1.5},null]')
) v (ID, Data)

Statement:

SELECT 
  t.ID, 
  JSON_VALUE(t.Data, '$[0].answer') AS Score1,
  JSON_VALUE(t.Data, '$[1].answer') AS Score2
FROM JsonTable t

Result:

ID Score1 Score2
------------------
1  1.5    0.5
2  0.5    1
3  1.5