I am trying to query based on lat and lng values but the rows returned aren't matching my query when the value store is negative. However when i query positive values it works as expected. I am wondering if there is a specific way i need to format my queries.
Here are some example queries to prove the weird results:
db_development=# select address from listing where 'address.coords.latitude' < '40';
no rows returned.
db_development=# select address from listing where 'address.coords.latitude' > '40';
"formatted":"Riversleigh, 1235 Princes Hwy, Heathmere VIC 3305, Australia"
You have two problems or perhaps two versions of the same problem is more accurate. In your query:
select address from listing where 'address.coords.latitude' > '40'
'address.coords.latitude' is a string literal, not a path into the
'40' is also a string literal, not the number
40. So your WHERE clause is simply comparing two string literals, it isn't comparing the
latitude embedded in the
address column with the number
40. That means WHERE clause in your first query is always false:
=> select 'address.coords.latitude' < '40' as correct; correct --------- f (1 row)
and the WHERE clause in your second query is always true:
=> select 'address.coords.latitude' > '40' as confused; confused ---------- t (1 row)
If you want to dig into your
address JSON, you want to use the
Get JSON object at specified path as text
so you'd say:
address #>> array['coords', 'latitude']
to look at the embedded
latitude; you could also use the
jsonb_extract_path_text function if you or your tools don't like the
#>> operator. Then you'd need to cast that
text value to a number to get the comparison to work properly and compare that number to a plain (numeric)
select address from listing where (address #>> array['coords', 'latitude'])::numeric > 40
Similarly for your first query:
select address from listing where (address #>> array['coords', 'latitude'])::numeric < 40