PostgreSQL JSON字段经纬度距离计算查询失败求助

核心错误原因 #

1. JSON字段访问语法错误：你写的location -> latLong[0]不符合PostgreSQL的JSON访问规则，latLong是JSON对象的键名，必须用单引号包裹，且要先获取数组再提取元素。正确的写法是通过location -> 'latLong'拿到数组，再用->> 索引提取对应位置的元素（->>会把JSON值转为文本格式）。
2. 缺少数据类型转换：从JSON提取的文本需要显式转为数值类型，才能被point()函数正确处理。

修复后的SQL查询 #

假设location是JSON/JSONB类型，且latLong数组顺序为[纬度, 经度]，修复后的语句如下：

SELECT *, 
       point(59.9260437, 10.7221398) <@> 
       point( (location -> 'latLong' ->> 0)::numeric, 
              (location -> 'latLong' ->> 1)::numeric ) AS distance
FROM myapp.recipes
ORDER BY distance;

额外注意事项 #

• 若你用earthdistance扩展计算球面距离，point()函数的参数顺序是**(经度, 纬度)**，需要调整坐标顺序：

  SELECT *, 
         point(10.7221398, 59.9260437) <@> 
         point( (location -> 'latLong' ->> 1)::numeric, 
                (location -> 'latLong' ->> 0)::numeric ) AS distance
  FROM myapp.recipes
  ORDER BY distance;
  

• 确保已启用依赖的扩展（<@>操作符需要cube和earthdistance），未启用的话先执行：

  CREATE EXTENSION IF NOT EXISTS cube;
  CREATE EXTENSION IF NOT EXISTS earthdistance;
  

内容的提问来源于stack exchange，提问作者Oliver Dixon