从一个有多个根的列打开JSON值到一个表- SQL Server

Question

在SQL Server中，我想打开一个JSON，所有这些都在一列的不同行中，我还必须提到它们有多个根我的表(RelationTBL)就像:

tbody> <<tr>

PersonCode	Relation
961113021	[{"Name"{"DataType"string","value":"Sara"},"Relation":{"DataType"string","value":"Wife"}}]
971113052	[{"Name"{"DataType"string","value":"Sana"},"Relation":{"DataType"string","value":"Wife"}},{"Name"{"DataType"string","value":"Sara"},"Relation":{"DataType"string","value":"Son"}}]

我不知道你说的是什么意思…它们有多个根，因为存储的JSON是JSON对象的有效JSON数组，没有多个根项，所以在这种情况下，基于OPENJSON()的显式模式和APPLY操作符的方法是一种选择:

SELECT t.PersonCode, j.Name, j.Relation
FROM (VALUES
('961113021', '[{"Name":{"DataType":"string","value":"Sara"},"Relation":{"DataType":"string","value":"Wife"}}]'),
('971113052', '[{"Name":{"DataType":"string","value":"Sana"},"Relation":{"DataType":"string","value":"Wife"}},{"Name":{"Ben":"string","value":"Sara"},"Relation":{"DataType":"string","value":"Son"}}]')
) t (PersonCode, Relation)
OUTER APPLY OPENJSON(t.Relation) WITH (
Name varchar(100) '$.Name.value',
Relation varchar(100) '$.Relation.value'
) j

结果:

PersonCode  Name    Relation
961113021   Sara    Wife
971113052   Sana    Wife
971113052   Sara    Son

如果您真的想使用JSON_VALUE()，请使用以下语句:

SELECT 
t.PersonCode,
JSON_VALUE(j.[value], '$.Name.value') AS [Name],
JSON_VALUE(j.[value], '$.Relation.value') AS [Relation]
FROM (VALUES
('961113021', '[{"Name":{"DataType":"string","value":"Sara"},"Relation":{"DataType":"string","value":"Wife"}}]'),
('971113052', '[{"Name":{"DataType":"string","value":"Sana"},"Relation":{"DataType":"string","value":"Wife"}},{"Name":{"Ben":"string","value":"Sara"},"Relation":{"DataType":"string","value":"Son"}}]')
) t (PersonCode, Relation)
OUTER APPLY OPENJSON(t.Relation) j