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

your JSON examples are different structures. Your first one sysadmin isn't an array, but the second one you listed sysadmin is an array? Which one is it as the tsql code will be different? — Tim Mylott, Jul 22 '19 at 21:35
The reason your first one gives an error is because in that situation since sysadmin is not an array the value you get in b.value isn't a valid JSON so json_value throws the error. Your second example, sysadmin is an array, as a result you get a valid json in b.value and json_value can traverse it. — Tim Mylott, Jul 22 '19 at 21:42
Yes I see that now. I think now I realize I can't parse both with the same query. I'll most likely need to see if I can get the "singular" JSON string made into an array as well? — Dean McMillan, Jul 22 '19 at 22:05
As an aside: that "JSON" is pretty clearly clumsily converted XML, complete with now no longer necessary `xsi` attribute. SQL Server also has excellent support for storing and parsing XML, so there might be an opportunity here for cutting out a conversion step. — Jeroen Mostert, Jul 23 '19 at 09:27

Zhorov · Answer 1 · 2019-07-23T09:06:56.230

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.

score 0 · Accepted Answer · answered Jul 23 '19 at 14:11

Using OPENJSON() and the WITH clause will allow you to handle both situations or sysadmin as an array or not.

Here's a working example that includes both:

DECLARE @testData TABLE
    (
        [id] INT
      , [MessageDetail] NVARCHAR(MAX)
    );

INSERT INTO @testData (
                          [id]
                        , [MessageDetail]
                      )
VALUES (1 , '{"@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"}]}}' )
       ,(2 , '{"@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"}}}')


SELECT [b].*
FROM   @testData [a]
CROSS APPLY
       OPENJSON([a].[MessageDetail], '$.sysadmins.sysadmin')
           WITH (
                    [member] NVARCHAR(100) '$.Member'
                  , [IsDiabled] NVARCHAR(100) '$.IsDisabled'
                  , [IsNTGroup] NVARCHAR(100) '$.IsNTGroup'
                  , [Added] DATETIME '$.Added'
                  , [Removed] DATETIME '$.Removed'
                ) [b];

@TimMylott It's just a comment, nothing more, but it's the same approach as `Solution 2` in my answer. I can't understand the reason for such a duplicate answer. — Zhorov, Jul 24 '19 at 08:11
@Zhorov I didn't see an answer when I originally posted, we must have been working on it at the same time. Wasn't trying to steal your answer. — Tim Mylott, Jul 24 '19 at 20:01

How to parse JSON column using TSQL

2 Answers2