JSON Columns and Schema Drift and How to Find It Early

A JSON column is a promise that the shape can change without a migration. Nobody promises that it will change in a controlled way. After a year of deploys, three teams and a vendor integration, the column holds documents of several shapes, and the reports that read it are right for some of them.

The drift is invisible until it is counted. I built a table of 500,000 events with the kinds of drift I see in real ones, found it with two queries, measured what it did to a report, and put a constraint in front of it.

The data

Events came in three eras. The first 200,000 had type, amount, currency and user_id. Later ones added coupon and a nested device object. Scattered through were three mistakes: every 1,000th row spelled the user key userId, every 1,000th had amount as a string, and every 700th had amount as JSON null. Nothing complained at insert time, because the column accepts any JSON.

Count the keys

The first question about an unfamiliar JSON column is which keys exist and how often:

select k, count(*) as docs, round(100.0*count(*)/500000, 2) as pct
from d_events, jsonb_object_keys(doc) k
group by k order by docs desc;

    k     |  docs  |  pct
----------+--------+--------
 type     | 500000 | 100.00
 currency | 500000 | 100.00
 amount   | 500000 | 100.00
 user_id  | 499500 |  99.90
 device   | 299314 |  59.86
 coupon   | 299314 |  59.86
 userId   |    500 |   0.10

It took 458 ms for 500,000 rows. Three kinds of information are in that table. Keys at 100 percent are the contract. Keys at 59.86 percent were added at some point, and any code that assumes they exist is wrong for 40 percent of the rows. And userId at 0.1 percent is a typo-sized key that sits next to user_id at 99.9 percent, a drift from a second spelling of the same field (naming keys consistently in JSON). Any query on user_id misses those 500 rows with no error.

Look for singletons and near-duplicates in the long tail. In a bigger table the tail is where a bug in one deploy lives.

Count the types

A key present is not a key usable. The second query groups by the JSON type of each value:

select k, jsonb_typeof(doc->k) as t, count(*)
from d_events, jsonb_object_keys(doc) k
where k in ('amount','user_id','userId','coupon')
group by 1,2 order by 1,3 desc;

    k    |   t    | count
---------+--------+--------
 amount  | number | 498857
 amount  | null   |    643
 amount  | string |    500
 coupon  | null   | 270000
 coupon  | string |  29314
 user_id | number | 499500
 userId  | number |    500

It took 638 ms. amount is the problem: 498,857 numbers, 500 strings and 643 nulls. coupon has a different sort of variation: it is null on 270,000 rows and a string on 29,314, so "no coupon" is spelled as null when the key is there, and absent in the older rows. A report that counts coupons with doc ? 'coupon' gets 299,314, nearly ten times the real 29,314.

Each of these has a reasonable story: a client library that serialized a decimal as a string, a field that became optional, a service that writes explicit nulls. The count is what turns the stories into a list of work.

What drift does to a report

The query people write first assumes the clean case:

select sum((doc->>'amount')::numeric) from d_events where doc->>'type' = 'purchase';
-- 24217677.5

It ran without an error and returned 24,217,677.5. The guarded version, which only counts values stored as numbers, returned:

select count(*), sum((doc->>'amount')::numeric)
from d_events
where doc->>'type' = 'purchase' and jsonb_typeof(doc->'amount') = 'number';
-- 498857 | 24193707.5

A difference of 23,970. The 500 string amounts held numeric text such as "12", and ->> returns text for both, so the cast accepted them. The 643 nulls dropped out of the sum in both. Neither version is plainly right. If the strings are real amounts, the first total is correct, and if they are a bug in a client, the second is. The drift made the answer depend on how the query was written, and that is the real cost: two analysts can run two reasonable queries and report different revenue. (A string like "n/a" would not have been summed at all; it would have raised invalid input syntax for type numeric and failed the query.)

Put a constraint in front of it

The cheapest control is a CHECK on the key that matters. Adding it as NOT VALID avoids scanning the existing rows, so it takes only a brief lock and starts protecting new writes immediately:

alter table d_events add constraint amount_is_number
  check (jsonb_typeof(doc->'amount') = 'number') not valid;

insert into d_events(doc) values ('{"type":"purchase","amount":"12","currency":"usd","user_id":1}');
-- ERROR: new row for relation "d_events" violates check constraint "amount_is_number"
insert into d_events(doc) values ('{"type":"purchase","amount":12,"currency":"usd","user_id":1}');
-- INSERT 0 1

The bad row was refused and the good one went in. Validating the constraint against the old rows fails until they are repaired:

alter table d_events validate constraint amount_is_number;
-- ERROR: check constraint "amount_is_number" of relation "d_events" is violated by some row

Repair in three statements, each run in batches in production. Convert the string amounts, drop the null amounts, and rename the stray key:

update d_events set doc = jsonb_set(doc,'{amount}', to_jsonb((doc->>'amount')::numeric))
  where jsonb_typeof(doc->'amount') = 'string';                    -- 500 rows, 99 ms
update d_events set doc = doc - 'amount' where jsonb_typeof(doc->'amount') = 'null';   -- 643 rows
update d_events set doc = (doc - 'userId') || jsonb_build_object('user_id', doc->'userId')
  where doc ? 'userId';                                            -- 500 rows

After those, validate constraint succeeded, and select count(*) from d_events where doc ? 'userId' returned 0. Dropping the key when amount was null is a decision, not a rule: a check constraint passes when its expression is NULL, so a missing amount is accepted. If amount is required, the constraint must say so (doc ? 'amount' and jsonb_typeof(...) = 'number'). Make the same decision about every key you constrain.

Move what matters out

A constraint per key is a sign that the key wants to be a column. When a field is always present, always the same type and always queried, promote it: add a generated column or a real column, and let the type system do the work. The rest of the document stays flexible (extracting properties into typed columns nightly covers one way to do it without a risky migration).

For the keys that stay in the document, keep a schema and check it where the data enters (validating request JSON before it reaches the database), and keep the key-and-type count in a dashboard or a scheduled check. A new row in that output is a deploy you did not know about. Version the shape when you change it on purpose (versioning JSON payloads). The functions used here are described in the PostgreSQL JSON functions page, and the constraint behavior in the ALTER TABLE reference. To inspect a single bad document, the JSON formatter is quicker than SQL.

When drift is acceptable

The data is an archive. Raw webhook payloads kept for replay should be stored as they arrived. Normalize on the way out, not on the way in.

Each producer is a separate tenant. A column that stores customer-defined fields will have a different shape per customer, by design. Constrain the envelope (type, tenant) and leave the content alone.

Readers are tolerant. If every consumer treats missing and null the same, ignores unknown keys, and accepts a number or a numeric string, the drift is harmless. That is a property you have to write down and test, not one you can assume.

The table is small and temporary. A staging table does not need a constraint. The counting queries are still worth a minute when something looks wrong.