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"}] }]

Zhorov · Accepted Answer · 2019-12-18 08:15:13Z

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"}}]

Serg · Accepted Answer · 2019-12-16 10:49:43Z

1

The simplest way

select *
from (
    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
) t
for json auto

answered Dec 16, 2019 at 10:49

Serg

22.9k5 gold badges23 silver badges50 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related