How to convert PostgreSQL 9.4's jsonb type to float

sql postgresql casting jsonb postgresql-9.4

fadedbee · Jul 18, 2014 · Viewed 48.2k times · Source

I'm trying the following query:

SELECT (json_data->'position'->'lat') + 1.0 AS lat FROM updates LIMIT 5;

(The +1.0 is just there to force conversion to float. My actual queries are far more complex, this query is just a test case for the problem.)

I get the error:

ERROR:  operator does not exist: jsonb + numeric

If I add in explicit casting:

SELECT (json_data->'position'->'lat')::float + 1.0 AS lat FROM updates LIMIT 5;

the error becomes:

ERROR:  operator does not exist: jsonb + double precesion

I understand that most jsonb values cannot be cast into floats, but in this case I know that the lats are all JSON numbers.

Is there a function which casts jsonb values to floats (or return NULLs for the uncastable)?

Answer

There are two operations to get value from JSON. The first one -> will return JSON. The second one ->> will return text.

Try

SELECT (json_data->'position'->>'lat')::float + 1.0 AS lat
FROM updates
LIMIT 5