I have a JSON blob column (eg Groups) in a user table that contains a json object as follows:
{Security:[1,5],Reporting:[2,8]}
If i try and query that table using JSON_QUERY I get a badly formatted error. So for eg
SELECT JSON_QUERY([Groups],'$.Security') from User
Returns
JSON text is not properly formatted. Unexpected character 'S' is found at position 1.
The reason is because JSON blobs in SQL server seem to insist on double quotes on the object attributes. So if the above JSON looks like this (note the double quotes) then all is fine.
{"Security":[1,5],"Reporting":[2,8]}
The problem is that I am using ServiceStack in a C# application that is automatically building this JSON blob based on pre-defined User poco and inserting directly into the DB via the API. When it inserts this JSON blob as predefined on the POCO it automatically inserts it without the double quotes....like this.
{Security:[1,5],Reporting:[2,8]}
Now once again any kind of JSON_QUERY sql that I am using (and need to use) in views that consume this data is failing.
Can anyone suggest an approach here to deal with this or some clarification as to why this would be happening. There seems to be an inconsistency in how the JSON data is represented between the C# code and SQL server that's making it incompatible.
Thanks!
ServiceStack's Servicestack.Text JSON Serializer always double quotes properties as required for valid JSON.
If you're talking about OrmLite's blobs for complex types that's serialized using ServiceStack's JSV Format by default for SQL Server which stores keys and values using CSV Format which it only uses double-quotes when required.
OrmLite's docs shows how you can configure the Complex Type Serializer per RDBMS Dialect, e.g. you can configure SQL Server to serialize Complex Types using JSON with:
SqlServerDialect.Provider.StringSerializer = new JsonStringSerializer();