Denham Coder

From SQL Server 2016, Microsoft provided support for storing and manipulating JSON within a SQL Server database. The aim of this blog is not to teach you how to use JSON but rather a use case for when JSON is a better alternative.

For this second example I am going to show you how I implemented a Parameter table that stored JSON for multiple instances of non-scalar values. For example; we need to store a list of servers and databases used by a reporting application. For each server and database pair, the application will carry out its reporting function.

An Example without using JSON

I have a table called Parameter defined as follows.

CREATE TABLE [Parameter]
(
   [Name]     [NVARCHAR](128)   NOT NULL,
   [Param]    [NVARCHAR](128)   NOT NULL,
   [Instance] [TINYINT]         NOT NULL,
   [Value]    [NVARCHAR](MAX)   NOT NULL,
   CONSTRAINT [Parameter_U1] UNIQUE NONCLUSTERED ([Name] ASC, [Param] ASC, [Instance] ASC)
);

I will insert some test data for demonstration purposes.

INSERT [Parameter]
VALUES
('Demo', 'ReportServer',   1, 'ServerA'),
('Demo', 'ReportServerDB', 1, 'DatabaseA'),
('Demo', 'ReportServer',   2, 'ServerB'),
('Demo', 'ReportServerDB', 2, 'DatabaseB'),
('Demo', 'ReportServer',   3, 'ServerC'),
('Demo', 'ReportServerDB', 3, 'DatabaseC');

SELECT   *
FROM     Parameter
WHERE    Name = 'Demo'
ORDER BY Instance, Param;

We entered three servers and the corresponding databases. Each pair is bound by the same Instance value. Notice for each identical Instance value, the Param is unique. The constraint will prevent invalid entries.

In order to query the table and return a result set, we have to get a bit clever and PIVOT the table. We will create a view in order to remove the complexity of querying the table from the application.

CREATE VIEW [vData]
AS
   SELECT ReportServer AS [Server], ReportServerDB AS [Database]
   FROM   Parameter 
   PIVOT (MAX(Value) FOR Param IN(ReportServer, ReportServerDB)) AS P;

After selecting from the view we get our desired results.

SELECT * FROM vData

This gets us the desired result and follows data normalization rules. Whilst it works I don’t really like this solution and for small datasets, JSON will be more appropriate.

An Example using JSON

Our definition for our Parameter table has just two columns as follows.

CREATE TABLE [Parameter]
(
   [Name]     [NVARCHAR](128)   NOT NULL,
   [Value]    [NVARCHAR](MAX)   NOT NULL,
   CONSTRAINT [Parameter_U1] UNIQUE NONCLUSTERED ([Name] ASC)
);

I will insert the same three servers and the corresponding databases I entered above.

INSERT [Parameter]
VALUES('Demo',
'{"Servers":[
{"Server":"ServerA","Database":"DatabaseA"},
{"Server":"ServerB","Database":"DatabaseB"},
{"Server":"ServerC","Database":"DatabaseC"}
]}'
);

SELECT *, CASE ISJSON([Value]) WHEN 0 THEN 'INVALID JSON' ELSE 'OK' END AS [Check_JSON]
FROM   Parameter
WHERE  Name = 'Demo';

I even added a check to ensure the JSON is value when querying the table.

Next we will create a view in order to remove the complexity of querying the table from the application.

CREATE VIEW [vData]
AS
   -- Needs DB Compat level 130+
   WITH CTE AS
   (
      SELECT JSON_QUERY(Value, '$.Servers') AS [Servers]
      FROM   [Parameter] AS P
      WHERE  Name = 'Demo'
   )
   SELECT      Server, [Database]
   FROM        CTE
   CROSS APPLY OPENJSON(CTE.Servers) WITH (Server NVARCHAR(128) '$.Server', [Database] NVARCHAR(128) '$.Database');

After selecting from the view we get our desired results.

SELECT * FROM vData

I hope you found this useful. Look out for more blogs on the use of JSON inside SQL Server.