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
.