Unexpected result with JSON auto-CodePudding

I've below 3 tables and sample data:

Employee, Address, States

/****** Object:  Table [AzureSearch].[Address]    Script Date: 11/2/2021 2:16:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [AzureSearch].[Address](
    [EmployeeId] [int] NULL,
    [Address] [nvarchar](60) NULL,
    [City] [nvarchar](30) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [AzureSearch].[Employee]    Script Date: 11/2/2021 2:16:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [AzureSearch].[Employee](
    [EmployeeId] [int] NOT NULL,
    [FirstName] [nvarchar](200) NOT NULL,
    [LastName] [nvarchar](200) NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [AzureSearch].[States]    Script Date: 11/2/2021 2:16:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [AzureSearch].[States](
    [City] [varchar](50) NULL,
    [StateName] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (1, N'4350 Minute Dr.', N'Newport Hills')
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (2, N'7559 Worth Ct.', N'Renton')
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (3, N'2137 Birchwood Dr', N'Redmond')
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (4, N'5678 Lakeview Blvd.', N'Minneapolis')
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (5, N'9435 Breck Court', N'Bellevue')
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (6, N'5670 Bel Air Dr.', N'Renton')
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (7, N'1873 Lion Circle', N'Bothell')
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (7, N'3858 Vista Diablo', N'Issaquah')
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (8, N'4912 La Vuelta', N'Bothell')
GO
INSERT [AzureSearch].[Address] ([EmployeeId], [Address], [City]) VALUES (8, N'4039 Elkwood Dr.', N'Ballard')
GO
INSERT [AzureSearch].[Employee] ([EmployeeId], [FirstName], [LastName]) VALUES (1, N'Ken', N'Sánchez')
GO
INSERT [AzureSearch].[Employee] ([EmployeeId], [FirstName], [LastName]) VALUES (2, N'Terri', N'Duffy')
GO
INSERT [AzureSearch].[Employee] ([EmployeeId], [FirstName], [LastName]) VALUES (3, N'Roberto', N'Tamburello')
GO
INSERT [AzureSearch].[Employee] ([EmployeeId], [FirstName], [LastName]) VALUES (4, N'Rob', N'Walters')
GO
INSERT [AzureSearch].[Employee] ([EmployeeId], [FirstName], [LastName]) VALUES (5, N'Gail', N'Erickson')
GO
INSERT [AzureSearch].[Employee] ([EmployeeId], [FirstName], [LastName]) VALUES (6, N'Jossef', N'Goldberg')
GO
INSERT [AzureSearch].[Employee] ([EmployeeId], [FirstName], [LastName]) VALUES (7, N'Osarumwense', N'Agbonile')
GO
INSERT [AzureSearch].[Employee] ([EmployeeId], [FirstName], [LastName]) VALUES (8, N'Karl', N'Xie')
GO
INSERT [AzureSearch].[States] ([City], [StateName]) VALUES (N'Bothell', N'Washington')
GO
INSERT [AzureSearch].[States] ([City], [StateName]) VALUES (N'Ballard', N'Utah')
GO

When I run below query, I get the JSON details in one JSON Object

SELECT e.EmployeeId
        ,(
            SELECT a.Address
                ,a.City
            --  ,s.StateName AS StateName
            FROM AzureSearch.[Address] AS a
            --  ,AzureSearch.States AS s
            WHERE a.EmployeeId = e.employeeId
            --  AND a.City = s.City
            FOR json auto
            ) AS AddressDetails
FROM azuresearch.Employee AS e
 WHERE e.EmployeeId = 8

Result in AddressDetails column:

[
    {
     "Address": "4912 La Vuelta",
     "City": "Bothell"
    },
    {
        "Address": "4039 Elkwood Dr.",
        "City": "Ballard"
    }
]

But if you uncomment the commented lines in above SQL query to include column from States table, I get the result as below:

Why the StateName details going into array?

Can I have that outside along with Address and city details by tweaking the query?

CodePudding user response：

FOR JSON AUTO formats the output of the FOR JSON clause automatically based on the structure of the SELECT statement. The documentation explains: When you specify the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables. You can't change this format.

The statement joins tables (although it uses the old style JOIN syntax), so the columns in the States table are generated as properties of a nested JSON array using the table alias as the name of this nested array.

In your case a better option is FOR JSON PATH:

SELECT 
   e.EmployeeId,
   (
        SELECT 
            a.Address
           ,a.City
           ,s.StateName
        FROM 
            [Address] AS a
           ,[States] AS s
        WHERE 
            a.EmployeeId = e.employeeId
            AND a.City = s.City
        FOR JSON PATH
   ) AS AddressDetails
FROM [Employee] AS e
WHERE e.EmployeeId = 8

Result:

EmployeeId AddressDetails
8          [
           {"Address":"4912 La Vuelta","City":"Bothell","StateName":"Washington"},
           {"Address":"4039 Elkwood Dr.","City":"Ballard","StateName":"Utah"}
           ]