[SOLVED] how to fix SQL server Json text is not properly formatted. Unexpected character is found at position 151

how to fix SQL server Json text is not properly formatted. Unexpected character is found at position 151

Im working on a table that has a column in JSON format and I want to extact the coordinate value from that column.

So I run the following code:

Select *,JSON_VALUE(field,'$."Coordinate-X"[0]') As coordinate INTO TABLE_1 FROM table
WHERE JSON_VALUE(field,'$."Coordinate-X"[0]') IS NOT NULL

And I get the following error after 5 mins of running

Msg 13609, Level 16, State 1, Line 27 Json text is not properly formatted. Unexpected character '"' is found at position 151.

When I try to check some rows with top 200 * below code returns results withour any error

Select TOP 200 *,JSON_QUERY(field,'$."Coordinate-X"[0]') As coordinate FROM table
WHERE JSON_VALUE(field,'$."Coordinate-X"[0]') IS NOT NULL

I guess I have a row that is causing the error but I dont know how to identify it or exclude that row and return the results.

Solution

I managed to run around the problem with this where statement in case someone has same problem I hope it helps!

Select *,JSON_QUERY(field,'$."Coordinate-X"[0]') As coordinate FROM table
WHERE field like '%Coordinate-X%'
and ISJSON(field)=1