zlwaterfield zlwaterfield - 1 month ago 4x
SQL Question

Postgres JSONB: query by negative numbers

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';

returned address:
"street":"Princes Highway",
"formatted":"Riversleigh, 1235 Princes Hwy, Heathmere VIC 3305, Australia"

As you can see i am trying to find the rows where the latitude is less than 40, but that returns no row. However when i check greater than 40 it returns the row where the latitude to -38.19523969999999.

Any help is appreciated.


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 address JSON; '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;
(1 row)

and the WHERE clause in your second query is always true:

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

If you want to dig into your address JSON, you want to use the #>> operator:

#>> text[]
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) 40:

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