In the Part 1, We have gone through how to extract and manipulate JSON string in SQL. In this article, let us see how to format the query result as JSON.
FOR JSON
Used to export SQL table data in JSON format. It take column or alias as a key. It is similar to FOR XML in SQL Server.
AUTO– It will create nested JSON sub-array based on the table hierarchy used in the query. It is most commonly used.PATH– It defines the required JSON structure using the column name or alias. It is used to control how JSON data is generated or nested.
SELECT <COL_NAME> ..
FROM <TABLE>
FOR JSON AUTO| PATH
Let us create a table with some data to see how this FOR JSON function works.
CREATE TABLE [dbo].[Addresses](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NULL,
[Address] [varchar](250) NULL,
[City] [varchar](50) NULL,
[Country] [varchar](50) NULL,
CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED
(
[Id] ASC
))
CREATE TABLE [dbo].[Customer](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Code] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id] ASC
))
GO
SET IDENTITY_INSERT [dbo].[Addresses] ON
GO
INSERT [dbo].[Addresses] ([Id], [CustomerId], [Address], [City], [Country]) VALUES (1, 1, N'XXXX', N'Oxford', N'UK')
GO
INSERT [dbo].[Addresses] ([Id], [CustomerId], [Address], [City], [Country]) VALUES (2, 1, N'YYYY', N'London', N'UK')
GO
INSERT [dbo].[Addresses] ([Id], [CustomerId], [Address], [City], [Country]) VALUES (3, 2, N'ZZZZ', N'Birmingham', N'UK')
GO
INSERT [dbo].[Addresses] ([Id], [CustomerId], [Address], [City], [Country]) VALUES (4, 2, N'XYXY', N'Chennai', N'India')
GO
SET IDENTITY_INSERT [dbo].[Addresses] OFF
GO
SET IDENTITY_INSERT [dbo].[Customer] ON
GO
INSERT [dbo].[Customer] ([Id], [Code], [FirstName], [LastName]) VALUES (1, N'ABCD', N'Kumar', N'Thamilarasan')
GO
INSERT [dbo].[Customer] ([Id], [Code], [FirstName], [LastName]) VALUES (2, N'XYZ', N'Cholan', N'Kumaran')
GO
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT [FK_Addresses_Customer] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
GO
ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT [FK_Addresses_Customer]
GoLet use JSON AUTO to display data in JSON format.
SELECT * FROM [dbo].[Customer] c
INNER JOIN [dbo].[Addresses] Addresses ON c.Id = Addresses.CustomerId
WHERE c.Id = 1
FOR JSON AUTOIt will return result as below.
Let’s try with JSON PATH.
SELECT Id, Code, FirstName, LastName,
(SELECT Id, Address, City, Country
FROM [dbo].[Addresses] a
WHERE a.CustomerId = c.Id
FOR JSON AUTO
) as Addresses
FROM [dbo].[Customer] c
WHERE c.Id =1
FOR JSON PATH, ROOT ('Customer');
OPENJSON
A table value function will generate a relational table with its content from the JSON string.
Basically, it will iterate through the JSON object and arrays and generate a row for each element. We can define and generate a table with and without predefined schema.
Without Predefined Schema
DECLARE @JSONData AS NVARCHAR(4000);
SET @JSONData = N'{
"FirstName":"Kumar",
"LastName":"Thamilarasan",
"Code":"ABC123",
"Addresses":[
{ "Address":"XXXX", "City":"Oxford", "Country":"UK"},
{ "Address":"YYYY", "City":"London", "State":"UK"}
]
}';
SELECT * FROM OPENJSON(@JSONData);
With Pre-defined schema
DECLARE @JSONData AS NVARCHAR(4000);
SET @JSONData = N'{
"FirstName":"Kumar",
"LastName":"Thamilarasan",
"Code":"ABC123",
"Addresses":[
{ "Address":"XXXX", "City":"Oxford", "Country":"UK"},
{ "Address":"YYYY", "City":"London", "Country":"UK"}
]
}';
SELECT
FirstName, LastName, Address, City, Country
FROM OPENJSON(@JSONData)
WITH (FirstName VARCHAR(50),
LastName VARCHAR(50),
Code VARCHAR(50),
Addresses NVARCHAR(max) as json
) as B
cross apply openjson (B.Addresses)
with
(
Address VARCHAR(50),
City VARCHAR(50),
Country VARCHAR(50)
);
We can also access child JSON objects using OPENJSON by using CROSS APPLY function. If the column specified with AS JSON , then the type must be NVARCHAR(MAX). Without this option, it will return NULL or if we specify strict option, it will throw an error.
Hope this gives some idea on how to format the query result in JSON.
Happy SQL’ing 🙂
My coding exploration 