September 30, 2020 at 2:50 pm
Cross post of Stack Overflow as well, for future readers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 30, 2020 at 2:54 pm
I can't keep on giving answers on moving goal posts. I have shown how you would do this, I will leave it to you to implement.
JSON_VALUE(v.value, '$.fields.System.State.newValue')will not work though, as there is that is not referencefields."System.State".newValue("added for clarity), it's trying to reference the elementStatein the elementSystem; neither of which exist.
How come JSON_VALUE(v.value, '$.fields.MainValue.newValue') returns a value?
September 30, 2020 at 3:05 pm
How come JSON_VALUE(v.value, '$.fields.MainValue.newValue') returns a value?
Because that exists in yoru data.... There is no element called "System" not "State", you have one called "System.State" and are trying to reference it as separate elements, as I have explain to you... If we use brackets ([]) to demonstrate instead, "System.State" does not mean "[System.State]"... It would mean "[System].[State]", so "$.fields.System.State.newValue" would mean "$.[fields].[System].[State].[newValue]" not "$.[fields].[System.State].[newValue]".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
The solution for anyone, who might run into the same issue is to place double quotes on the element with the dot, so it works perfect for me now, thank for a help from here https://stackoverflow.com/questions/64140092/t-sql-how-to-process-json-elements-with-dot-in-their-names
DECLARE @v_Json NVARCHAR(MAX) = N'{
"count": 56,
"value": [{
"id": 1,
"workItemId": 1234,
"fields": {
"MainValue": {"newValue": 98765},
"System.UserName": "User Name 1",
"System.Id": {
"newValue": 1234
},
"System.State": {
"newValue": "New"
}
}
},
{
"id": 2,
"workItemId": 1234,
"fields": {
"MainValue": {"newValue": 123456, "oldValue": 98765},
"System.UserName": "User Name 2",
"System.State": {
"oldValue": "new",
"newValue": "Defined"
}
}
}
]
}';
SELECTJSON_VALUE(v.value, '$.id') AS id,
JSON_VALUE(v.value, '$.workItemId') AS workItemID,
JSON_VALUE(v.value, '$.fields.MainValue.newValue') AS MainValue,
JSON_VALUE(v.value, '$.fields."System.UserName"') AS itemUserName,
JSON_VALUE(v.value, '$.fields."System.State".newValue') AS itemStateNewValue
FROM OPENJSON(@v_Json, '$.value') AS v Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply