I have the below Json string. I need to write a query to get the TP records. Without providing the index value, we need get the result.
{ "S": [ { "Name": "Project1", "SP": [ { "ID": 1, "Name": "Test1", "TP": [ { "TID": 11, "TName": "TT1", }, { "TID": 12, "TName": "TT2", }, ] }, { "ID": 2, "Name": "Test2", "TP": [ { "TID": 13, "TName": "TT3", }, { "TID": 14, "TName": "TT4", }, ] }, ]}]}
How do I query to get the TP values.
Expected Result:
TID TName 11 TT1 12 TT2 13 TT3 14 TT4
Advertisement
Answer
You can use OPENJSON
function containing WITH
Clause added as many CROSS APPLY
Clause as upto see all sub-arrays :
SELECT S3.TID, S3.TName FROM tab CROSS APPLY OPENJSON(JsonData) WITH ( S nvarchar(max) '$.S' AS JSON) AS S0 CROSS APPLY OPENJSON (S0.S) WITH ( SP nvarchar(max) '$.SP' AS JSON ) S1 CROSS APPLY OPENJSON (S1.SP) WITH ( TP nvarchar(max) '$.TP' AS JSON ) S2 CROSS APPLY OPENJSON (S2.TP) WITH ( TID nvarchar(500) '$.TID', TName nvarchar(500) '$.TName' ) S3
Update : If the first array S
is fixed to contain only one item, we can reduce one-step as
SELECT S3.TID, S3.TName FROM tab CROSS APPLY OPENJSON(JsonData) WITH ( SP nvarchar(max) '$.S[0].SP' AS JSON ) S1 CROSS APPLY OPENJSON (S1.SP) WITH ( TP nvarchar(max) '$.TP' AS JSON ) S2 CROSS APPLY OPENJSON (S2.TP) WITH ( TID nvarchar(500) '$.TID', TName nvarchar(500) '$.TName' ) S3