jsonsql-server

SQL Server - how to query JSON string which has numeric value as keys

I have a table in the database that holds a JSON string in one of its columns. I need to query this string.

The issue is that some of the key names in the JSON string are numeric, and SQL Server refuses to allow me to query them.

Example JSON string: { "1": "abc", "2": "bcd" }

When I attempt to execute the following code, I get an error:

SELECT
    JSON_QUERY(JSONMessage, '$.1') as test
  FROM [table]

Error:

Msg 13607, Level 16, State 4, Line 2
JSON path is not properly formatted. Unexpected character '1' is found at position 2.

I've tried the following:

JSON_QUERY(JSONMessage, '$.''1''')
JSON_QUERY(JSONMessage, '$."1"')
JSON_QUERY(JSONMessage, '$.[1]')
JSON_QUERY(JSONMessage, '$.{1}')
JSON_QUERY(JSONMessage, '$.["1"]')
JSON_QUERY(JSONMessage, '$."[1]"')

..without any positive results.

Is there some trick to this?

Solution

  • Should use JSON_VALUE:

    SELECT
JSON_VALUE(JSONMessage, '$."1"') as test
FROM [table]

    Thanks to Jeroen Mostert