PostgreSQL: Cast JSONB to Integer

You’ve got numeric data stored inside JSONB objects, and you need to treat it as actual integers for calculations, comparisons, or storage. The challenge is getting that JSON number out and converting it to a proper integer type.

Quick Answer: Use :: Cast Operator

To cast a JSONB value to integer, extract it as text with ->>, then cast to integer:

SELECT
    emp_id,
    first_name,
    metadata,
    (metadata ->> 'salary')::INTEGER as salary_int,
    (metadata ->> 'years_experience')::INTEGER as years_int
FROM employees
WHERE metadata IS NOT NULL
LIMIT 5;

Returns:

emp_id | first_name | metadata                    | salary_int | years_int
-------+------------+-----------------------------+------------+----------
     1 | James      | {"salary":100000,"years":5} |     100000 |         5
     2 | Mary       | {"salary":120000,"years":8} |     120000 |         8

That’s the core pattern. Extract as text, then cast to integer.

How It Works

The ->> operator pulls the JSONB value out as a text string. Once it’s text, the ::INTEGER cast converts it to an actual integer type. PostgreSQL handles the conversion automatically if the text represents a valid integer.

Different Approaches & When to Use Them

Approach 1: Simple Cast (Most Common)

The straightforward approach for numeric JSONB values:

SELECT
    (metadata ->> 'count')::INTEGER as count,
    (metadata ->> 'price')::DECIMAL(10,2) as price
FROM products
WHERE metadata IS NOT NULL;

This works perfectly when you know the JSONB contains valid numbers.

Best for: Clean data where you trust the format.

Approach 2: Using CAST Function (More Explicit)

For clarity in complex queries, use the CAST function instead of :::

SELECT
    emp_id,
    CAST(metadata ->> 'salary' AS INTEGER) as salary,
    CAST(metadata ->> 'bonus' AS INTEGER) as bonus
FROM employees
WHERE metadata IS NOT NULL;

This is equivalent to the :: operator but more explicit about intent.

Best for: Team projects where explicit > implicit.

Approach 3: Direct JSONB to Integer (PostgreSQL 14+)

If you’re on newer PostgreSQL, you can sometimes cast directly:

SELECT
    (metadata -> 'salary')::TEXT::INTEGER as salary
FROM employees;

The extra ::TEXT step ensures compatibility.

Approach 4: Safe Casting with NULL Handling

When data quality varies, catch invalid conversions:

SELECT
    emp_id,
    CASE
        WHEN metadata ->> 'salary' ~ '^\d+$'  -- Regex: digits only
        THEN (metadata ->> 'salary')::INTEGER
        ELSE 0  -- Default for invalid values
    END as safe_salary
FROM employees;

The regex ^\d+$ checks that the string contains only digits before attempting conversion.

Best for: Production systems with messy data.

Approach 5: Cast JSONB Arrays to Integer Array

If your JSONB contains an array of numbers:

SELECT
    emp_id,
    (
        SELECT array_agg((elem::TEXT)::INTEGER)
        FROM jsonb_array_elements(metadata -> 'scores') AS elem
    ) as scores_as_integers
FROM employees
WHERE metadata -> 'scores' IS NOT NULL;

This handles arrays of JSON numbers.

Best for: Complex JSONB structures with numeric arrays.

Real-World Example: Calculate Compensation

Calculate total employee compensation from JSONB metadata:

SELECT
    emp_id,
    first_name,
    (metadata ->> 'salary')::INTEGER as annual_salary,
    (metadata ->> 'bonus')::INTEGER as bonus,
    ((metadata ->> 'salary')::INTEGER + (metadata ->> 'bonus')::INTEGER) as total_comp,
    ROUND(
        ((metadata ->> 'salary')::INTEGER + (metadata ->> 'bonus')::INTEGER) / 12.0,
        2
    ) as monthly_income
FROM employees
WHERE metadata ->> 'salary' IS NOT NULL
ORDER BY total_comp DESC;

Now you can do math with these values, create comparisons, and generate reports.

Using Cast Results in Calculations

Once cast to integer, you can use all numeric operators:

SELECT
    emp_id,
    -- Arithmetic
    (metadata ->> 'salary')::INTEGER * 1.1 as salary_with_raise,

    -- Comparisons
    CASE WHEN (metadata ->> 'salary')::INTEGER > 100000
         THEN 'Senior'
         ELSE 'Junior'
    END as level,

    -- Aggregations
    (metadata ->> 'salary')::INTEGER as base_salary
FROM employees
WHERE (metadata ->> 'salary')::INTEGER > 50000;

Performance Notes

Casting happens at query time, so there’s a tiny performance cost. If you’re casting the same value repeatedly, consider:

  1. Creating a computed column
  2. Storing the value as integer instead of JSON
  3. Creating an index on the cast result (PostgreSQL 14+): CREATE INDEX idx ON employees((metadata ->> 'salary')::INTEGER)

Common Patterns

-- Cast with arithmetic in WHERE
WHERE (metadata ->> 'years')::INTEGER > 5

-- Cast array elements
SELECT array_agg((elem::TEXT)::INTEGER)
FROM jsonb_array_elements(metadata -> 'numbers') AS elem

-- Cast with COALESCE for NULL handling
SELECT COALESCE((metadata ->> 'count')::INTEGER, 0)

-- Cast with GROUP BY
GROUP BY (metadata ->> 'department_id')::INTEGER

-- Cast in ORDER BY
ORDER BY (metadata ->> 'priority')::INTEGER DESC

FAQ

Q: Why do I need ->> first? Can’t I cast directly from JSONB? A: JSONB to integer cast isn’t direct in most PostgreSQL versions. The ->> extracts it as text, which casts reliably to integer.

Q: What happens if the text isn’t a valid integer? A: PostgreSQL throws an error: “invalid input syntax for integer.” Use regex validation first if data quality varies.

Q: Can I cast DECIMAL JSONB values to integers? A: Yes: (metadata ->> 'price')::DECIMAL(10,2)::INTEGER but you’ll lose decimals. Be intentional about truncation.

Q: Should I store numbers as JSON or native types? A: Native types (INTEGER, DECIMAL) are faster. Use JSONB only when structure varies. If all records have the same numeric fields, use integer columns.

Q: Can I create indexes on casted JSONB values? A: Yes (PostgreSQL 14+): CREATE INDEX idx ON table ((metadata ->> 'count')::INTEGER). This makes lookups instant.

Q: What about casting NULL values? A: NULL::INTEGER returns NULL. Check with IS NOT NULL first if you need a default.

Q: Performance difference between :: and CAST? A: None. PostgreSQL optimizes both identically.

Wrapping Up

Casting JSONB to integer is a two-step process: extract as text with ->>, then cast with ::INTEGER`. This pattern works consistently across PostgreSQL versions and gives you proper integer types for math and comparisons.

The key takeaway: always extract JSONB to text first, then cast to the target type. It’s reliable and works everywhere.