Not able to produce JSON from SQL for Master/Detail tables

Question

Following is the query:

SELECT  [C].[Id],
        [C].[Name],          
        [Module].[Id],  
        [Module].[Name]    
FROM [dbo].[Category] [C]       
INNER JOIN [CategoryModule] [CM]
 ON [C].[Id] = [CM].[CategoryId]
CROSS APPLY (SELECT [M].[Id], [M].[Name] FROM [Module] [M]
 WHERE [M].[Id] = [CM].[ModuleId]) [Module]
 WHERE [C].[Id]IN (1,2)
 FOR JSON AUTO

Output:

[
  {"Id":1,"Name":"Book","Module":[{"Id":1,"Name":"Unit"}]},
  {"Id":2,"Name":"Business","Module":[{"Id":1,"Name":"Unit"}]},
  {"Id":1,"Name":"Book","Module":[{"Id":2,"Name":"App"}]}
]

Need:

[
  {"Id":1,"Name":"Book","Module":[{"Id":1,"Name":"Unit"},{"Id":2,"Name":"App"}]},
  {"Id":2,"Name":"Business","Module":[{"Id":1,"Name":"Unit"}]}
]

It generate separate object for same master table entry.

Answer 1

Hi i think it's possible use FOR JSON AUTO, OPENJSON and JSON_MODIFY

I had the same problem but i finally find a solution

In your case try this

SELECT  
JSON_MODIFY (
    (SELECT value FROM  OPENJSON((SELECT * FROM Category  WHERE Id = C.Id FOR JSON AUTO),'$')),
    '$.Module',
    (SELECT M.Id, M.Name FROM CategoryModule CM INNER JOIN Module M ON CM.ModuleId= M.Id WHERE CM.CategoryId= C.Id FOR JSON AUTO)
) AS Category 
FROM Category C
FOR JSON AUTO 

The Ouput is

[{
    "Category": {
        "Id": 1,
        "Name": "Book",
        "Module": [{
            "Id": 1,
            "Name": "Unit"
        }, {
            "Id": 2,
            "Name": "App"
        }]
    }
}, {
    "Category": {
        "Id": 2,
        "Name": "Business",
        "Module": [{
            "Id": 1,
            "Name": "Unit"
        }]
    }
}]