I'm attempting to fetch multiple rows using the SQL Server 2017 TSQL code from a JSON object.
DECLARE @json NVARCHAR(MAX);
SET @json = N'{
"DateCreated": "2020-08-02T16:04:59.3558001+01:00",
"Name": "Bolts",
"Price": 123.45,
"Length": 15.7,
"Quantity": 1,
"Attributes": [
{
"AttrHeading": "Material",
"AttrVal": "Steel"
},
{
"AttrHeading": "Colour",
"AttrVal": "Black"
},
{
"AttrHeading": "Finish",
"AttrVal": "Matt"
}
]
}';
SELECT j.*
FROM OPENJSON (@json, N'$')
WITH (
DateCreated datetime2 N'$.DateCreated',
[Name] varchar(100) N'$.Name',
Price decimal(19,4) N'$.Price',
[Length] decimal(19,4) N'$.Length',
Quantity integer N'$.Quantity',
AttrHeading varchar(max) N'$.Attributes.AttrHeading',
AttrVal varchar(max) N'$.Attributes.AttrVal'
) AS j;
I am hoping for three distinct rows to be returned, each one representing an attribute. This means that the other details like Name, Price, Length will remain the same for each row, only changing for AttrHeading and AttrValue.
As of now, AttrHeading and AttrVal are showing as null - is there any way to resolve this?