How to convert PostgreSQL 9.4's jsonb type to float

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

Details: JSON Functions and Operators

Try

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

AFAIK there's no json->float casting in Postgres, so you could try an explicit (json_data->'position'->'lat')::text::float cast

Per documentation, there are also the functions

jsonb_populate_record()
jsonb_populate_recordset()

Analog to their json twins (present since pg 9.3)

json_populate_record()
json_populate_recordset()

You need a predefined row type. Either use the row-type of an existing table or define one with CREATE TYPE. Or substitute with a temporary table ad hoc:

CREATE TEMP TABLE x(lat float);

Can be a single column or a long list of columns.

Only those columns are filled, where the name matches a key in the json object. The value is coerced to the column type and has to be compatible or an exception is raised. Other keys are ignored.

SELECT lat + 1  -- no need for 1.0, this is float already
FROM   updates u
     , jsonb_populate_record(NULL::x, u.json_data->'position')
LIMIT  5;

Using an implicit LATERAL JOIN here.

Similarly, use jsonb_populate_recordset() to decompose arrays into multiple rows per entry.

This works the same way in Postgres 9.3 with json. There is the added benefit that casting to / from text internally is not necessary for numeric data in jsonb.

Adding a clarification because this comes up as the top hit for a 'JSONB float conversion' search - note that you need to wrap the JSON conversion in brackets, and then apply the '::' casting.

As mentioned above, the correct method is:

(json_data #>> '{field}')::float

If instead you try this it will fail:

json_data #>> '{field}'::float

This was the mistake I was making in my code and it took me a while to see it - easy fix once I noticed.