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

I am using SQL Server 2019

Ashok Yaganti
– Ashok Yaganti

2020-04-17 10:25:39 +00:00
Commented Apr 17, 2020 at 10:25 — Ashok Yaganti
– Ashok Yaganti, Commented Apr 17, 2020 at 10:25

Barbaros Özhan · Accepted Answer · 2020-04-17 12:28:58Z

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

edited Apr 17, 2020 at 12:28

answered Apr 17, 2020 at 10:50

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashok Yaganti Over a year ago

Is there any easy way to get the count? In the above Json, SP records are one and TP records 4 like that.

Barbaros Özhan Over a year ago

well, I don't know easier way, difficult to handle S, SP, TP ,by which arrays start and because of this, we cannot combine the paths such as $.S.SP.TP at one step @AshokYaganti .

Collectives™ on Stack Overflow

SQL Query on Json object inside multiple JSON objects

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related