How to split an array of objects in SQL?

Question

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

the answer will differ depending on the database ,which dbms are you using? tag your database — eshirvana
– eshirvana, Commented May 10, 2021 at 13:58
Every database does that differently. Are you using SQLite, Oracle, MySQL5.x, MySQL8, SQL Server, etc, etc, etc? — MatBailie
– MatBailie, Commented May 10, 2021 at 14:01
Also, aiming for each score in a different column is a SQL Anti-Pattern. You should be aiming for each score to be on a different row, that's the structure the SQL language is designed to work with, and the structure database engines are optimised for. — MatBailie
– MatBailie, Commented May 10, 2021 at 14:04
Also double double quote doesn't look like valid json format ex:""answer"" — eshirvana
– eshirvana, Commented May 10, 2021 at 14:04
Please add this information to the question, not just comments, thanks. — MatBailie
– MatBailie, Commented May 10, 2021 at 14:08

Zhorov · Accepted Answer · 2021-05-10 14:16:40Z

1

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

edited May 10, 2021 at 14:16

answered May 10, 2021 at 14:14

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

the_new_guy Over a year ago

Thank you, it works. Really appreciate your help. Cheers

Collectives™ on Stack Overflow

How to split an array of objects in SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related