How to convert PostgreSQL 9.4's jsonb type to float

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)?


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

Documentation: 9.4: Data Type Formatting Functions, The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted  The name of the conversion. The conversion name can be schema-qualified. If it is not, the conversion is defined in the current schema. The conversion name must be unique within a schema. source_encoding. The source encoding name. dest_encoding. The destination encoding name. function_name. The function used to perform the conversion.


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

Documentation: 9.4: Upgrading a PostgreSQL Cluster, This section discusses how to upgrade your database data from one PostgreSQL release to a newer one. PostgreSQL major versions are represented by the first  DROP CONVERSION removes a previously defined conversion. To be able to drop a conversion, you must own the conversion. To be able to drop a conversion, you must own the conversion. Parameters


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.

Documentation: 9.4: CREATE CAST, A cast specifies how to perform a conversion between two data types. For example, SELECT CAST(42 AS float8);. converts the integer constant 42 to type float8  Documentation → PostgreSQL 9.4. pg_conversion. The catalog pg_conversion describes encoding conversion procedures. See CREATE CONVERSION for more information.


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.

Migrate from PostgreSQL 9.4 to 9.5 with easy steps, Migrate from PostgreSQL 9.4 to 9.5 with easy steps. Introduction. PostgreSQL 9.5 was released on 01/07/2016. Backup the current database. Install PostgreSQL 9.5 on the server. Stop the PostgreSQL server. Migrate the configuration files from the old version to the new version. Keep PostgreSQL 9.4 from starting. Try How to convert postgresql 9.4 jsonb to object without function/server side language. Ask Question Asked 5 years, 4 months ago. Active 4 years, 9 months ago.


You must to cast the json value to text and then to float.

Try this:

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

Migrating the data from PostgreSQL 9.x to 10.x, In the process of upgrading, you need to migrate PostgreSQL 9.x database and configuration information to PostgreSQL 10.x. Migrating is  Functions and Operators. Next. 9.8. Data Type Formatting Functions. The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. Table 9-21 lists them.


[PDF] PostgreSQL 9.4.21 Documentation, src/tutorial/. (Binary distributions of PostgreSQL might not compile these files.) To use those files, first change to that directory and run make: $ cd ./src/tutorial. CREATE CAST defines a new cast. A cast specifies how to perform a conversion between two data types. For example, SELECT CAST(42 AS float8); converts the integer constant 42 to type float8 by invoking a previously specified function, in this case float8(int4). (If no suitable cast has been defined, the conversion fails.)


Documentation: 9.4: JSON Functions and Operators, PostgreSQL 9.4.26 Documentation from the type to json, the cast function will be used to perform the conversion; otherwise, a JSON scalar value is produced. When a pg_cast entry has different source and target types and a function that takes more than one argument, it represents converting from one type to another and applying a length coercion in a single step. When no such entry is available, coercion to a type that uses a type modifier involves two steps, one to convert between data types and a second to apply the modifier.


[PDF] Manually Upgrading PostgreSQL 9.1to PostgreSQL 9.4.19, This document describes how to manually update those databases to PostgreSQL 9.4.19. The processes described in this document are only  PostgreSQL also provides versions of these functions that use the regular function invocation syntax (see Table 9-7). Note: Before PostgreSQL 8.3, these functions would silently accept values of several non-string data types as well, due to the presence of implicit coercions from those data types to text .