How to parse JSON column using TSQL

Question

I'm trying to parse a specific, valid JSON string from a column to its individual values using T-SQL.

I have looked at many of the samples, particularly this one Parse JSON in TSQL and still am not quite there. Can anyone suggest a valid T-SQL statement that will do the job?

Sample cte with json in column MessageDetail:

select Id, MessageDetail from cte_example

Id  MessageDetail
1   {"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", "sysadmins": {"sysadmin": {"Member": "DummyAdmin", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-22T18:10:55.023", "Removed": "2019-07-22T19:21:15.867"}}}

T-SQL I'm trying to use:

select
    json_value(b.value, '$.Member') as Member
    ,json_value(b.value, '$.IsDisabled') as IsDisabled
    ,json_value(b.value, '$.IsNTGroup') as IsNTGroup
    ,json_value(b.value, '$.Added') as Added
    ,json_value(b.value, '$.Removed') as Removed
from
    cte_example a
    outer apply openjson(json_query(a.MessageDetail, '$.sysadmins.sysadmin')) b

Which results in the below error:

Msg 13609, Level 16, State 2, Line 17 JSON text is not properly formatted. Unexpected character 'D' is found at position 0.

Which I find confusing as the JSON query $.sysadmins.sysadmin is valid. What am I doing wrong?

Note: I have this query working correctly when it attempts to parse the below

{"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", "sysadmins": {"sysadmin": [{"Member": "sa", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "testuser", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "abc\\User1", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "NT SERVICE\\SQLWriter", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "NT SERVICE\\Winmgmt", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "NT Service\\MSSQLSERVER", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "NT SERVICE\\SQLSERVERAGENT", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "DummyAdmin", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-22T02:10:07.833", "Removed": "2019-07-22T03:00:02.177"}, {"Member": "domain1\\testservice", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T04:18:51.900"}, {"Member": "##MS_PolicyEventProcessingLogin##", "IsDisabled": "1", "IsNTGroup": "0", "Added": "2019-07-22T04:07:48.497"}]}}

Answer 1

Solution 1:

If you want to use JSON_VALUE, what you need in this situation is to use OPENJSON with explicit schema and AS JSON option in a column definition. The path argument here is $.sysadmins:

Table:

CREATE TABLE cte_example (
   Id int,
   MessageDetail nvarchar(max)
)
INSERT INTO cte_example
   (Id, MessageDetail)
VALUES   
   (1, N'{"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", "sysadmins": {"sysadmin": {"Member": "DummyAdmin", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-22T18:10:55.023", "Removed": "2019-07-22T19:21:15.867"}}}')

Statement:

SELECT 
   JSON_VALUE(b.sysadmin, '$.Member') AS Member,
   JSON_VALUE(b.sysadmin, '$.IsDisabled') AS IsDisabled,
   JSON_VALUE(b.sysadmin, '$.IsNTGroup') AS IsNTGroup,
   JSON_VALUE(b.sysadmin, '$.Added') AS Added,
   JSON_VALUE(b.sysadmin, '$.Removed') AS Removed
FROM cte_example a
OUTER APPLY OPENJSON(a.MessageDetail, '$.sysadmins') WITH (
   sysadmin nvarchar(max) '$.sysadmin' AS JSON
) b

Output:

-------------------------------------------------------------------
Member      IsDisabled  IsNTGroup   Added                   Removed
-------------------------------------------------------------------
DummyAdmin  0           0           2019-07-22T18:10:55.023 2019-07-22T19:21:15.867

Solution 2:

You may try with another approach, without JSON_VALUE, again using OPENJSON with explicit schema definition. The path argument here is $.sysadmins.sysadmin:

SELECT b.*
FROM cte_example a
OUTER APPLY OPENJSON(a.MessageDetail, '$.sysadmins.sysadmin') WITH (
   Member nvarchar(10) '$.Member', 
   IsDisabled nvarchar(1) '$.IsDisabled', 
   IsNTGroup nvarchar(1) '$.IsNTGroup', 
   Added nvarchar(23) '$.Added', 
   Removed nvarchar(23) '$.Removed'
) b

Explanations about your error:

With statement:

select
    b.*
from
    cte_example a
    outer apply openjson(json_query(a.MessageDetail, '$.sysadmins.sysadmin')) b

your results are:

---------------------
key value   type 
---------------------
Member      DummyAdmin              1
IsDisabled  0                       1
IsNTGroup   0                       1
Added       2019-07-22T18:10:55.023 1
Removed     2019-07-22T19:21:15.867 1

Values in value column is not in JSON format and json_value(b.value, '$.Member') returns an error.