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

Question

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?

'$."1"' is the correct way to escape that. What you're missing is that you want JSON_VALUE for a scalar value. — Jeroen Mostert
– Jeroen Mostert, Commented Sep 17, 2019 at 13:49
Thanks, please post that as an answer and I'll accept it. That works! — CatZilla
– CatZilla, Commented Sep 17, 2019 at 13:53

CatZilla · Accepted Answer · 2019-09-17 14:59:23Z

6

Should use JSON_VALUE:

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

Thanks to Jeroen Mostert

answered Sep 17, 2019 at 14:59

CatZilla

1,5963 gold badges12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related