sql - 如何修复 SQL Server Json 文本格式不正确的问题。在第 151 位发现意外字符

Question

我正在处理一个包含 JSON 格式列的表，我想从该列中提取坐标值。
所以我运行以下代码:

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

运行 5 分钟后出现以下错误

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

当我尝试检查一些前 200 * 下面的行时，代码返回没有任何错误的结果

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

我想我有一行导致错误，但我不知道如何识别它或排除该行并返回结果。

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