How to convert PostgreSQL 9.4's jsonb type to float

Question

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 1

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

Answer 2

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

Answer 3

Now we can do it!

In nowadays we can cast directly from JSONb to SQL datatypes. I am using PostgreSQL v12.3, where it is working fine:

SELECT (j->'i')::int, (j->>'i')::int, (j->'f')::float, (j->>'f')::float
FROM  (SELECT '{"i":123,"f":12.34}'::jsonb) t(j); 

---

Sub-questions:

• From which version is it possible?

• It is a syntax sugar or a real conversion?

• If real "binary JSONb → binary SQL" conversion, where the micro-optimizations?
  For example, what wold be faster (?) tham "binary JSONb → string → binary SQL"? boolean→boolean, number→numeric, number→int, number→bigint; number→flloat, number→double.

• Why not optimized for NULL?
  Curiosily the "NULL to SqlType" not works, "ERROR: cannot cast jsonb null to type integer".

---

Benchmark suggestion

How to check? When PostgreSQL optimize loop queries?

EXPLAIN ANALYSE SELECT (j->'i')::int, (j->'f')::float       -- bynary to bynary INT and FLOAT
-- EXPLAIN ANALYSE SELECT (j->>'i')::int, (j->>'f')::float  -- string to bynary INT and FLOAT

-- EXPLAIN ANALYSE SELECT (j->'i')::numeric, (j->'f')::numeric    -- bynary to bynary NUMERIC
-- EXPLAIN ANALYSE SELECT (j->>'i')::numeric, (j->>'f')::numeric  -- string to bynary NUMERIC

FROM (
  SELECT (('{"i":'||x||',"f":'||x||'.34}')::jsonb) as j FROM  generate_series(1,599999) g(x)
  -- SELECT (('{"i":123,"f":12.34}')::jsonb) as j FROM  generate_series(1,599999) g(x)
) t;

---

PostgreSQL bug?

Even now, 2021 with version pg13 version... Not make sense to not cast NULLs: the natural is to cast NULL::int to integer, but PostgreSQL fail in the automatic cast:

SELECT (j->'i')::int FROM  (SELECT '{"i":null}'::jsonb) t(j); -- fail

results in "ERROR: cannot cast jsonb null to type integer".

Answer 4

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.

Answer 5

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.

Answer 6

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

Try this:

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

Answer 7

When creating a view I used CAST:

create view mydb.myview as
            select id,
            config->>'version' as version,
            config->>'state' as state,
            config->>'name' as name,
            config->>'internal-name' as internal_name,
            config->>'namespace' as namespace,         
            create_date,
            update_date,
            CAST(config ->> 'version' as double precision) as version_number
            from mydb.mytbl;