将JSON数据拉入视图SSMS

Question

我有一个列在SQL Server与JSON数据在这种格式:

{"FormCode":"1224","Answers":[{"Type":"FirstName","Values":["Tony"]},{"Type":"LastName","Values":
["Tiger"]},{"Type":"IsExistingPatient","Values":["Maybe"]},{"Type":"Race","Values":["RaceValue1","RaceValue2"]}]}

我正试图将这些数据拉到一个视图中，每个人口统计数据都在它自己的列中。例如:

tbody> <<tr>

表。ClientID	ExistingPatient	比赛
987654321	或者	RaceValue1, RaceValue2

如果你只寻找Race值的第一个元素，那么你可以使用一个额外的cross apply，像这样:

select
Id,
json_value(answer1.Value, '$.Values[0]') as ExistingPatient,
json_value(answer2.Value, '$.Values[0]') as Race
from dbo.[Table]
cross apply openjson(JsonValue, '$.Answers') answer1
cross apply openjson(JsonValue, '$.Answers') answer2
where json_value(answer1.Value, '$.Type') = N'IsExistingPatient'
and json_value(answer2.Value, '$.Type') = N'Race'

收益率:

IdExistingPatient比赛或者RaceValue1