Getting the Primary key of the row which can not be parsed

Question

I am trying to parse a json value from a column in Azure Sql Database with the following query

select Key, JSON_VALUE(JsonField, '$.Total') 
from MyTable

However I am encountering some error in parsing that produces the following message

[12:37:32]  Started executing query at Line 12

    Msg 13609, Level 16, State 2, Line 1
JSON text is not properly formatted. Unexpected character '.' is found at position 0. 

Is there any way to understand which rows have the following problem by, for example, having the column set to NULL by default in the return?

This way I can make a direct check on the resulting field.

Answer 1

You can use the T-SQL ISJSON function to identify problem values:

SELECT
      Key
    , JsonField
FROM dbo.MyTable
WHERE ISJSON(JsonField) = 0;