title: Unexpected result with JSON auto
body: <p>I've below 3 tables and sample data:</p>
  <p>Employee, Address, States</p>
  <pre><code>/****** 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
  </code></pre>
  <p>When I run below query, I get the JSON details in one JSON Object</p>
  <pre><code>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
  </code></pre>
  <p>Result in AddressDetails column:</p>
  <pre><code>[
      {
       &quot;Address&quot;: &quot;4912 La Vuelta&quot;,
       &quot;City&quot;: &quot;Bothell&quot;
      },
      {
          &quot;Address&quot;: &quot;4039 Elkwood Dr.&quot;,
          &quot;City&quot;: &quot;Ballard&quot;
      }
  ]
  </code></pre>
  <p>But if you uncomment the commented lines in above SQL query to include column from States table, I get the result as below:</p>
  <p><a href="https://i.stack.imgur.com/UcqLC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UcqLC.png" alt="enter image description here" /></a></p>
  <p>Why the <code>StateName</code> details going into array?</p>
  <p>Can I have that outside along with Address and city details by tweaking the query?</p>