MS SQL Query a field containing JSON

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??

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.