I have a JSON file from Azure's DevOps I need to process. It seems to be straight forward on columns with one word, but how do I process those with dot between them, please?
{
"count": 56,
@value" :[
{"id": 1,
"workItemId: 1234,
"fields":
{"System.Id": {"newValue": 1234}
"System.State": {"newValue": "New"},
{"id": 2,
"workItemId: 1234,
"fields":
{
"System.State": {"oldValue": "new", "newValue": "Defined"}
}
}
]
}I run
SELECT JSON_VALUE(v.value, '$.id') AS id,
JSON_VALUE(v.value, '$workItemId') AS workItemId
FROM OpenJson(@v_MyJsonString, '$.value) as v
and do receive the columns as expected, but when I add something like
JSON_VALUE(v.value, '$.fields.System.State') AS State
State column simply shows NULL. I have tried [] brackets on System.State but received an error.
I have also tried to add.newValue: '$.fields.System.State.newValue' to no avail.
Can one advise, please?
September 30, 2020 at 1:13 pm
The JSON you've given us isn't even valid. If we take the time to format it properly, there's plenty of problems.
{
"count": 56,
@value" :[ //No opening double quotes(")
{
"id": 1,
"workItemId: 1234, //Missing closing double quotes (")
"fields":
{
"System.Id": {"newValue": 1234}
"System.State": {"newValue": "New"},
{
"id": 2,
"workItemId: 1234,//Missing closing double quotes (")
"fields":
{
"System.State": {"oldValue": "new", "newValue": "Defined"}
}
}
] //The brace ({}) haven't been closed yet
}
//Missing final brace ({})Before you can parse JSON it needs to be valid.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 30, 2020 at 1:19 pm
The JSON you've given us isn't even valid. If we take the time to format it properly, there's plenty of problems.
{
"count": 56,
@value" :[ //No opening double quotes(")
{
"id": 1,
"workItemId: 1234, //Missing closing double quotes (")
"fields":
{
"System.Id": {"newValue": 1234}
"System.State": {"newValue": "New"},
{
"id": 2,
"workItemId: 1234,//Missing closing double quotes (")
"fields":
{
"System.State": {"oldValue": "new", "newValue": "Defined"}
}
}
] //The brace ({}) haven't been closed yet
}
//Missing final brace ({})Before you can parse JSON it needs to be valid.
Apologies, but I took a part of the code to demo the query I have - how to process elements with dot in their names.
I don't expect a solution, but the guidelines.
Much obliged for taking your time to look into.
September 30, 2020 at 1:19 pm
If, however, you have valid JSON, then simply delimit identify the column names in your WITH clause:
DECLARE @JSON nvarchar(MAX) = N'{
"Count":56,
"Fields":{
"System.ID": 17,
"System.Name": "Jupiter"
}
}';
SELECT *
FROM OPENJSON (@JSON)
WITH ([Count] int,
Fields nvarchar(MAX) AS JSON) J
CROSS APPLY OPENJSON(J.Fields)
WITH ([System.ID] int,
[System.Name] nvarchar(20)) F;Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 30, 2020 at 1:34 pm
Following your correct comment, below is fully working SQL example for your perusal:
DECLARE @v_Json NVARCHAR(MAX) = N'{
"count": 56,
"value": [{
"id": 1,
"workItemId": 1234,
"fields": {
"System.Id": {
"newValue": 1234
},
"System.State": {
"newValue": "New"
}
}
},
{
"id": 2,
"workItemId": 1234,
"fields": {
"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') AS fields,
JSON_VALUE(v.value, '$.fields.System.State') AS itemState
FROM OPENJSON(@v_Json, '$.value') AS v September 30, 2020 at 1:36 pm
I think CROSS APPLY in this case is overkilling, as there is only one "fields" complex element per id. What do you think?
September 30, 2020 at 1:40 pm
CROSS APPLY will affect show plan significantly on large JSON files.
September 30, 2020 at 1:45 pm
CROSS APPLY will affect show plan significantly on large JSON files.
CROSS APPLY has nothing to do with the performance there; are you actually suggesting that OPENJSON is slow?
By "show plan" do you actually mean the execution plan?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 30, 2020 at 1:47 pm
Sorry for Oracle, yes, Execution Plan. I am not suggesting anything, yet asking for your opinion.
September 30, 2020 at 1:49 pm
Sorry for Oracle, yes, Execution Plan. I am not suggesting anything, yet asking for your opinion.
Then I don't understand your point here. You suggested that "Cross Apply was overkilling", if that isn't the case, then what is the problem with the answer above? Does it not work?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 30, 2020 at 2:03 pm
Providing I have implemented your suggestion correctly as per my code below, I receive no value for System.State either
DECLARE @v_Json NVARCHAR(MAX) = N'{
"count": 56,
"value": [{
"id": 1,
"workItemId": 1234,
"fields": {
"System.Id": {
"newValue": 1234
},
"System.State": {
"newValue": "New"
}
}
},
{
"id": 2,
"workItemId": 1234,
"fields": {
"System.State": {
"oldValue": "new",
"newValue": "Defined"
}
}
}
]
}';
SELECT *
FROM OPENJSON (@v_Json)
WITH ([count] int,
value nvarchar(MAX) AS JSON) J
CROSS APPLY OPENJSON(J.value)
WITH ([System.State] NVARCHAR(50)) F; September 30, 2020 at 2:16 pm
Because System.State is in value.fields, you're looking for value."System.ID", which doesn't exist. System.State is also a further JSON value. You can short cut to value.fields in the OPENJSON call, which gives you the following:
SELECT F.[System.State]
FROM OPENJSON (@v_Json,'$.value')
WITH (fields nvarchar(MAX) AS JSON ) J
CROSS APPLY OPENJSON(J.fields)
WITH ([System.State] nvarchar(MAX) AS JSON) F;
Which gives 2 rows, one for each instance of System.State, with the values
{
"newValue": "New"
}and
{
"oldValue": "new",
"newValue": "Defined"
}Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 30, 2020 at 2:43 pm
I have added more elements to "fields" complex one and seems I can receive the results without CROSS APPLY, but not those with the dot
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"
}
}
}
]
}';
--SELECT F.[System.State]
--FROM OPENJSON (@v_Json)
-- WITH ([count] int,
-- value nvarchar(MAX) AS JSON) J
-- CROSS APPLY OPENJSON(J.value)
-- WITH ([System.State] NVARCHAR(50)) F;
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') AS itemState,
JSON_VALUE(v.value, '$.fields.System.State.newValue') AS itemStateNewValue
FROM OPENJSON(@v_Json, '$.value') AS v September 30, 2020 at 2:48 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 reference fields."System.State".newValue (" added for clarity), it's trying to reference the element State in the element System ; neither of which exist.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply