December 13, 2023 at 9:38 pm
Hey Steve,
You are correct, the .NET developer is having to loop through on the original JSON like you mentioned.
I guess with the new structure, you would simplify his work significantly.
December 14, 2023 at 5:04 am
On this particular topic I disagree with Jeff and I think Phil/me have the right approach.
Understand that I was speaking only from the SQL Server side... there is no "looping" required to pivot the JSON into a table.
While were on the subject, I wouldn't create JSON on the programming side. I'd have it make a TSV which is super easy to parse, even as an import file in SQL Server 2017 and above.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2023 at 5:35 am
While were on the subject, I wouldn't create JSON on the programming side. I'd have it make a TSV which is super easy to parse, even as an import file in SQL Server 2017 and above.
In terms of TSV, Would it work in my situation,
Basically I've broken down what the application is trying to do below. I've also added in a image of the test app created.
ADMIN PORTAL
* pre-requisite in previous page which I haven't captured:
Process for validating the register:
This is for a test validation on the Admin portal which is only ever 1 entry.
USERS PORTAL
For the user what will happen is that they will do the exact same steps but have the ability to enter in multiple entries... they could have like 1 line 5,10,20 Lines etc
So you will see something like
01/12/2023 -> Desc123 -> Rec123 -> ''"
03/12/2023 -> Description test -> RecTest1 -> ''"
10/12/2023 -> FixIssue-> Fixedbug-> ''"
The user will then save the work which will trigger the same validation (except for how many lines entered)
I will received the JSON, I will break it up and return to the APP the below
01/12/2023 -> Desc123 -> Rec123 -> 11/12/2023
03/12/2023 -> Description test -> RecTest1 -> 13/12/2023
10/12/2023 -> FixIssue-> Fixedbug-> 20/12/2023
Hope this helps explain it a bit further on what I'm trying to achieve
December 14, 2023 at 5:46 am
Actually, Just thinking out loud! Wouldn't a user defined table type (UDT) work in this scenario
It would also eliminate needing the complexity of the JSON be parsed through and broken up and can be returned to the app with the same expected format?
Edit: If the above works, I guess I can still store the data as a JSON for the app to reference in the DB when they need to read the data, but just the calculation etc could be UDT
Curious to see if this would work at all and your thoughts.
December 14, 2023 at 6:43 am
Actually, Just thinking out loud! Wouldn't a user defined table type (UDT) work in this scenario
It would also eliminate needing the complexity of the JSON be parsed through and broken up and can be returned to the app with the same expected format?
Edit: If the above works, I guess I can still store the data as a JSON for the app to reference in the DB when they need to read the data, but just the calculation etc could be UDT
Curious to see if this would work at all and your thoughts.
TBH, I don't see why the people making the JSON can't make it into a table and populate the table directly. They even provide a datatype key and they could actually execute the "Formula" to provide an actual date. I see zero need for any JSON here.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2023 at 10:53 am
TBH, I don't see why the people making the JSON can't make it into a table and populate the table directly. They even provide a datatype key and they could actually execute the "Formula" to provide an actual date. I see zero need for any JSON here.
I can request that to happen, if it achieves the end result and can simplify the process. Its something we can work towards.
If I go down this path, I'll need to change one of the table designs which is fine. Since all the data was handled in the JSONContent column and that is dynamic.
Below was the original design I had.
-- Original Design
CREATE TABLE [dbo].[AdminRegister](
[ID] [int] IDENTITY(1,1) NOT NULL,
[JSONContent] [nvarchar](4000) NOT NULL,
[Name] [varchar](500) NOT NULL,
[NoOfColumns] [int] NOT NULL,
[HeaderText] [varchar](250) NULL,
[FooterText] [varchar](250) NULL,
[IsPublished] [bit] NOT NULL,
[IsArchived] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[UpdatedDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_AdminRegister] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Since it can vary from minimum 4 columns up to maximum 10 and different columns names all the time, Any suggestions on best practice for design?
Do you think having a Header/Detail relationship would be suited. Something like
RegisterHeader
RegisterDetails
This would then allow cross apply / pivot etc taking place. The value column in registerdetail would be stored as a varchar(x) but in fact could really be INT/DATE etc
Any ideas would be grateful
December 14, 2023 at 1:25 pm
Understand that I was speaking only from the SQL Server side... there is no "looping" required to pivot the JSON into a table.
While were on the subject, I wouldn't create JSON on the programming side. I'd have it make a TSV which is super easy to parse, even as an import file in SQL Server 2017 and above.
Understood. For my part I'm speaking from the combined SQL Server+.NET including data access "side" or perspective. When it comes to SQL (and in general) we're on the same side :). From my perspective I'm advocating for the simplest and most reliable combined SQL Server+.NET approach. As has been confirmed there is looping involved in creating the (original, malformed) JSON in .NET. "... a TSV which is super easy to parse" Why parse at all? Writing parsers is the IT equivalent of cleaning the latrine. I would happily learn Javascript (which is never going to happen) before I even look at Antlr. With JSON you can just simply open it and use it! JSON provides the ability to pass objects of unlimited complexity (in terms of nested structure and hierarchies) to SQL Server without complex procedural programming in .NET. JSON is just a string and sending a string to SQL Server is simple and easy
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 14, 2023 at 1:43 pm
TBH, I don't see why the people making the JSON can't make it into a table and populate the table directly. They even provide a datatype key and they could actually execute the "Formula" to provide an actual date. I see zero need for any JSON here.
Imo parsing a SQL Server function from a text file, adding it dynamically to the SELECT list of a query, and executing the query is aka a SQL injection attack. Suppose there's a nefarious "man in the middle" who modifies the text file by changing "DATEADD(day, 10, column_name)" to "DATEADD(day, 10, column_name), *". Now the query will likely fail due to a runtime error. SQL Server code doesn't belong mixed into data files imo
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 14, 2023 at 2:03 pm
I can request that to happen, if it achieves the end result and can simplify the process. Its something we can work towards.
If I go down this path, I'll need to change one of the table designs which is fine. Since all the data was handled in the JSONContent column and that is dynamic.
Below was the original design I had.
-- Original Design
CREATE TABLE [dbo].[AdminRegister](
[ID] [int] IDENTITY(1,1) NOT NULL,
[JSONContent] [nvarchar](4000) NOT NULL,
[Name] [varchar](500) NOT NULL,
[NoOfColumns] [int] NOT NULL,
[HeaderText] [varchar](250) NULL,
[FooterText] [varchar](250) NULL,
[IsPublished] [bit] NOT NULL,
[IsArchived] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[UpdatedDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_AdminRegister] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GOSince it can vary from minimum 4 columns up to maximum 10 and different columns names all the time, Any suggestions on best practice for design?
Do you think having a Header/Detail relationship would be suited. Something like
RegisterHeader
- ID int identity(1,1) not null
- Name varchar(x) not null
- Header varchar(x) not null
- Footer varchar(x) not null
- IsArchived bit not null
- IsPublished bit not null
- CreatedDatetime datetime not null
RegisterDetails
- ID int identity(1,1) not null
- RegisterHeaderID int
- ColumnName varchar(x) not null
- Value varchar(x) not null
- Formula varchar(x) null
This would then allow cross apply / pivot etc taking place. The value column in registerdetail would be stored as a varchar(x) but in fact could really be INT/DATE etc
Any ideas would be grateful
This could be improved with some refinements imo. It would be helpful to know two additional items. First, in terms of the request pipeline is the data being provided to .NET already in JSON format? Maybe the application sends JSON to a .NET API which then makes a SQL Server procedure call? Second, how (briefly) does your application handle managing user identity?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 14, 2023 at 9:15 pm
This could be improved with some refinements imo. It would be helpful to know two additional items. First, in terms of the request pipeline is the data being provided to .NET already in JSON format? Maybe the application sends JSON to a .NET API which then makes a SQL Server procedure call? Second, how (briefly) does your application handle managing user identity?
If I understand your question and I'll try my best to.
When it comes to the other portal which is user based, there will be a UserId field which will have an entry belonging to them.
Hopefully that is correct answer to your question
December 21, 2023 at 1:44 am
Jeff/Scott,
Sorry been away for the last week & haven't had time to update you on what I've done.
This is the approach I've gone down, hoping it will resolve the issues faced. I've provided the code, but one thing I'm not able to achieve is the getting the result on 1 row.
Previously Scott used the MAX(IIF) but I'm not able to get that working. Any assistance here would be appreciated.
FYI, i've also gone down the PIVOT Way as I was able to achieve it dynamically but other method I can't.
-- dropping table
drop table if exists [dbo].[AdminRegisterHeader]
drop table if exists [dbo].[AdminRegisterDetail]
-- create tables
CREATE TABLE [dbo].[AdminRegisterHeader](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](500) NOT NULL,
[NoOfColumns] [int] NOT NULL,
[HeaderText] [varchar](250) NULL,
[FooterText] [varchar](250) NULL,
[IsPublished] [bit] NOT NULL,
[IsArchived] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[UpdatedDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_AdminRegisterHeader] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AdminRegisterDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RegisterHeaderID] [int] NOT NULL,
[ColumnName] [varchar](250) NULL,
[ColumnValue] [varchar](250) NULL,
[Formula] [varchar](250) NULL
CONSTRAINT [PK_AdminRegisterDetail] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- insert test data
insert into [dbo].[AdminRegisterHeader] (
[Name],
[NoOfColumns],
[HeaderText],
[FooterText],
[IsPublished],
[IsArchived],
[CreatedDateTime],
[UpdatedDateTime]
)
values(
'TestRegister',
4,
'TestHeader',
'TestFooter',
0,
0,
GETDATE(),
GETDATE()
)
insert into [dbo].[AdminRegisterDetail] (
[RegisterHeaderID],
[ColumnName],
[ColumnValue],
[Formula]
)
values
(1,'DateIssued','2023-12-01',NULL),
(1,'Description','TestDescription',NULL),
(1,'Rectification','TestRectification',NULL),
(1,'DateReviewed','','DATEADD(DAY,10,DateIssued')
insert into [dbo].[AdminRegisterHeader] (
[Name],
[NoOfColumns],
[HeaderText],
[FooterText],
[IsPublished],
[IsArchived],
[CreatedDateTime],
[UpdatedDateTime]
)
values(
'IssuesReg',
3,
'HeaderText',
'FooterText',
0,
0,
GETDATE(),
GETDATE()
)
insert into [dbo].[AdminRegisterDetail] (
[RegisterHeaderID],
[ColumnName],
[ColumnValue],
[Formula]
)
values
(2,'IssueDate','2023-12-03',NULL),
(2,'Owner','JohnSmith',NULL),
(2,'IssueDue','','DATEADD(DAY,5,IssueDate')
-- dynamic code for returning the results can return a specific HeaderID which will cover unkown column count.
-- local variables
declare
@columns nvarchar(max) ,
@sql nvarchar(max),
@registeredHeaderID int = 1 -- can specify 1 or 2
-- Get the list of unique values for the pivot column
SELECT @columns = COALESCE(@columns + ', ', '') + QUOTENAME(ColumnName) FROM (SELECT DISTINCT ColumnName FROM [AdminRegisterDetail] Where RegisterHeaderID = @registeredHeaderID) x;
drop table if exists #results
create table #results (
[RegisterHeaderID] [int],
[ColumnName] [varchar](250),
[ColumnValue] [varchar](250),
[Formula] [varchar](250)
)
insert into #results (
[RegisterHeaderID],
[ColumnName],
[ColumnValue] ,
[Formula]
)
select
[RegisterHeaderID] ,
[ColumnName] ,
[ColumnValue] ,
[Formula]
from
[AdminRegisterDetail]
Where
RegisterHeaderID = @registeredHeaderID
-- Construct the dynamic pivot query
SET @sql = '
SELECT *
FROM #results
PIVOT (
Max(ColumnValue)
FOR ColumnName IN (' + @columns + ')
) p';
-- Execute the dynamic pivot query
EXEC sp_executesql @sql;
December 22, 2023 at 11:57 pm
I actually just realised PIVOT isn't working when Multiple rows are present Putting a fake aggregation such as MAX isn't working as expected.
I'm stumped 🙁
Any ideas, to point me in the right direction.
We've been through a couple of changes with you, now. Would you post what the JSON looks like now and what the output needs to look like? And please describe what "isn't working" actually means.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2023 at 1:42 am
Sure Jeff & thank you,
Right now I'm trying a POC to avoid the JSON altogher at this stage.
I'm going with the approach that the app will place the data directly in the table.
As you can see from my code, ive built a parent & child table which allows it to not worry about how many columns are added (i.e min 1 to max 10 columns).
My test code inserts 1 row in 1st sample and then 2 rows in the second. Rows entered in these tables could be anything but as long as 2 can be returned, i assume the N amount of rows can be returned.
The thing not working is exactly what you mentioned, was the max aggregate in the pivot which will only return 1 row.
Also the fact everything will be dynamic adds more complexity.
I'm unsure if my DB design in this case isn't suitable. I thought it would cater for it or the design is right but code I'm writing is incorrect for it.
I can design it where by I can create all columns etc but I'm really trying to avoid that.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply