CODEWITHSUNDEEP
postgresql
edA-qa mort-ora-y
edA-qa mort-ora-y

Reputation: 31851

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

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.

Upvotes: 1

Views: 2762

Answers (2)

edA-qa mort-ora-y
edA-qa mort-ora-y

Reputation: 31851

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.

Upvotes: 2

Marth
Marth

Reputation: 24802

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

Upvotes: 2

Related Questions