If you’re using a T-SQL function such as OPENJSON(), JSON_QUERY(), or JSON_VALUE(), you might be wary of any non-alphanumeric characters that might be in the JSON document that you’re working with. Especially if those special characters are in the key names, and you need to reference those key names.

For example, you could have a key name that contains a space (like "first name"), or a dollar sign ($).

Fortunately, any time you reference such keys, you can simply surround the key name with double quotes.

Example 1 – OPENJSON()

Here’s an example of referencing a key with a space in its name when using OPENJSON().

DECLARE @json NVARCHAR(4000) = N'{
        "contact details" : {
            "client id" : 1, 
            "work phone" : "+61 987 902 029"
        }
    }';
SELECT * FROM OPENJSON(@json, '$."contact details"');

Result:

+------------+-----------------+--------+
| key        | value           | type   |
|------------+-----------------+--------|
| client id  | 1               | 2      |
| work phone | +61 987 902 029 | 1      |
+------------+-----------------+--------+

Here’s another example, this time we have a dollar sign in the key name.

DECLARE @json NVARCHAR(4000) = N'{
        "$ per hour" : {
            "normal rate" : 80, 
            "overtime" : 160 
        }
    }';
SELECT * FROM OPENJSON(@json, '$."$ per hour"');

Result:

+-------------+---------+--------+
| key         | value   | type   |
|-------------+---------+--------|
| normal rate | 80      | 2      |
| overtime    | 160     | 2      |
+-------------+---------+--------+

Example 2 – JSON_QUERY()

Here’s an example using JSON_QUERY().

DECLARE @json NVARCHAR(4000) = N'{
        "contact details" : {
            "client id" : 1, 
            "work phone" : "+61 987 902 029"
        }
    }';
SELECT JSON_QUERY(@json, '$."contact details"');

Result:

+--------------------+
| (No column name)   |
|--------------------|
| {
            "client id" : 1, 
            "work phone" : "+61 987 902 029"
        }                    |
+--------------------+

And here it is with a dollar sign.

DECLARE @json NVARCHAR(4000) = N'{
        "$ per hour" : {
            "normal rate" : 80, 
            "overtime" : 160 
        }
    }';
SELECT JSON_QUERY(@json, '$."$ per hour"');

Result:

+--------------------+
| (No column name)   |
|--------------------|
| {
            "normal rate" : 80, 
            "overtime" : 160 
        }                    |
+--------------------+

Example 3 – JSON_VALUE()

This example uses JSON_VALUE().

DECLARE @json NVARCHAR(4000) = N'{
        "contact details" : {
            "client id" : 1, 
            "work phone" : "+61 987 902 029"
        }
    }';
SELECT JSON_VALUE(@json, '$."contact details"."work phone"');

Result:

+--------------------+
| (No column name)   |
|--------------------|
| +61 987 902 029    |
+--------------------+

And here it is with a dollar sign.

DECLARE @json NVARCHAR(4000) = N'{
        "$ per hour" : {
            "normal rate" : 80, 
            "overtime" : 160 
        }
    }';
SELECT JSON_VALUE(@json, '$."$ per hour"."normal rate"') AS [Normal Rate];

Result:

+---------------+
| Normal Rate   |
|---------------|
| 80            |
+---------------+