How to deal with JSON in SQL Server? – Part 2

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]

Go

Let 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 AUTO

It 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 🙂