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

Question

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.

Answer 1

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

Answer 2

seems like you have bad data( in your case doublequote) in your JSON field. your TOP 200 runs successfully because that bad character is not showing on TOP 200.. I suggest if you don't have a check constraint on that column add one to avoid bad

ALTER TABLE tablename ADD CONSTRAINT JSON_CHECK CHECK (ISJSON(field) = 1 ) 

character in your JSON data: .,to add double quote as part of values in your JSON it shuld look like this : "JSON Value with \" in the value"

for example [{"Id":3,"Title":"JSON Value with \" in the value"}]