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