PostgreSQL and jsonb type casts

Found a bug in one of our systems at work that deals with jsonb data stored in Postgres today. The quick summary is that one of our systems was looking for the maximum numerical value of a jsonb integer in a table.

A very simplified example:

SELECT
  max(balances)
FROM
  unnest(ARRAY[
    '{"balance": 7}'::jsonb->>'balance',
    '{"balance": 17}'::jsonb->>'balance'
  ]) as balances

This returns 7, because while the “balance” field is a numeric JSON value, the ->> operator turns the value into text! — which makes the comparison a lexicographical one.

Instead, you need to make sure to cast the values before comparing:

SELECT
  max(balances)
FROM
  unnest(ARRAY[
    ('{"balance": 7}'::jsonb->>'balance')::integer,
    ('{"balance": 17}'::jsonb->>'balance')::integer
  ]) as balances

Which returns the expected 17.