How to cast / parse JSON string as number / float in Postgres?

Question

I have a JSONB column in my Postgres DB. In some cases it contains numbers encoded as strings*, such as: "123" or "3.232" or "2.32e14". The quotes are part of the stored JSONB value, it's "123" not 123.

I'd like to sum up these values, thus need a way to treat them as floating point. I cannot find any way in PSQL to do this.

select column::float fails since they are JSON strings, not DB text type. Nor does column::text::float work.

select to_number(column::text, '999.999') doesn't work since to_number requires a fixed format of number and I can't find a way to parse a standard formatted floating point number.

How can I convert the JSONB string-encoded values into a numeric value for computations?

*Background: They are stored as strings because storage needs to be exact and does not survive a round-trip to/from a float point. For calculations it's okay to reduce precision to float. They could survive a round-trip to a high-level decimal format, but it's a limitation of the Python library SQLAlchemy with JSON that prevents this from working. Even if we find a solution here, I still need to migrate existing data, thus the question stands.

Does this answer your question? [How to convert PostgreSQL 9.4's jsonb type to float](https://stackoverflow.com/questions/24826385/how-to-convert-postgresql-9-4s-jsonb-type-to-float) — Bergi, Jul 29 '23 at 03:07
No, as it deals with a sub-field and misses the specific need to cast the top-level value. — edA-qa mort-ora-y, Jul 30 '23 at 07:36

Marth · Answer 1 · 2021-02-19T11:35:15.063

2

Assuming you're using postgresql 12+, you can use the .double() jsonpath method for this:

WITH d(json) AS (
    VALUES ('"123"'::jsonb)
         , ('"3.232"')
         , ('"2.32e14"')
)
SELECT json, JSONB_TYPEOF(json), jsonb_path_query(json, '$.double()')
FROM d;

json	jsonb_typeof	jsonb_path_query
123	string	123
3.232	string	3.232
2.32e14	string	232000000000000

View on DB Fiddle

Since the path here only ever returns a single double the jsonb_path_query_first() function is more appropriate, as the jsonb_path_query() returns a set of result (usually used in a subquery):

WITH d(json) AS (
    VALUES ('"123"'::jsonb)
         , ('"3.232"')
         , ('"2.32e14"')
)
SELECT SUM(jsonb_path_query_first(json, '$.double()')::float)
FROM d;

sum
232000000000126.22

edited Feb 19 '21 at 11:35

answered Feb 19 '21 at 11:23

Marth

23,920
3
60
72

I'm not sure what type this is returning, but the result does not appear to be usable in a computation such as `sum`. That is, `select sum(jsonb_path_query(json, '$.double()))` does not work. `aggregate function calls cannot contain set-returning function calls`. So I presume the path function is an aggregate, not a value. – edA-qa mort-ora-y Feb 19 '21 at 11:27
@edA-qamort-ora-y: you're right, the `jsonb_path_query` returns a set and so is not really meant to be used directly in the `SELECT` clause most of the time. I mostly used it to show the `$.double()` jsonpath could work. I added a example using `jsonb_path_query_first()`, which works better here since it returns a single result. – Marth Feb 19 '21 at 11:36
Ah, okay. I tried the `::float` cast but the `_first` variation of the function was the trick to getting it to work. – edA-qa mort-ora-y Feb 19 '21 at 11:43
I found an alternate solution as well, see my answer. Do you know which approach might make more sense? – edA-qa mort-ora-y Feb 19 '21 at 11:48

score 2 · Answer 2 · answered Feb 19 '21 at 11:47

2

This syntax converts the type to an unescaped JSON value and then casts to a float:

(value#>>'{}')::float

For example, in a sum:

WITH d(json) AS (
    VALUES ('"123"'::jsonb)
         , ('"3.232"')
         , ('"2.32e14"')
)
SELECT SUM((json#>>'{}')::float)
FROM d;

This achieves the same result as the answer from @Marth. I'm uncertain which is the more correct, performant, or preferred approach.

answered Feb 19 '21 at 11:47

edA-qa mort-ora-y

30,295
39
137
267

1

I wouldn't have guessed that "value at current path" (since you could write your query as `json#>>(ARRAY[]::text[])` with the array the path to look up) would extract the text value itself and make it castable, but it makes sense. The main difference I see with my answer is the error for non-numeric values (generic cast error e.g "invalid input syntax for type double precision" vs "jsonpath item method .double() can only be applied to a string or numeric value"). I can't say which is faster, though I would guess it's yours. Anyway, good to have a solution for postgresql <12, +1. – Marth Feb 19 '21 at 12:58

How to cast / parse JSON string as number / float in Postgres?

2 Answers2