SQL Query to get the Json inside Json values by comma separated

Question

I have the below Json object. I need to get the task names by comma (,) separated.

{
  "Model": [
    {
      "ModelName": "Test Model",    
      "Object": [
         {
           "ID": 1,
           "Name": "ABC",
           "Task" : [
             {
                TaskID : 1222,
                Name: "TaskA"
             },
             {
                TaskID : 154,
                Name: "TaskB"
             }
           ]
         },
         {
           "ID": 11,
           "Name": "ABCD",
           "Task" : [
             {
                TaskID : 222,
                Name: "TaskX"
             },
             {
               TaskID : 234,
               Name: "TaskY"
             }
           ]
        },         
     ]  
 }]}

The expected Output should be in the below table. I need the task names should be comma separated.

ModelName   ObjectID   ObjectName  TaskName
Test Model     1          ABC      TaskA, TaskB
Test Model     11         ABCD     TaskX, TaskY

I tried the below query. But I don't know how to group Task names.

                  SELECT   S1.ModelName,
                           S2.ID  AS ObjectID, 
                           S2.Name AS ObjectName, 
                           S3.TaskName
                     FROM TableA 
                       CROSS APPLY OPENJSON(JsonData)
                         WITH (Model NVARCHAR(MAX) '$.Model[0]' AS JSON) S1
                       CROSS APPLY OPENJSON (S1.Model) 
                         WITH (Object NVARCHAR(MAX) '$.Object' AS JSON,
                               ID  INT '$.ID',
                               Name NVARCHAR(250) '$.Name') S2
                       CROSS APPLY OPENJSON (S2.Object) 
                         WITH (Task NVARCHAR(MAX) '$.Task' AS JSON ,
                               TaskName NVARCHAR(MAX) '$.TaskName') S3  

Answer 1

You need to use STRING_AGG() to aggregate the text values and one possible approach (based on the attempt in the question) is the following statement. The aggregation of the task names is for each item in the Object JSON array:

Table:

CREATE TABLE TableA (JsonData varchar(max))
INSERT INTO TableA (JsonData) VALUES ('{
  "Model": [
    {
      "ModelName": "Test Model",    
      "Object": [
         {
           "ID": 1,
           "Name": "ABC",
           "Task" : [
             {
                "TaskID" : 1222,
                "Name": "TaskA"
             },
             {
                "TaskID" : 154,
                "Name": "TaskB"
             }
           ]
         },
         {
           "ID": 11,
           "Name": "ABCD",
           "Task" : [
             {
                "TaskID" : 222,
                "Name": "TaskX"
             },
             {
               "TaskID" : 234,
               "Name": "TaskY"
             }
           ]
        }         
     ]  
 }]}')

Statement:

SELECT 
   j1.ModelName,
   j2.ObjectID, j2.ObjectName,
   c.TaskName
FROM TableA t
CROSS APPLY OPENJSON(t.JsonData, '$.Model[0]') WITH (
   ModelName varchar(50) '$.ModelName',
   Object nvarchar(max) '$.Object' AS JSON
) j1
CROSS APPLY OPENJSON(j1.Object, '$') WITH (
   ObjectID int '$.ID',
   ObjectName varchar(50) '$.Name',
   Task nvarchar(max) '$.Task' AS JSON
) j2
CROSS APPLY (
   SELECT STRING_AGG([Name], ',') AS TaskName
   FROM OPENJSON (j2.Task, '$') WITH (Name varchar(50) '$.Name')
) c