Menu
We store data as a JSON string in our database. Due to the use of Newtonsoft JSON, this data contains fields that have $ signs in the key.
Is it possible to query these fields using the JSON_VALUE function in MSSQL?
The query I tried throws exception JSON path is not properly formatted. Unexpected character '$' is found at position 2.
SELECT JSON_VALUE(JsonData, '$.$type') FROM dbo.JsonTable
The JsonData looks like this:
{
"$type": "Project.Classes.JsonType",
"amount": 50
}
704
You might think that JSON is a string). In this context $ is a mongodb positional operator to perform update in a specific position.
Option 1 - You control output with FOR JSON PATHIn PATH mode, you can use the dot syntax - for example, 'Item. Price' - to format nested output. Here's a sample query that uses PATH mode with the FOR JSON clause. The following example also uses the ROOT option to specify a named root element.
As is mentioned in the documentation, you need to surround the key name with quotes, if the key name starts with a dollar sign or contains special characters such as spaces.
T-SQL:
DECLARE @json nvarchar(max) = N'{"$type": "Project.Classes.JsonType", "amount": 50}'
SELECT JSON_VALUE(@json, '$."$type"') AS JsonValue
Output:
------------------------
JsonValue
------------------------
Project.Classes.JsonType
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
