Extracting Properties Into Typed Columns Nightly From JSON

Raw event tables answer any question, slowly. A dashboard that sums revenue by day does not need every property of every event. It needs four columns of the purchase events, typed, indexed and arriving on time. The usual solution is a second table of facts, rebuilt or extended every night from the raw one. The pattern is old, and what makes it succeed is a handful of details: how bad values are handled, how the job knows where it stopped, and what happens when it runs twice.

I built the job on a 2-million-row jsonb event table in PostgreSQL and measured each part.

The facts table

Decide the columns by the questions people ask. For revenue and signups: when, who, how much, in what currency, which plan.

create table an_facts(
  event_id bigint primary key,
  ts       timestamptz not null,
  user_id  int not null,
  plan     text,
  amount   numeric(12,2),
  currency text,
  items    int,
  day      date generated always as ((ts at time zone 'UTC')::date) stored
);

The primary key is the id of the raw event, which is what makes the job restartable. The day column is generated, so there is exactly one definition of "a day" (UTC here) in the table and every report that groups by day agrees. A report that does date_trunc('day', ts) in the session's zone and another that uses UTC produce two sets of daily totals that differ near midnight. This is the same decision that came up with order exports (flattening a Shopify style order export).

Bad values must not stop the run

The extraction is a cast of text to a number. In PostgreSQL a failed cast aborts the statement:

select ('12,5')::numeric;     -- ERROR: invalid input syntax for type numeric: "12,5"

A nightly job that stops because one event has "n/a" in its amount is a job that stops every night until someone fixes the event. Since PostgreSQL 16 there is a function that asks whether a cast would work:

case when pg_input_is_valid(props->>'amount', 'numeric')
     then (props->>'amount')::numeric end as amount

An amount that is not a number becomes NULL in the facts table and the run continues. The same can be done for integers (pg_input_is_valid(props->>'items','integer')) and dates. Do not stop there: count what was discarded and report it. A NULL that replaces a bad value is information you chose to drop, and the number of them is a measure of data quality. A careful job also writes the event id and the reason for every discarded value to a side table, and raises an alert when the count rises (handling properties that change type goes deeper on the reasons).

The first load

insert into an_facts(event_id, ts, user_id, plan, amount, currency, items)
select id, ts, user_id, props->>'plan',
       case when pg_input_is_valid(props->>'amount','numeric') then (props->>'amount')::numeric end,
       props->>'currency',
       case when pg_input_is_valid(props->>'items','integer') then (props->>'items')::int end
from an_events where name in ('purchase','signup');

500,000 rows loaded in 1.14 s from the 2,000,000 raw events. 250,000 rows have an amount (the purchases) and 250,000 have a plan (the signups). The facts table is 50 MB with its primary key, against 284 MB for the raw table, so the working set for dashboards is a sixth of the size.

Nightly: a watermark and an idempotent insert

Each night the job should process only what is new. The watermark is the highest raw id already in the facts table:

select coalesce(max(event_id), 0) as wm from an_facts \gset

insert into an_facts(event_id, ts, user_id, plan, amount, currency, items)
select id, ts, user_id, props->>'plan', ...
from an_events
where id > :wm and name in ('purchase','signup')
on conflict (event_id) do nothing;

I added 50,000 new purchase events and ran it: 0.2 s, and the table went from 500,000 to 550,000 rows. Then I ran the insert again over an overlapping range, starting 1,000 ids earlier:

... where id > :wm - 1000 ...  on conflict (event_id) do nothing;
-- count stays 550,000

The count did not change. ON CONFLICT DO NOTHING on the primary key makes the job idempotent: a crash halfway through, a rerun by hand or a deliberate overlap to catch late-arriving events all leave the table correct. The overlap matters in real pipelines, where events arrive out of order and an id lower than the watermark can show up after it. Choose a lookback window, reprocess it every night, and let the conflict clause ignore duplicates.

DO NOTHING is the right behavior when an event never changes. If the raw event can be corrected, use DO UPDATE SET ... for the columns that may change, and only update when they differ, so the job does not rewrite every row.

For ids that are not monotonic (UUIDs), the watermark is a timestamp of arrival, not the id. Keep an ingested_at column on the raw table and use that. Do not use the event's own timestamp, which can be hours old when it arrives.

What the dashboard gains

The daily revenue query, on the facts table with an index on (day, currency):

select day, currency, sum(amount) from an_facts where amount is not null group by 1, 2 order by 1;
-- 2.29 ms

And on the raw events:

select date_trunc('day', ts) d, props->>'currency', sum((props->>'amount')::numeric)
from an_events where name = 'purchase' and jsonb_typeof(props->'amount') = 'number' group by 1, 2 order by 1;
-- 107.99 ms

The same totals (174,600.00 on the first day) in 2.3 ms against 108 ms: 47 times faster, from a table a sixth of the size. A dashboard that issues twenty queries a page notices. The difference grows with the table, since the typed scan stays proportional to 500,000 rows and the raw one to 2,000,000, with a parse of JSON on each.

Operating it

A few practical points once the job exists.

Schedule it with a lock. Two overlapping runs are harmless to correctness with the conflict clause, and wasteful. Use an advisory lock or the scheduler's concurrency setting.

Alert on volume. A night where the number of new rows is a tenth of normal usually means an upstream outage. A job that reports "0 rows, success" is the worst failure to miss.

Version the mapping. The mapping from property to column is code. Keep it in the repository, review changes, and when a property moves or is renamed, extend it to read both names for the transition (versioning JSON payloads).

Plan the backfill. A new column needs history. Add the column, run an update in batches over old events, and then let the nightly job take over (backfilling historic events after a schema change).

Keep the raw events. The facts table is derived. If the extraction was wrong, you fix the query and rebuild from raw.

The same shape works in other engines. In MySQL it is JSON_TABLE or generated columns (JSON_TABLE in MySQL), in DuckDB a create table as over the JSON files with try_cast. The pg_input_is_valid function is documented in the PostgreSQL manual under system information functions, and INSERT ... ON CONFLICT in the INSERT reference. To check what a property looks like before mapping it, use the JSON formatter.

When a nightly extraction is the wrong shape

You need fresh numbers. A nightly job gives yesterday's data. For live dashboards, a streaming insert or a trigger populates the facts table as events arrive, or a materialized view refresh runs more often.

The table is small. Under a few million rows, an expression index on the raw JSON may be all you need (indexing hot JSON properties for dashboards).

The mapping changes weekly. If the questions keep changing, the facts table will be wrong every week. Keep the JSON and write views until the questions settle.

The warehouse does it for you. Some platforms materialize typed columns from semi-structured data automatically. Check what yours does before writing a job.