Most sources (e.g. this answer) seem to indicate that the canonical way to extract a numeric data type from a JSONB column with values like, say, {"foo": 1.2345} is to do (col->>'foo')::numeric.

Theoretically, though, the semantics are that this first needs to be converted from JSONB (which is fully capable of representing numerics in a binary format) to a textual representation of the characters, then parsed as a numeric.

If this is done in a tight loop, or as the basis for complicated comparisons, perhaps visiting the same row multiple times due to a join, does Postgres automatically know that it doesn't need to format-and-parse these numbers every time, and does it optimize (or even JIT) accordingly? Is there a better way to do this?

  • 3
    The solution is, to properly normalize your data model and store that as a real column. If you really need this a lot, you could think about creating a generated column that extracts that numeric value – a_horse_with_no_name May 27 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.