How to convert PostgreSQL 9.4's jsonb type to float
Solution 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
Solution 2
AFAIK there's no json->float casting in Postgres, so you could try an explicit (json_data->'position'->'lat')::text::float
cast
Solution 3
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
.
Solution 4
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".
Solution 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.
fadedbee
Updated on July 08, 2022Comments
-
fadedbee almost 2 years
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)?
-
Abhijit Gujar over 6 yearsprobably you don't want to mess with value so use ::float + 0.0 AS lat, 1 lat change is very big difference !!
-
n3rd over 4 yearswhy do I need to add 0.0 (or 1.0 in your case), isn't it already typcasted into a float using ::float?
-
Peter Krauss over 3 yearsSeems that the only optimization is with
jsonb_populate_record
(check alsojsonb_to_record
orjsonb_to_recordset
). See also this dba discussion -
Peter Krauss almost 3 yearsPlease correct to "direct cast" on pg12.3+ as showed by stackoverflow.com/a/62621483/287948
-
Peter Krauss almost 3 yearsOn pg12.3+ you can do "direct cast", as showed by stackoverflow.com/a/62621483/287948
-
Peter Krauss almost 3 yearsOn pg12.3+ you can do "direct cast", as showed by stackoverflow.com/a/62621483/287948 (below)
-
Peter Krauss almost 3 yearsThe advantage of JSONb is the NUMBER and BOOLEAN binary formats, and there is no need for intermediary conversion to text. So, this answer is the best because remember a good practice: don't waste your CPU time with intermediary CAST to text!. By other hand, unfortunately, PostgreSQL developers have done no more than that: the only evidence about "non-reduntant CAST" in PostgreSQL is when using
jsonb_populate_record
(or recordset). See dba.stackexchange.com/a/271249/90651 -
Gaurav about 2 yearsworks flawlessly for accessing inner elements
-
Bergi almost 2 years"cannot cast jsonb null to type integer" is not a bug. You cannot cast objects or arrays to integers either. Use
NULLIF(j->'i', 'null')::int
, or(j->>'i')::int