1

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!

Gotts
  • 2,274
  • 3
  • 23
  • 32
  • 1
    Fix the application or contact the vendor to go have a look at http://json.org/. – Salman A Apr 30 '19 at 15:29
  • 1
    `JSON blobs in SQL server seem to insist on double quotes on the object attributes.` That's how JSON is defined by the spec. Attribute keys must have double quotes to be valid JSON, in any system. –  Apr 30 '19 at 15:32
  • Json keys must be quoted. Read [this](https://stackoverflow.com/questions/2067974/in-json-why-is-each-name-quoted) and [that](https://stackoverflow.com/questions/949449/do-the-json-keys-have-to-be-surrounded-by-quotes) for details. Therefor, your application is *not* creating a valid Json and should be fixed. – Zohar Peled Apr 30 '19 at 15:33

1 Answers1

3

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();
mythz
  • 141,670
  • 29
  • 246
  • 390
  • Thats helpful, will investigate. Thanks – Gotts Apr 30 '19 at 18:01
  • 1
    This was a big help. In the end I didnt want to change this global variable as not sure what effect it will have elsewhere. So I created an override toString() on my object that returns toJSV() and registered the object for serialization/deserialization and all good!! – Gotts Apr 30 '19 at 18:45