SQL Query for Json object inside Json

Question

I have the below Json string. I need to write a query to get the SP records. Without providing the index value, we need get the result.

 {
   "S": [
    {
      "Name": "Project1",
       "SP": [
        {
          "ID": 1,
          "Name": "Test1"
        },
        {
          "ID": 2,
          "Name": "Test2"
        },
  }]}

How do I query to get the SP values.

Expected Result:

 ID    Name 
 1     Test1 
 2     Test2

 I tried the below but not working. Can you please suggest the correct query.

SELECT DISTINCT JSON_VALUE(JsonData, '$.S[0].SP.ID') AS ID,
                JSON_VALUE(JsonData, '$.S[0].SP.Name') AS Name
    FROM TableA

Answer 1

You can use JSON_QUERY nested in OPENJSON function containing WITH Clause in order to visit all members of SP array dynamically :

SELECT ID, Name
  FROM TableA
 CROSS APPLY OPENJSON(JSON_QUERY(JsonData, '$.S[0].SP'))
             WITH (ID   nvarchar(500) '$.ID',
                   Name nvarchar(500) '$.Name')

Btw, you need to fix the JsonData as converting to

{
   "S": [
    {
      "Name": "0219 Project Methodology - Allergies",
       "SP": [
        {
          "ID": 1,
          "Name": "Test1"
        },
        {
          "ID": 2,
          "Name": "Test2"
        }
  ] } ] }

Demo