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

Barbaros Özhan · Answer 1 · 2020-05-04T20:56:27.710

0

You need to use STRING_AGG() function, which applies to the DB version SQL Server 2017 and later, together with the below GROUP BY expression as

SELECT S1.ModelName, S2.ID  AS ObjectID, S2.Name AS ObjectName, 
       STRING_AGG(S3.TaskName, ',') WITHIN GROUP (ORDER BY TaskName) AS TaskName
  FROM <rest of your query>
 GROUP BY S1.ModelName, S2.ID, S2.Name

as a complete query :

SELECT S1.ModelName, S3.ObjectID, S3.ObjectName,
       STRING_AGG(S4.TaskName, ',') WITHIN GROUP (ORDER BY S4.TaskName) AS TaskName
  FROM TableA 
 CROSS APPLY OPENJSON(JsonData)
  WITH (ModelName NVARCHAR(255) '$.Model[0].ModelName') S1
 CROSS APPLY OPENJSON (JsonData) 
  WITH (Object NVARCHAR(MAX)    '$.Model[0].Object' AS JSON) S2
 CROSS APPLY OPENJSON (S2.Object)   
  WITH (ObjectID       INT           '$.ID',
        ObjectName     NVARCHAR(255) '$.Name',
        Task           NVARCHAR(MAX) '$.Task' AS JSON) S3 
 CROSS APPLY OPENJSON (S3.Task) 
  WITH (TaskID   NVARCHAR(MAX) '$.TaskID',
        TaskName NVARCHAR(MAX) '$.Name') S4
 GROUP BY S1.ModelName, S3.ObjectID, S3.ObjectName

Using WITHIN GROUP (ORDER BY TaskName) is optional, if you do not want ordering, then you can remove that part from the function as in the below demonstration :

Demo

edited May 04 '20 at 20:56

answered May 04 '20 at 16:54

Barbaros Özhan

59,113
10
31
55

With the above query the order is changing. I don't want to change the order. Is there anything we can modify? Also we have the possibility of duplicate Model Name, Object ID and Object Name. If we use Group by again there is an issue. – Ashok Yaganti May 04 '20 at 17:08
then just use `STRING_AGG(S3.TaskName, ',') AS TaskName` part @AshokYaganti – Barbaros Özhan May 04 '20 at 17:28
Even the order was changing. Is there any other way without using Group by? – Ashok Yaganti May 04 '20 at 17:38
I added a Demo and changed the query in the answer according to it @AshokYaganti – Barbaros Özhan May 04 '20 at 20:53
Barbaros Özhan the above query is correct but some edge case scenarios are failing. The below query gave me the correct results. Thanks for helping me on this. – Ashok Yaganti May 05 '20 at 05:50
Well, you're welcome @Ashok Yaganti . You still have possibility to upvote while marking the other question :) – Barbaros Özhan May 05 '20 at 06:36

score 0 · Accepted Answer · answered May 04 '20 at 19:37

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

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

2 Answers2