SQL Server: Making JSON relational

Working on a task to create a relational ordered set of prompts from rules with dynamic model binding.  This is one way to do it with JSON and SQL.  In this case the prompts may change, but it is a rare event.  The goal is to make the SQL server which has relational information in tables create and do the dynamic binding and make it do type checking.  How?

Start with Business table and then a related BusinessSteps table like so

DROP TABLE IF EXISTS [dbo].[BusinessSteps]
DROP TABLE IF EXISTS [dbo].[Business]

CREATE TABLE [dbo].[Business](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Business] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))
GO
CREATE TABLE [dbo].[BusinessSteps](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BusinessID] [int] NOT NULL,
	[TemplateText] [nvarchar](max) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))
GO

ALTER TABLE [dbo].[BusinessSteps]  WITH CHECK ADD  CONSTRAINT [FK_BusinessSteps_Business] FOREIGN KEY([BusinessID])
REFERENCES [dbo].[Business] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[BusinessSteps] CHECK CONSTRAINT [FK_BusinessSteps_Business]
GO

Load some initial data, in the JSON steps don't worry about the formatting yet.  It's to allow for dynamic formatting by letting the SQL engine take care of the binding and presentation

DECLARE @businessName NVARCHAR(200) = 'Jasmine''s Jewels'

MERGE INTO [dbo].[Business] AS [target]
USING 
(
	VALUES (@businessName)
) AS [source] ([Name])
ON [target].[Name] = [source].[Name]
WHEN NOT MATCHED BY TARGET THEN
	  INSERT ([Name])
	  VALUES ([Name]);


DECLARE @business_id INT;

SELECT @business_id = [Id]
FROM [dbo].[Business]
WHERE [Name] = @businessName;

MERGE INTO [dbo].[BusinessSteps] AS [target]
USING 
(

	VALUES
	(@business_id, '{
  "steps": [
		{
		  "order": 1,
		  "text": "Dial: '' + @phoneNumber + ''"
		},
		{
		  "order": 2,
		  "text": "Enter: '' + @firstName + '' '' + @lastName + ''"
		},
		{
		  "order": 3,
		  "text": "Enter Date of Birth: '' + FORMAT(@dateOfBirth, ''MM/dd/yyyy'') + ''"
		},
		{
		  "order": 4,
		  "text": "Enter the requested status: '' + @status + ''"
		}
	]
}')
) AS [source] ([BusinessID], [TemplateText])
ON [target].[BusinessID] = [source].[BusinessID]
WHEN NOT MATCHED BY TARGET THEN
	  INSERT ([BusinessID], [TemplateText])
	  VALUES ([BusinessID], [TemplateText]);

Now when you do a select you should get something like this

Each business entity has a corresponding row that contains steps within these steps are objects that are composed of the elements {order, text} the idea is that each step has a specific order and that step is then composed of some text that text is template text that can be dynamically executed to input parameters in a safe manner with dynamic sql and the use of SELECT to do the dynamic binding.  In this case the data, binding, and formatting all can occur in SQL.  Remember this is to keep modeling and display consistent so let it be SQL all the way up and down.

If you run the following query to find the BusinessSteps you entered above

DECLARE @businessName NVARCHAR(MAX) = 'Jasmine''s Jewels'
DECLARE @json NVARCHAR(MAX)

SELECT TOP 1 @json = [TemplateText]
FROM [dbo].[BusinessSteps] [bs]
INNER JOIN [dbo].[Business] [b]
ON [b].[Id] = [bs].[BusinessID]
WHERE [b].[Name] = @businessName

SELECT JSON_VALUE(value, '$.text')
FROM OPENJSON(@json, '$.steps')
ORDER BY CAST(JSON_VALUE(value, '$.order') AS INT)

It would give you the following

So what you want to do is have those SQL variables dynamically bind and run like so for the first statement like so

DECLARE @phoneNumber NVARCHAR(100) = '123-456-7890'
SELECT 'Dial: ' + @phoneNumber + ''

Results

How to do that dynamically for every step is to have the variables passed in and evaluated for each statement using sp_executesql

It's a bit of a mess so let's create the stored procedure to extract, bind, and return the intended data dynamically, so let me give you the stored procedure to do this

CREATE OR ALTER PROCEDURE [dbo].[Get_PromptSteps] 
(
	@businessName NVARCHAR(100),
	@phoneNumber NVARCHAR(20),
	@lastName NVARCHAR(100),
	@firstName NVARCHAR(100),
	@dateOfBirth DATETIME,
	@status NVARCHAR(100)
) AS
BEGIN

SET NOCOUNT ON

DECLARE @response NVARCHAR(MAX)
DECLARE @json NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX) = '
@phoneNumber NVARCHAR(20),
@lastName NVARCHAR(100),
@firstName NVARCHAR(100),
@dateOfBirth DATETIME,
@status NVARCHAR(100)
'

SELECT TOP 1 @json = [TemplateText]
FROM [dbo].[BusinessSteps] [bs]
INNER JOIN [dbo].[Business] [b]
ON [b].[Id] = [bs].[BusinessID]
WHERE [b].[Name] = @businessName

DECLARE @totalSteps INT
SELECT @totalSteps = COUNT(JSON_VALUE(value, '$.order'))
FROM OPENJSON(@json, '$.steps')

DECLARE @currentStep INT = 1
DECLARE @tab AS TABLE (response NVARCHAR(MAX)) 

WHILE @currentStep <= @totalSteps
BEGIN

	DECLARE @stmt NVARCHAR(MAX);	
	SELECT TOP 1 @stmt = JSON_VALUE(value, '$.text')
	FROM OPENJSON(@json, '$.steps')
	WHERE CAST(JSON_VALUE(value, '$.order') AS INT) = @currentStep

	IF CHARINDEX('@', @stmt) < 0
	BEGIN
		SET @stmt = 'SELECT ' + @stmt
	END
	ELSE
	BEGIN
		SET @stmt = 'SELECT ''' + @stmt + ''''
	END

	PRINT @stmt

	INSERT INTO @tab
	EXECUTE @response = sp_executesql @stmt,
		@params,
		@phoneNumber = @phoneNumber,
		@lastName = @lastName,
		@firstName = @firstName,
		@dateOfBirth = @dateOfBirth,
		@status = @status

	SET @currentStep = @currentStep + 1
END

SELECT [response] FROM @tab

END

Now you can simply run the stored procedure as so

DECLARE @RC int
DECLARE @businessName NVARCHAR(100) = 'Jasmine''s Jewels'
DECLARE @phoneNumber nvarchar(100) = '123-456-7890'
DECLARE @lastName nvarchar(100) = 'Zero'
DECLARE @firstName nvarchar(100) = 'Kandy'
DECLARE @dateOfBirth datetime = '1999-01-06'
DECLARE @status nvarchar(100) = 'Ok for now'

EXECUTE @RC = [dbo].[Get_PromptSteps]
   @businessName
  ,@phoneNumber
  ,@lastName
  ,@firstName
  ,@dateOfBirth
  ,@status
GO

Gives you the output of

Then to call it from C# using Dapper like so

var sqlConnection = new SqlConnectionStringBuilder
{
	DataSource = "localhost",
	InitialCatalog = "DbCache",
	IntegratedSecurity = true
};

var businessName = "Jasmine's Jewels";
var phoneNumber = "123-456-7890";
var lastName = "Zero";
var firstName = "Kandy";
var status = "Ok for now";
var dateOfBirth = new DateTime(1999, 1, 6);

using var conn = new SqlConnection(sqlConnection.ConnectionString);
var steps = (await conn.QueryAsync<string>("Get_PromptSteps", new
{
	businessName,
	phoneNumber,
	lastName,
	firstName,
	dateOfBirth,
	status
}, null, null, CommandType.StoredProcedure)).ToList();

Now to make a change to an step you will need to access the step and modify it's template, say I want to reverse the name step from firstName lastName to lastName, firstName.  I would do the following.  I already know this step is 2 so that simplifies my query

I need to use a JSON path query and the JSON_MODIFY to change the output as needed.

DECLARE @businessName NVARCHAR(MAX) = 'Jasmine''s Jewels'
DECLARE @json NVARCHAR(MAX)

SELECT TOP 1 @json = [TemplateText]
FROM [dbo].[BusinessSteps] [bs]
INNER JOIN [dbo].[Business] [b]
ON [b].[Id] = [bs].[BusinessID]
WHERE [b].[Name] = @businessName

DECLARE @stepOrder INT = 2

SELECT JSON_VALUE(value, '$.text')
FROM OPENJSON(@json, '$.steps')
WHERE CAST(JSON_VALUE(value, '$.order') AS INT) = @stepOrder


UPDATE [BusinessSteps]
SET [TemplateText] = JSON_MODIFY(@json, '$.steps[' + CAST((@stepOrder - 1) as NVARCHAR(100)) +'].text', 'Enter: '' + @lastName + '','' + @firstName + ''')
WHERE [BusinessID] = 
(
	SELECT [Id]
	FROM [Business]
	WHERE [Name] = @businessName
)

SELECT TOP 1 @json = [TemplateText]
FROM [dbo].[BusinessSteps] [bs]
INNER JOIN [dbo].[Business] [b]
ON [b].[Id] = [bs].[BusinessID]
WHERE [b].[Name] = @businessName

SELECT JSON_VALUE(value, '$.text')
FROM OPENJSON(@json, '$.steps')
WHERE CAST(JSON_VALUE(value, '$.order') AS INT) = @stepOrder

First half of the query gives current value for the step

And second half shows the updated step

Now run the stored procedure again with above parameters

DECLARE @RC int
DECLARE @businessName NVARCHAR(100) = 'Jasmine''s Jewels'
DECLARE @phoneNumber nvarchar(100) = '123-456-7890'
DECLARE @lastName nvarchar(100) = 'Zero'
DECLARE @firstName nvarchar(100) = 'Kandy'
DECLARE @dateOfBirth datetime = '1999-01-06'
DECLARE @status nvarchar(100) = 'Ok for now'

EXECUTE @RC = [dbo].[Get_PromptSteps]
   @businessName
  ,@phoneNumber
  ,@lastName
  ,@firstName
  ,@dateOfBirth
  ,@status
GO

And you get

Successful modification of the step