How to avoid nested keys and values while joining tables and using 'for json auto'

Question

I am trying to retrieve some data from joined tables in JSON format. Currently I'm trying this:

select top(1) 
    a.IdUser , 
    b.UserName , 
    a.IdGroup , 
    c.Title 
from UserGroupMembers as a 
inner join users as b on a.IdUser= b.iduser 
inner join UserGroups as c on a.IdGroup = c.IdUserGroup
for json auto 

which returns this:

[{"IdUser":1,"IdGroup":8,"b":[{"UserName":"User1","c":[{"Title":"Group1"}]}]}]

while I need to get this:

[{"IdUser":1,"IdGroup":8, "UserName":"User1", "Title":"Group1" }]

or at least this:

[{"IdUser":1,"IdGroup":8,"b":[{"UserName":"User1"}] ,"c":[{"Title":"Group1"}] }]

Answer 1

You need to use FOR JSON PATH instead of FOR JSON AUTO:

Tables:

CREATE TABLE UserGroupMembers (
   IdUser int,
   IdGroup int
)
CREATE TABLE Users (
   IdUser int,
   UserName nvarchar(50)
)
CREATE TABLE UserGroups (
   IdUserGroup int,
   Title nvarchar(100)
)
INSERT INTO UserGroupMembers(IdUser, IdGroup) VALUES (1, 8)
INSERT INTO Users(IdUser, UserName) VALUES (1, N'User1')
INSERT INTO UserGroups(IdUserGroup, Title) VALUES (8, N'Group1')

Statement:

SELECT TOP (1)
    a.IdUser , 
    b.UserName, 
    a.IdGroup, 
    c.Title 
FROM UserGroupMembers AS a 
INNER JOIN Users AS b on a.IdUser= b.IdUser 
INNER JOIN UserGroups AS c on a.IdGroup = c.IdUserGroup
FOR JSON PATH

Result:

[{"IdUser":1,"UserName":"User1","IdGroup":8,"Title":"Group1"}]

Note, that with FOR JSON PATH you may use dot-separated column names to generate JSON with nested objects.

SELECT TOP (1)
    a.IdUser , 
    b.UserName AS [b.UserName], 
    a.IdGroup, 
    c.Title AS [c.Title]
FROM UserGroupMembers AS a 
INNER JOIN Users AS b on a.IdUser= b.IdUser 
INNER JOIN UserGroups AS c on a.IdGroup = c.IdUserGroup
FOR JSON PATH

Result:

[{"IdUser":1,"b":{"UserName":"User1"},"IdGroup":8,"c":{"Title":"Group1"}}]