0

I am trying to validate if some text, which is json, before processing it further.

I am using the ISJSON function in SQL Server 2016.

I have a problem that some of my json is slightly malformed; it contains unescaped newline char, quotes and tabs. (I may also get some trailing commas as well, but these are less of a concern).

Json libraries in languages like C# are more forgiving and just transform/ignore these small errors.

Is there a way to allow ISJSON() to be less strict and accept unescaped newline, quote and tab chars?

Or

Is there a way in SQL to check if some text is valid json; which is forgiving of unescaped newline, quotes and tab chars?

Examples:

'[{"key1": "Some value",}]' -- Trailing comma

'[{"key1": "Some \value"}]' -- Unescaped backslash

'[{"key1": "Some value"}]' -- Unescaped tab

'[{"key1": "Some value"}]' -- Unescaped new line

2
  • 2
    What you are describing is incoming data issue that you need to fix before validating. There are no knobs that you can turn on to make isjson() less strict.
    – Kin Shah
    Nov 4, 2017 at 19:52
  • @Kin I agree. However all of the C# libraries I have tried will just transform/ignore these small errors; which is desirable in 99% of all cases. But in this case wont stop bad json getting trough to the DB and failing there. (This is an error we are having in production) Which I why I was looking to see if is a way to get ISJSON() to be more forgiving. ( I am also looking into finding a stricter parser upstream). Also due to the data provenance requirements I can't use C# (etc.) to transform the data prior to it landing in the DB. Any other suggestions gladly accepted. Nov 4, 2017 at 23:45

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.