Handling Properties That Change Type in JSON Event Data

You write a dashboard query over a JSON column. It works for a month. One morning it fails with:

ERROR:  invalid input syntax for type numeric: "n/a"

The query did not change. A client library started sending "n/a" for an amount it could not compute, and the whole report, over every other row, now fails. This is what happens when a property changes type. It is a different failure from a missing key (which gives NULL) because the database refuses to guess, and one value stops the statement.

I reproduced it on a 2.25-million-row event table by planting the kinds of type change I have seen in real event data, and then found out what each fix does.

First, see what is there

Before you fix the query, count the types. jsonb_typeof returns the JSON type of a value:

select jsonb_typeof(props->'amount') as type, count(*)
from an_events where name = 'purchase' group by 1 order by 2 desc;

  type  | count
 number | 297110
 string |   2429
        |    187
 null   |    143
 object |    131

Five shapes of amount. A number is what the cast expects. 2,429 strings, 131 objects, 143 JSON nulls and 187 events with no amount key at all (the blank row: jsonb_typeof of a missing key is SQL NULL). The same count by key and type is the first diagnostic for any JSON column (JSON columns and schema drift has the version that runs across all keys).

Then look inside the strings:

select pg_input_is_valid(props->>'amount','numeric') as parses, count(*)
from an_events where name='purchase' and jsonb_typeof(props->'amount')='string' group by 1;
-- f  539
-- t 1890

1,890 strings parse as numbers ("0.5", "20.5") and 539 do not. A sample of the common values:

n/a    343
4,5    196
0.5     76
20.5    76

"4,5" is a comma decimal: a client with a European locale formatted the number as text. It does not parse as numeric in PostgreSQL, and a fix that replaces the comma with a dot recovers it. "n/a" is a refusal to provide a value.

Why the naive query fails

select sum((props->>'amount')::numeric) from an_events where name = 'purchase';
-- ERROR: invalid input syntax for type numeric: "n/a"

->> returns text for any type: the number 42.5 becomes '42.5', and so does the string "n/a". The cast then fails on the first text it cannot read. Where the failing row is processed is not guaranteed, so the same query can succeed on a different day on a different plan, which makes the bug look intermittent. pg_input_is_valid is available in PostgreSQL 16 and later; on earlier versions you test with a regular expression or write a small function with an exception handler.

Fix 1: only the numbers

The strictest guard keeps the rows whose type is right:

select count(*), sum((props->>'amount')::numeric)
from an_events where name='purchase' and jsonb_typeof(props->'amount') = 'number';
-- 297110 | 13278707.0

Safe and conservative. It also drops 2,890 events from the total. Whether they matter is a business question: if the 1,890 strings that parse are real purchases, revenue is understated by their sum.

Fix 2: tolerate what can be read

Extend the condition to numeric-looking strings:

... where name='purchase' and (jsonb_typeof(props->'amount') = 'number'
   or (jsonb_typeof(props->'amount') = 'string' and pg_input_is_valid(props->>'amount','numeric')))
-- 299000 | 13370500.0

The total went from 13,278,707.0 to 13,370,500.0, a difference of 91,793, and the row count to 299,000. Neither figure is wrong. They answer different questions: "revenue from well-formed events" and "revenue from events I could interpret." State which one the dashboard shows, in its title.

Fix 3: one function that normalizes

Putting the rules in the query invites each analyst to write them differently. A function keeps them in one place. My first attempt, as an SQL function with a CASE, did not work:

create function amount_of(p jsonb) returns numeric language sql immutable as $$
  select case jsonb_typeof(p->'amount')
    when 'number' then (p->>'amount')::numeric
    when 'string' then case when pg_input_is_valid(replace(p->>'amount',',','.'),'numeric')
                       then replace(p->>'amount',',','.')::numeric end
    ...

It raised the same error, invalid input syntax for type numeric: "n/a", with the context SQL function "amount_of" statement 1. A CASE is supposed to evaluate only the branch that applies, and a plain select over a constant showed why it did not here: select case when pg_input_is_valid('n/a','numeric') then 'n/a'::numeric end also raised, because the planner evaluates constant subexpressions while planning, before the CASE can choose. SQL functions are planned with the argument values in them. The likely cause is that, though I did not look into the planner further. The same logic in plpgsql does not have the problem:

create function amount_of(p jsonb) returns numeric language plpgsql immutable as $$
declare t text := jsonb_typeof(p->'amount'); s text;
begin
  if t = 'number' then return (p->>'amount')::numeric;
  elsif t = 'string' then
    s := replace(p->>'amount', ',', '.');
    if pg_input_is_valid(s, 'numeric') then return s::numeric; end if;
  elsif t = 'object' and jsonb_typeof(p->'amount'->'value') = 'number' then
    return (p->'amount'->>'value')::numeric;
  end if;
  return null;
end $$;

It returned NULL for {"amount":"n/a"} and {} and {"amount":null}, 4.5 for "4,5" and 3 for the object {"value": 3}. Across the purchase events:

 usable | unusable |    sum
 299327 |      673 | 13371971.5

299,327 rows produced a value and 673 did not. The unusable ones, by type:

string   343    (the text n/a)
missing  187
null     143

That list is a to-do for the people who send the events. The function took 918 ms for the 300,000 rows against 97 ms for the simple guarded form, because it runs plpgsql per row. For a nightly extraction that is fine (extracting properties into typed columns every night runs a function like this once per event). For a dashboard that scans the raw table every time, move the result into a column.

Indexes and generated columns fail too

An index on the plain cast refuses to build:

create index on an_events (((props->>'amount')::numeric)) where name='purchase';
-- ERROR: invalid input syntax for type numeric: "n/a"

A generated column defined with the plain cast would be expected to fail the same way, which I did not run. Use the function in the definition, if it is immutable and safe, or index only the rows that are known good (where jsonb_typeof(props->'amount') = 'number').

Fix it at the source as well

Query-side tolerance is a bandage. The fix that holds is upstream: the producer sends the type the contract says. Enforce it where events arrive, with a schema that says amount is a number and rejects or quarantines the rest (validating request JSON before it reaches the database), and watch the counts per type so that a new shape is noticed the day it appears. When the type of a property must change on purpose, treat it as a new property (amount becomes amount_cents) and read both during the transition (versioning JSON payloads). Rewriting old rows is the last step (backfilling historic events after a schema change).

The functions used here are documented in the PostgreSQL manual, and the type check function in system information functions. To inspect an odd payload, paste it into the JSON formatter.

When the right fix is upstream, not in the query

One client is wrong. If all 343 "n/a" values come from one app version, fix that client and quarantine its events. A query that tolerates them forever hides the bug.

The type change is a new meaning. A number that became an object with a currency is a different property. Give it a new name and read both.

The data is money. Silently reading "4,5" as 4.5 or ignoring "n/a" changes revenue. Reject and report, and decide with the owner of the number.

The table is small and temporary. A one-off analysis can use the guarded form and move on. The function and the monitoring are for data you will use again.