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 ofkey
as 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 producekey
instead of[key]
.