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 — Bergi
– Bergi, Commented Jul 29, 2023 at 3: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
– edA-qa mort-ora-y, Commented Jul 30, 2023 at 7:36

Marth · Accepted Answer · 2021-02-19 11:35:15Z

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, 2021 at 11:35

answered Feb 19, 2021 at 11:23

Marth

24.9k3 gold badges68 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

edA-qa mort-ora-y Over a year ago

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.

Marth Over a year ago

@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.

edA-qa mort-ora-y Over a year ago

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 Over a year ago

I found an alternate solution as well, see my answer. Do you know which approach might make more sense?

edA-qa mort-ora-y · Accepted Answer · 2021-02-19 11:47:46Z

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, 2021 at 11:47

edA-qa mort-ora-y

32.3k43 gold badges154 silver badges281 bronze badges

1 Comment

Marth Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related