T-SQL: how to process JSON elements with dot in their names?

user_4659220 0
Viewed 445

I have JSON object I need to process, using T-SQL. I can successfully retrieve all the elements, except those, having "." (dot) in their names, e.g. System.State, System.UserName. Can one advise how it can be addressed, please?

    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;

SELECT  JSON_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

Edited to add a result screen, where I would expect to see, for example, "User Name 1" in column itemUserName.

enter image description here

tsql json
3 Answers

It should be enough to use quotes in your JSON path:

    JSON_VALUE(v.value, '$.fields."System.UserName"') AS itemUserName,

This returns with values:

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"
                }
            }
        }
    ]
}';

--the query

SELECT  JSON_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".oldValue') AS itemState,
        JSON_VALUE(v.value, '$.fields."System.State".newValue') AS itemStateNewValue

FROM OPENJSON(@v_Json, '$.value') AS v;
user_50892040

To repeat my answer from SQL Server Central, with the 3rd set of sample data I was provided (the above is the 4th..):

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 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 returns the dataset below:

System.State
-----------------------------------------
{
                    "newValue": "New"
                }
{
                    "oldValue": "new",
                    "newValue": "Defined"
                }

db<>fiddle

user_20299830

This opens and flattens the JSON and assigns unique column names

select *
from openjson (@v_Json) with ([count] int,
                              [value] nvarchar(MAX) AS JSON) J
     cross apply openjson(j.[value])
                 with (id               int,
                       workItemId       int,
                       fields           nvarchar(max) as json) v
     cross apply openjson(v.fields) with ([System.Id] nvarchar(max) as json,
                                          [System.State] nvarchar(max) as json) f
     outer apply openjson(f.[System.Id]) with (sys_id_newValue     int '$.newValue',
                                               sys_id_oldValue     int '$.oldValue') si
     outer apply openjson(f.[System.State]) with (sys_st_newValue     nvarchar(4000) '$.newValue',
                                                  sys_st_oldValue     nvarchar(4000) '$.oldValue');
user_67487580
Related