SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab( JsonData VARCHAR(8000) );
INSERT INTO tab VALUES('{
   "S": [ { "Name": "0219 Project Methodology - Allergies",
            "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": "TT3" }] }  
                  ]
          }

]}');
  
1 rows affected

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

   


TID
TName


11
TT1

12
TT2

13
TT3

14
TT3