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

Question

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.

Answer 1

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;