-- JSON 字符串转 行记录
Declare @JsonStr Nvarchar(Max)='[{"State":0,"Name":"语文","ReMark":"了解国学信息","RepDtl":[{"ID":1,"Age":11},{"ID":2,"Age":12},{"ID":3,"Age":13}]},{"State":2,"Name":"数学","ReMark":"九天算术","RepDtl":[]},{"State":21,"Name":"英语","ReMark":"全球畅游","RepDtl":[{"ID":1,"Age":31},{"ID":2,"Age":32},{"ID":3,"Age":33}]}]'
转换处理 >> cross apply
select Row_Number()Over(Order by tmpB.State,tmpC.ID) RIDX, tmpB.State,tmpC.ID,tmpB.Name,tmpC.Age,tmpB.ReMark
from OpenJson(@JsonStr) tmpA
cross apply openjson(tmpA.value) with (
State Integer,
Name NVarchar(50),
ReMark NVarchar(50),
RepDtl NVarchar(Max) '$.RepDtl' as Json
) tmpB
cross apply openjson(tmpB.RepDtl) with (
ID Integer,
Age Integer
) tmpC
>> outer apply
