SQL Query on Json object inside multiple JSON objects

Question

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

Answer 1

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

Demo

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  

Demo