如何在SQL中拆分对象数组

Question

我的数据库中有一个表，它包含以下列：

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]

现在，我想在select语句中将数据列拆分为两列(在其他地方使用(。第一列Score1将具有存在于第一对象的答案字段中的数据，即对于ID＝1为1.5，第二列Score2将具有存在于第二对象的答案域中的数据(即对于ID=1为0.5(，如果存在null，则该值应为null/0。

分数的最大数量为2，即两个对象。因此，我需要提取两个名为Score1和Score2 的列

select语句的结果应该是这样的：

从分数中选择ID，"这里发生了一些魔法"作为分数1，"又发生了一些魔术"作为分数2；

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

感谢您的帮助。谢谢

Answer 1

如果使用SQL Server 2016+，则可以使用JSON_VALUE()解析存储的JSON并提取值。注意，来自JSON_VALUE()的结果是类型为nvarchar(4000)的单个文本值。

表：

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)

声明：

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

结果：

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