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 19 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy