Postgres JSONB：负数查询

Question

我想基于经纬度和lng值进行查询，但返回的行与价值存储为负值时的查询不匹配。但是，当我查询正值它按预期工作。我想知道是否有特定的方式我需要格式化我的查询。

这里有一些例子查询证明怪异的结果：

QUERY 1 
======= 
db_development=# select address from listing where 'address.coords.latitude' < '40'; 

no rows returned. 

QUERY 2 
======= 
db_development=# select address from listing where 'address.coords.latitude' > '40'; 

returned address: 
{ 
    "city":"Heathmere", 
    "state":"VIC", 
    "coords":{ 
    "latitude":-38.19523969999999, 
    "longitude":141.6158994 
    }, 
    "number":"1235", 
    "street":"Princes Highway", 
    "country":"Australia", 
    "zipcode":"3305", 
    "formatted":"Riversleigh, 1235 Princes Hwy, Heathmere VIC 3305, Australia" 
} 

正如你可以看到我试图找到行，其中纬度小于40，但没有返回行。但是，当我检查大于40时，它将返回纬度为-38.19523969999999的那一行。

任何帮助表示赞赏。

Answer 1

你有两个问题，或者是两个版本的同一问题的更准确。在您的查询：

select address from listing where 'address.coords.latitude' > '40' 

'address.coords.latitude'是一个字符串文字，而不是为address JSON路径; '40'也是一个字符串文字，而不是数字40。因此，您的WHERE子句仅仅比较两个字符串文字，它并不比较嵌入在address列中的latitude和编号40。这意味着，在第一个查询的WHERE子句中始终是假的：

=> select 'address.coords.latitude' < '40' as correct; 
correct 
--------- 
f 
(1 row) 

，并在你的第二个查询的WHERE子句永远是正确的：

=> select 'address.coords.latitude' > '40' as confused; 
confused 
---------- 
t 
(1 row) 

如果你想深入到你的address JSON，要使用#>> operator：在specifie

#>>text[]
获取JSON对象文字d路径

所以你会说：

address #>> array['coords', 'latitude'] 

看嵌入式latitude;如果您或您的工具不喜欢#>>运营商，您也可以使用jsonb_extract_path_text function。然后，你需要的是text值转换为数值，以获得比较正常工作，这个数字比作一个简单的（数字）40：

select address 
from listing 
where (address #>> array['coords', 'latitude'])::numeric > 40 

同样，对于第一个查询：

select address 
from listing 
where (address #>> array['coords', 'latitude'])::numeric < 40 

Answer 2

这是不正常的原因，因为您正在比较JSON文本和文本值。

您应该将JSON值转换为适当的数字类型，并与数字进行比较。

例如：

select address from listing where ('address.coords.latitude')::numeric < 40;