December 11, 2023 at 11:12 am
Hi All,
I need some assistance and not sure how to achieve the expected output.
I have a JSON input parameter which is a nvarchar(max) passed from my application as per my sample code below.
I've started to break up the JSON into a table output and am a bit stuck on getting how I expect it to be done.
-- Test Json Passed via the Application
DECLARE @JsonContent NVARCHAR(MAX) =
'[
{
"Id": "DateIdentified001",
"ColumnName": "DateIdentified",
"DataType": "3",
"ColumnWidth": 10,
"Formula": "NA",
"Value": "2023-11-01"
},
{
"Id": "Description001",
"ColumnName": "Description",
"DataType": "2",
"ColumnWidth": 20,
"Formula": "NA",
"Value": "Desc-Number-1"
},
{
"Id": "RectificationSteps001",
"ColumnName": "RectificationSteps",
"DataType": "2",
"ColumnWidth": 20,
"Formula": "NA",
"Value": "Rec-Number-1"
},
{
"Id": "ReviewDate001",
"ColumnName": "ReviewDate",
"DataType": "3",
"ColumnWidth": 10,
"Formula": "DATEADD(DAY,10,DateIdentified)",
"Value": ""
},
{
"Id": "DateIdentified002",
"ColumnName": "DateIdentified",
"DataType": "3",
"ColumnWidth": 10,
"Formula": "NA",
"Value": "2023-12-01"
},
{
"Id": "Description002",
"ColumnName": "Description",
"DataType": "2",
"ColumnWidth": 20,
"Formula": "NA",
"Value": "Desc-Number-2"
},
{
"Id": "RectificationSteps002",
"ColumnName": "RectificationSteps",
"DataType": "2",
"ColumnWidth": 20,
"Formula": "NA",
"Value": "Rec-Number-2"
},
{
"Id": "ReviewDate002",
"ColumnName": "ReviewDate",
"DataType": "3",
"ColumnWidth": 10,
"Formula": "DATEADD(DAY,10,DateIdentified)",
"Value": ""
}
]'
-- Query to break up the JSON
SELECT
ColumnName,
Formula,
[Value]
FROM OPENJSON(@JsonContent) WITH (
ColumnName NVARCHAR(255) '$.ColumnName',
Formula NVARCHAR(255) '$.Formula',
[Value] NVARCHAR(255) '$.Value'
)
My output is as per image "OriginalOutput.png" or simply the execution of running the sample code.
But the expected output is like the File "ExpectedOutput.png,
The Review Date would be the actual formulated value but I'm not worried at this stage cause I can apply that after I get the expected output which I think would be easy.
This is a very cut down version cause I've brough back 4 columns but it could be dynamic up to 10. the output would need to be built dynamically, But as above I can give that a go once I stop getting stuck on the same problem
I'm struggling to achieve this but would it be a PIVOT to get it done what I expect.
EDIT:
I'm open to making changes to the Application JSON output if needed, for example have a value like C1 -> C10 which is static for every column to Pivot if needed.
December 11, 2023 at 3:16 pm
This is not an easy format to achieve what you want, because the array items are not linked to each other.
But if you were to add a key to the initial JSON (corresponding with the Id column in your expected output) it will make things a lot easier.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 11, 2023 at 5:47 pm
100% agree with Phil. Fix the JSON please 🙂 To save myself from toil I used ChatGPT-4 and here's the result
You're right, embedding SQL code in JSON is not a good practice. It's better to keep data and logic separate.
Including the DataType and ColumnWidth in the JSON data is not necessary if the target is SQL Server. The database schema should already define these properties. Including them in the JSON could lead to confusion or errors if there's a discrepancy between the JSON and the database schema.
The JSON should ideally only include the data that needs to be inserted or updated in the database. Here's a revised version of the JSON without the DataType and ColumnWidth:
DECLARE @JsonContent NVARCHAR(MAX) = N'
{
"Records": [
{
"Id": "001",
"Attributes": [
{
"ColumnName": "DateIdentified",
"Value": "2023-11-01"
},
{
"ColumnName": "Description",
"Value": "Desc-Number-1"
},
{
"ColumnName": "RectificationSteps",
"Value": "Rec-Number-1"
},
{
"ColumnName": "ReviewDate",
"DaysToAdd": 10,
"Value": ""
}
]
},
{
"Id": "002",
"Attributes": [
{
"ColumnName": "DateIdentified",
"Value": "2023-12-01"
},
{
"ColumnName": "Description",
"Value": "Desc-Number-2"
},
{
"ColumnName": "RectificationSteps",
"Value": "Rec-Number-2"
},
{
"ColumnName": "ReviewDate",
"DaysToAdd": 10,
"Value": ""
}
]
}
]
}'
select isjson(@JsonContent);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 11, 2023 at 10:16 pm
Thanks Phil & Steve, that's making life a bit easier.
So based on your code Steve, I've now changed it to the below JSON
DECLARE @JsonContent NVARCHAR(MAX) =
N'{
"Records": [
{
"Id": "001",
"Attributes": [
{
"ColumnName": "DateIdentified",
"Value": "2023-11-01"
},
{
"ColumnName": "Description",
"Value": "Desc-Number-1"
},
{
"ColumnName": "RectificationSteps",
"Value": "Rec-Number-1"
},
{
"ColumnName": "ReviewDate",
"DaysToAdd": 10,
"Value": ""
}
]
},
{
"Id": "002",
"Attributes": [
{
"ColumnName": "DateIdentified",
"Value": "2023-12-01"
},
{
"ColumnName": "Description",
"Value": "Desc-Number-2"
},
{
"ColumnName": "RectificationSteps",
"Value": "Rec-Number-2"
},
{
"ColumnName": "ReviewDate",
"DaysToAdd": 10,
"Value": ""
}
]
}
]
}'
-- NEW CODE BASED ON NEW SAMPLE JSON
SELECT t1.ID, t2.ColumnName, t2.DaysToAdd, t2.[Value]
FROM OPENJSON (@JsonContent, '$.Records')
WITH (
Id NVARCHAR(255) '$.Id',
Attributes NVARCHAR(max) '$.Attributes' AS JSON
) t1
CROSS APPLY OPENJSON(t1.Attributes)
WITH (
ColumnName nvarchar(255) '$.ColumnName',
[DaysToAdd] nvarchar(255) '$.DaysToAdd',
[Value] nvarchar(255) '$.Value'
) t2
So based on my Output now which is cleaner, now would I look at doing a PIVOT to achieve the results? Or am I still on the wrong track with my JSON code which I've used.
December 11, 2023 at 11:02 pm
Ok, I think I'm closer, its just the final step of getting "DaysToAdd" on the same Line and making it a single entry.
DECLARE @JsonContent NVARCHAR(MAX) =
N'{
"Records": [
{
"Id": "001",
"Attributes": [
{
"ColumnName": "DateIdentified",
"Value": "2023-11-01"
},
{
"ColumnName": "Description",
"Value": "Desc-Number-1"
},
{
"ColumnName": "RectificationSteps",
"Value": "Rec-Number-1"
},
{
"ColumnName": "ReviewDate",
"DaysToAdd": 10,
"Value": ""
}
]
},
{
"Id": "002",
"Attributes": [
{
"ColumnName": "DateIdentified",
"Value": "2023-12-01"
},
{
"ColumnName": "Description",
"Value": "Desc-Number-2"
},
{
"ColumnName": "RectificationSteps",
"Value": "Rec-Number-2"
},
{
"ColumnName": "ReviewDate",
"DaysToAdd": 10,
"Value": ""
}
]
}
]
}'
SELECT *
FROM (
SELECT t1.ID, t2.ColumnName, t2.DaysToAdd, t2.[Value]
FROM OPENJSON (@JsonContent, '$.Records')
WITH (
Id NVARCHAR(255) '$.Id',
Attributes NVARCHAR(max) '$.Attributes' AS JSON
) t1
CROSS APPLY OPENJSON(t1.Attributes)
WITH (
ColumnName nvarchar(255) '$.ColumnName',
[DaysToAdd] nvarchar(255) '$.DaysToAdd',
[Value] nvarchar(255) '$.Value'
) t2
) result
PIVOT
(
MAX([Value])
FOR ColumnName IN (
[DateIdentified],
[Description],
[RectificationSteps],
[ReviewDate]
)
) AS PivotTable
Uploaded new output
December 12, 2023 at 12:03 am
This mostly borrows your FROM clause except I changed DaysToAdd to be 'int' which the JSON supports (it's not in quotations). Instead of PIVOT I always use conditional aggregation. It's just as efficient to run and it's a more flexible syntax imo
select t1.Id,
max(iif(t2.ColumnName=N'ReviewDate', t2.DaysToAdd, null)) DaysToAdd,
max(iif(t2.ColumnName=N'DateIdentified', t2.[Value], null)) DateIdentified,
max(iif(t2.ColumnName=N'Description', t2.[Value], null)) [Description],
max(iif(t2.ColumnName=N'RectificationSteps', t2.[Value], null)) RectificationSteps,
dateadd(day, max(iif(t2.ColumnName=N'ReviewDate', t2.DaysToAdd, null)),
max(iif(t2.ColumnName=N'DateIdentified', t2.[Value], null))) ReviewDate
from openjson(@JsonContent, '$.Records')
with(Id NVARCHAR(255) '$.Id',
Attributes NVARCHAR(max) '$.Attributes' AS JSON) t1
cross apply openjson(t1.Attributes)
with(ColumnName nvarchar(255) '$.ColumnName',
[DaysToAdd] int '$.DaysToAdd',
[Value] nvarchar(255) '$.Value') t2
group by t1.Id;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 12, 2023 at 1:50 am
This mostly borrows your FROM clause except I changed DaysToAdd to be 'int' which the JSON supports (it's not in quotations). Instead of PIVOT I always use conditional aggregation. It's just as efficient to run and it's a more flexible syntax imo
select t1.Id,
max(iif(t2.ColumnName=N'ReviewDate', t2.DaysToAdd, null)) DaysToAdd,
max(iif(t2.ColumnName=N'DateIdentified', t2.[Value], null)) DateIdentified,
max(iif(t2.ColumnName=N'Description', t2.[Value], null)) [Description],
max(iif(t2.ColumnName=N'RectificationSteps', t2.[Value], null)) RectificationSteps,
dateadd(day, max(iif(t2.ColumnName=N'ReviewDate', t2.DaysToAdd, null)),
max(iif(t2.ColumnName=N'DateIdentified', t2.[Value], null))) ReviewDate
from openjson(@JsonContent, '$.Records')
with(Id NVARCHAR(255) '$.Id',
Attributes NVARCHAR(max) '$.Attributes' AS JSON) t1
cross apply openjson(t1.Attributes)
with(ColumnName nvarchar(255) '$.ColumnName',
[DaysToAdd] int '$.DaysToAdd',
[Value] nvarchar(255) '$.Value') t2
group by t1.Id;
That is excellent! it's working as expected. Really appreciate your help especially the removal of PIVOT.
I just need to work out how to do this all dynamically, as the column Names and amount of columns can vary in the JSON.
December 12, 2023 at 10:27 am
I just need to work out how to do this all dynamically, as the column Names and amount of columns can vary in the JSON.
Then you will need to use dynamic SQL. That will be a challenge, to say the least.
Best of luck.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 12, 2023 at 4:20 pm
Tava wrote:I just need to work out how to do this all dynamically, as the column Names and amount of columns can vary in the JSON.
Then you will need to use dynamic SQL. That will be a challenge, to say the least.
Best of luck.
To help there a bit and especially since this boils down to the "Black Art" of "CrossTabs", the following old article may help a whole lot in the area of making things "dynamic".
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2023 at 10:40 pm
Thanks Phil & Jeff, definitely not as easy as I thought.
That link you shared Jeff is intense, I need to read and understand it more.
Is there anyway, you can think of by changing the JSON will make life easier?
for example, even though the amount of Columns and Column names are dynamic, We can cap the amount of Columns to 10.
So If I had something like Column1 = C1, Column2 = C2 .... Column10 = C10 and preset 10 Columns even if only 4 are used.
And then Add a Field Value for Column name under the attribute would that group easier
December 13, 2023 at 5:29 am
Hi All,
I need some assistance and not sure how to achieve the expected output.
I have a JSON input parameter which is a nvarchar(max) passed from my application as per my sample code below.
I've started to break up the JSON into a table output and am a bit stuck on getting how I expect it to be done.
-- Test Json Passed via the Application
DECLARE @JsonContent NVARCHAR(MAX) =
'[
{
"Id": "DateIdentified001",
"ColumnName": "DateIdentified",
"DataType": "3",
"ColumnWidth": 10,
"Formula": "NA",
"Value": "2023-11-01"
},
{
"Id": "Description001",
"ColumnName": "Description",
"DataType": "2",
"ColumnWidth": 20,
"Formula": "NA",
"Value": "Desc-Number-1"
},
{
"Id": "RectificationSteps001",
"ColumnName": "RectificationSteps",
"DataType": "2",
"ColumnWidth": 20,
"Formula": "NA",
"Value": "Rec-Number-1"
},
{
"Id": "ReviewDate001",
"ColumnName": "ReviewDate",
"DataType": "3",
"ColumnWidth": 10,
"Formula": "DATEADD(DAY,10,DateIdentified)",
"Value": ""
},
{
"Id": "DateIdentified002",
"ColumnName": "DateIdentified",
"DataType": "3",
"ColumnWidth": 10,
"Formula": "NA",
"Value": "2023-12-01"
},
{
"Id": "Description002",
"ColumnName": "Description",
"DataType": "2",
"ColumnWidth": 20,
"Formula": "NA",
"Value": "Desc-Number-2"
},
{
"Id": "RectificationSteps002",
"ColumnName": "RectificationSteps",
"DataType": "2",
"ColumnWidth": 20,
"Formula": "NA",
"Value": "Rec-Number-2"
},
{
"Id": "ReviewDate002",
"ColumnName": "ReviewDate",
"DataType": "3",
"ColumnWidth": 10,
"Formula": "DATEADD(DAY,10,DateIdentified)",
"Value": ""
}
]'
-- Query to break up the JSON
SELECT
ColumnName,
Formula,
[Value]
FROM OPENJSON(@JsonContent) WITH (
ColumnName NVARCHAR(255) '$.ColumnName',
Formula NVARCHAR(255) '$.Formula',
[Value] NVARCHAR(255) '$.Value'
)My output is as per image "OriginalOutput.png" or simply the execution of running the sample code.
But the expected output is like the File "ExpectedOutput.png,
The Review Date would be the actual formulated value but I'm not worried at this stage cause I can apply that after I get the expected output which I think would be easy.
This is a very cut down version cause I've brough back 4 columns but it could be dynamic up to 10. the output would need to be built dynamically, But as above I can give that a go once I stop getting stuck on the same problem
I'm struggling to achieve this but would it be a PIVOT to get it done what I expect.
EDIT:
I'm open to making changes to the Application JSON output if needed, for example have a value like C1 -> C10 which is static for every column to Pivot if needed.
Thanks Phil & Jeff, definitely not as easy as I thought.
That link you shared Jeff is intense, I need to read and understand it more.
Is there anyway, you can think of by changing the JSON will make life easier?
for example, even though the amount of Columns and Column names are dynamic, We can cap the amount of Columns to 10.
So If I had something like Column1 = C1, Column2 = C2 .... Column10 = C10 and preset 10 Columns even if only 4 are used.
And then Add a Field Value for Column name under the attribute would that group easier
Will the JSON data IN YOUR ORIGINAL POST ALWAYS have the same name/value pairs as below?
Id
ColumnName
DataType
ColumnWidth
Formula
Value
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2023 at 6:31 am
The JSON will always have the same values/pairs as that part is static.
That pairing is still not 100% as I'm taking suggestions provided by Phil/Steves to help improve the JSON and passing onto the .net dev I work with.
But its safe to say, that what we decide with, will not change.
December 13, 2023 at 1:49 pm
The JSON will always have the same values/pairs as that part is static.
That pairing is still not 100% as I'm taking suggestions provided by Phil/Steves to help improve the JSON and passing onto the .net dev I work with.
But its safe to say, that what we decide with, will not change.
If they're going to make a change, they should make a change to the value stored in the ID column. Instead of the column name and a 3 digit number, it should just be the ID number. Other than that, I'd leave the original design of the JSON alone because of the incredible flexibility it offers (although if they have all that, what on earth is preventing them from making a good ol' fashion TSV (Tab Separated Value) file that would take virtually no effort to import into a table).
Other than that and with the confirmation that you just gave that the key/value pairs for each root.key will stay the same, I should be able to finish the "pivot" for your first post of example data tonight.
Disclaimer... I'm not a big, smart, JSON user but I've been able to do a nice simple parse of it to put all the parts into an EAV (Entity, Attribute, Value table) that currently using the stripped out 3 digit number from the ID column (which is why it would be better as just a number), and the root.key to keep the column sets together and, like I said, I should be able to have the unpivot ready tonight.
And, yep... it'll all be dynamic, although (like I said above) it would be MUCH better if the source were a TSV instead of JSON.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2023 at 9:05 pm
If they're going to make a change, they should make a change to the value stored in the ID column. Instead of the column name and a 3 digit number, it should just be the ID number. Other than that, I'd leave the original design of the JSON alone because of the incredible flexibility it offers (although if they have all that, what on earth is preventing them from making a good ol' fashion TSV (Tab Separated Value) file that would take virtually no effort to import into a table).
Other than that and with the confirmation that you just gave that the key/value pairs for each root.key will stay the same, I should be able to finish the "pivot" for your first post of example data tonight.
Disclaimer... I'm not a big, smart, JSON user but I've been able to do a nice simple parse of it to put all the parts into an EAV (Entity, Attribute, Value table) that currently using the stripped out 3 digit number from the ID column (which is why it would be better as just a number), and the root.key to keep the column sets together and, like I said, I should be able to have the unpivot ready tonight.
And, yep... it'll all be dynamic, although (like I said above) it would be MUCH better if the source were a TSV instead of JSON.
Hi Jeff,
I can go back and make that suggestion, to use a TSV. If that will simplify the process. JSON was the suggestion used and i don't think it was anticipated how hard this would be.
Just confirming when you say paring/values will remain the same, I mean the Red highlighted text will always stay the same but the black text will vary depending on what is created.
{
"Id": "Description002",
"ColumnName": "Description",
"DataType": "2",
"ColumnWidth": 20,
"Formula": "NA",
"Value": "Desc-Number-2"
}
However, once its created it cannot be changed. (i.e. Column Name "Description" can not change, if it does need to then a new report would need to be created.
December 13, 2023 at 9:11 pm
The JSON will always have the same values/pairs as that part is static.
That pairing is still not 100% as I'm taking suggestions provided by Phil/Steves to help improve the JSON and passing onto the .net dev I work with.
But its safe to say, that what we decide with, will not change.
On this particular topic I disagree with Jeff and I think Phil/me have the right approach. It would be a big pita for the .NET dev to create the original JSON because the hierarchies in the data are not object modeled. Said another way: to create that JSON from nested CLR objects would require writing loops afaik and imo. You could pass them these POCO classes which serialize into the suggested JSON.
public class Rootobject
{
public Record[] Records { get; set; }
}
public class Record
{
public string Id { get; set; }
public Attribute[] Attributes { get; set; }
}
public class Attribute
{
public string ColumnName { get; set; }
public string Value { get; set; }
public int DaysToAdd { get; set; }
}
Then to serialize/deserialze
//using System.Text.Json;
// ...
// Deserialize JSON string to POCO
string jsonString = "{ \"Records\": [ { \"Id\": \"1\", \"Attributes\": [ { \"ColumnName\": \"Test\", \"Value\": \"Value\", \"DaysToAdd\": 5 } ] } ] }";
Rootobject rootObject = JsonSerializer.Deserialize<Rootobject>(jsonString);
// Serialize POCO to JSON string
string jsonString = JsonSerializer.Serialize(rootObject);
One of the big advantages of JSON as medium of exchange vs TSV (Tab Separated Value) or any other flat file is the ability to nest subarrays and to represent hierarchies. Another advantage is the handling of escaping/delimiting within strings is pretty much solved.
After a bunch of prompts ChatpGPT-4 produced the following:
The original JSON structure can be represented by the following POCO classes in C#:
public class Rootobject
{
public Item[] Items { get; set; }
}
public class Item
{
public string Id { get; set; }
public string ColumnName { get; set; }
public string DataType { get; set; }
public int ColumnWidth { get; set; }
public string Formula { get; set; }
public string Value { get; set; }
}However, this structure does not represent the evident hierarchies in the data. The JSON structure suggests that there are multiple records, each with multiple attributes. But the POCO structure above treats each attribute as a separate record. To properly represent the hierarchies, you would need a more complex object model, like the one you proposed in your previous response.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply