T-SQL - JSON_QUERY : select json properties with special characters in the property name

Question

I'm facing some issues while trying to select a value from a JSON column. The json is:

{
    "$type":"myNameSpace.myClass, myDll"
}

And I'm trying to query it with something like

SELECT myIdColumnName, myJsonColumnName, JSON_QUERY(myJsonColumnName, '$.$type') as mType

the problem is that the path '$.$type' is invalid, the italian error is:

 Il formato del percorso JSON non è corretto. È stato trovato il carattere imprevisto '$' nella posizione 2.

which basically tells that the parser does not expect "$" after ".". I already tried using '$.type' and '$."$type"' but it in both cases I get null as mType.

Could you tell me the right syntax for this query?

Thank you

Answer 1

When you want to extract JSON object or scalar value and your path begins with a dollar sign $, you need to surround it with quotes ". Function JSON_QUERY extracts an object or an array from a JSON string, so JSON_VALUE is more appropriate here when you want to extract a scalar value from JSON text.

Example:

DECLARE @json nvarchar(max) = N'{
    "$type":"myNameSpace.myClass, myDll"
}'

SELECT JSON_VALUE(@json, '$."$type"')

Output:

--------------------------
(No column name)
--------------------------
myNameSpace.myClass, myDll