My JSON data has the key with [. When I try OpenJSON it complains about the [. Could you help.
DECLARE @Json VARCHAR(MAX)
SET @Json = '{"[key]":"val"}'
SELECT *
FROM openjson(@json)
WITH (C1 VARCHAR(200) '$.[key]')
Msg 13607, Level 16, State 4, Line 7 JSON path is not properly formatted. Unexpected character '[' is found at position 2.
OPENJSON works with arrays. The JSON string {"[key]":"val"} specifies a single object. In this case you should use JSON_QUERY or JSON_VALUE.
To get the key's value, use [JSON_VALUE] :
select JSON_VALUE('{"[key]":"val"}','$."[key]"')
returns val.
You should investigate why the property's name is [key] instead of key though. This defines defines a property named [key] instead of key.
This is completely unexpected and confusing to everyone. In fact, that's what was wrong with the path expression. This will cause problems for any deserializers that try to generate objects from this data, eg in C# or Javascript. You can't define a property named [key] which means that extra mapping work is needed to use this JSON string.
Whatever process generates this string should be changed to remove the brackets
[key]instead ofkeyas the object's property name? This is going to cause problems for everyone using that object. I'm not sure that JSON deserializers will be able to produce objects from this - how can you generate a C# or Javascript property called[key]? You should probably change whatever generates this JSON string to producekeyinstead of[key].