MS SQL Query a field containing JSON

Question

I have the following JSON in a SQL field in a table:

{
   "type": "info",
   "date": "2019/11/12 14:28:51",
    "state": {
        "6ee8587f-3b8c-4e5c-89a9-9f04752607f0": {
            "state": "open",
            "color": "#0000ff"
        }
    },
...
}

I query this in MS SQL using the folloing:

SELECT 
     JSON_VALUE(json_data, '$.type') AS msg_type
    ,JSON_VALUE(json_data, '$."date"') AS event_date
    ,JSON_QUERY(json_data, '$.state."6ee8587f-3b8c-4e5c-89a9-9f04752607f0".state') AS json_state
    ,JSON_QUERY(json_data, '$.state."6ee8587f-3b8c-4e5c-89a9-9f04752607f0".color') AS json_color
FROM 
[dbo].[tbl_json_dump]

To get the date (a reserved word) back I have to put the the field name in like $."date"

I cannot seem to get the data back for the state or color fields and I think it has to do with that it is nested under "6ee8587f-3b8c-4e5c-89a9-9f04752607f0" because when I query :

JSON_QUERY(json_data, '$.state."6ee8587f-3b8c-4e5c-89a9-9f04752607f0"') AS json_state

I get the object back -

{"state":"open","color":"#0000ff"}

but using

JSON_QUERY(json_data, '$.state."6ee8587f-3b8c-4e5c-89a9-9f04752607f0".state') AS json_state

it is not working

Any suggestions on what I'm doing wrong??

Salman A · Accepted Answer · 2019-11-13T13:51:03.607

3

Just replace JSON_QUERY with JSON_VALUE since you're interested in getting the value.

JSON_QUERY is supposed to return a JSON fragment and designed to work on objects and arrays, not values.

edited Nov 13 '19 at 13:51

answered Nov 13 '19 at 13:40

Salman A

262,204
82
430
521

Thank you for spotting the mistake... Fresh eyes on the issue always helps!! – Pieter Coetzer Nov 13 '19 at 17:10

Zhorov · Answer 2 · 2019-11-13T14:18:00.873

You may try with another possible approach (more complicated), which parses all nested JSON objects.

Table:

CREATE TABLE Data (
   JsonData nvarchar(max)
)
INSERT INTO Data
   (JsonData)
VALUES 
   (N'{
   "type": "info",
   "date": "2019/11/12 14:28:51",
    "state": {
        "6ee8587f-3b8c-4e5c-89a9-9f04752607f0": {
            "state": "open",
            "color": "#0000ff"
        },
        "6ee8587f-3b8c-4e5c-89a9-9f04752607f1": {
            "state": "open",
            "color": "#0000ff"
        }
    }
}')

Statement:

SELECT 
   j1.[type], j1.[date], j2.[key], j3.state, j3.color
FROM Data d
CROSS APPLY OPENJSON(d.JsonData) WITH (
   [type] nvarchar(100) '$.type',
   [date] datetime '$.date',
   [state] nvarchar(max) '$.state' AS JSON
) j1
CROSS APPLY OPENJSON(j1.state) j2
CROSS APPLY OPENJSON(j2.[value]) WITH (
   state nvarchar(10) '$.state',
   color nvarchar(10) '$.color'
) j3

Result:

type    date                key                                     state   color
info    12/11/2019 14:28:51 6ee8587f-3b8c-4e5c-89a9-9f04752607f0    open    #0000ff
info    12/11/2019 14:28:51 6ee8587f-3b8c-4e5c-89a9-9f04752607f1    open    #0000ff

Notes:

If the input JSON has only one key "6ee8587f-3b8c-4e5c-89a9-9f04752607f0" in the "state" JSON object, you may get the value with JSON_VALUE() using the correct path $.state."6ee8587f-3b8c-4e5c-89a9-9f04752607f0".state.

Thank you for your comment... I will have a look at using your method in a other query I have to transpose data into a table — Pieter Coetzer, Nov 13 '19 at 17:05

score 1 · Answer 3 · answered Nov 13 '19 at 14:13

Salman A already provided the answer. Just to add a few points.

JSON_VALUE() - Extracts a Scalar value

JSON_QUERY() - Extracts an object or an array from a JSON string.

If you see the syntax , JSON_QUERY ( expression [ , path ] ) & JSON_VALUE ( expression , path ) , both are more or less except the [] square brackets for path and it means optional. It is because JSON_QUERY() can extract whole JSON field if required.

And on the return types,

JSON_VALUE() returns a JSON fragment of type nvarchar(max)

JSON_QUERY() returns a single text value of type nvarchar(4000)

Overall comparison

DECLARE @data NVARCHAR(4000)
SET @data=N'{
    "type": "info",
   "date": "2019/11/12 14:28:51",
    "state": {
        "6ee8587f-3b8c-4e5c-89a9-9f04752607f0": {
            "state": "open",
            "color": "#0000ff"
        }
    },
}'
 SELECT 
   JSON_VALUE(@data,'$.state."6ee8587f-3b8c-4e5c-89a9-9f04752607f0"') AS 'JSON_VALUE_FAILED',
   JSON_QUERY(@data,'$.state."6ee8587f-3b8c-4e5c-89a9-9f04752607f0"') AS 'JSON_QUERY_SUCCEED',
   JSON_VALUE(@data,'$.state."6ee8587f-3b8c-4e5c-89a9-9f04752607f0".state') AS 'JSON_VALUE_SUCCEED',
   JSON_QUERY(@data,'$.state."6ee8587f-3b8c-4e5c-89a9-9f04752607f0".state') AS 'JSON_QUERY_SUCCEED';

Check Output here

Thanks for your comment... As you rightfully said Salman A was able to spot my mistake, but I appreciate the valuable input. This will definitely help someone else when they search for info on MSSQL/JSON queries! — Pieter Coetzer, Nov 13 '19 at 17:09

MS SQL Query a field containing JSON

3 Answers3