How to Convert PostgreSQL 9.4's jsonb Type to Float

The specifics: Casting jsonb to Float

When dealing with numeric values housed inside a jsonb field, proper casting is essential to sidestep any potential errors. Essentially, PostgreSQL 9.4 doesn’t inherently provide a direct route for casting from jsonb to float. Instead, this manoeuvre requires us to first convert the jsonb value to text, and then we can safely cast it to float.

Remember MongoDBsation? Here is PostgresCasting: "if it's castable, it's postgresable!"

If the value inside your jsonb cannot be cast to float, PostgreSQL will return NULL. To ensure all values in your jsonb field are numbers before proceeding with the conversion, try this:

SELECT (jsonb_column->>'key')::float
FROM your_table
WHERE (jsonb_column->>'key') ~ '^\s*-?\d+(\.\d+)?\s*$';


As they say, "Cast softly, and verify regex." 😁

Into the Depths: Handling Nested JSON

Situationally, the targeted number might be submerged more deeply in a nested JSON structure. In these cases, you will need to drill down to the right depth before extracting the text for casting:

SELECT ((json_data->'position'->>'lat')::text::float) AS latitude
FROM your_table;


Because floats sometimes like to play hide-and-seek. 🙈

Population Control: Using jsonb_populate_record

In scenarios where you have to deal with the entire JSON, jsonb_populate_record() can be your lifesaver. Define a custom type or use a temporary table to mark the required structure:

CREATE TYPE my_row_type AS (
   lat float,
   lng float
);

SELECT *
FROM jsonb_populate_record(NULL::my_row_type, '{"lat": "40.7128", "lng": "-74.0060"}'::jsonb);


...because how you structure your query, structures your life.

Exception, not the Rule: Edge Cases and Errors

There can be errors during casting from jsonb to float, especially if the JSON values aren't compatible with float data type or unexpected characters show up.

Dealing with the Uncastable

For values that resist being cast to float, you can either omit them or handle them separately. The CASE statement comes in handy here:

SELECT 
  CASE 
    WHEN (jsonb_column->>'num') ~ '^\s*-?\d+(\.\d+)?\s*$' THEN (jsonb_column->>'num')::float
    ELSE NULL  -- or a default value
  END AS float_value
FROM your_table;


"Can't cast? Don't worry, we have a CASE for it!" 😄

Run a Benchmark

Performance is key when dealing with large datasets. Run a quick EXPLAIN ANALYSE to benchmark your JSON to SQL conversion queries:

EXPLAIN ANALYSE SELECT (jsonb_column->>'number')::float FROM your_table;


"ANALYSE today. Improve tomorrow."

The Future is Bright: Reasons to Upgrade

While we've been focusing on PostgreSQL 9.4 thus far, the later versions (especially PostgreSQL v12.3) allow more direct casting from jsonb to SQL types. An upgrade to your database system could be in order if the version limitations are too restricting.