Here's a conversation I've had more than once.
Me:How many orders use express delivery?
Agent:(runs SELECT count() FROM orders WHERE details ->> 'express_delivery' = 'true')*
Agent:None. No orders in the table use express delivery.
The real answer was 2,317.
The agent didn't hallucinate. It ran a real query against a real database and reported the real result. The query was just wrong — and this is the part that matters — nothing anywhere in the stack was capable of noticing.
Ask your MCP server to describe the table and it tells you the truth, as far as it goes:
column_name | data_type
-------------+-----------
id | uuid
details | jsonb
jsonb
. That's the entire contract. That column could hold {}
in every row or a nine-level fulfillment tree with 400 distinct paths, and information_schema
genuinely cannot tell you which — because as far as Postgres's type system is concerned, jsonb
is one scalar type. The structure lives in the value, not the type. Nobody ever told the catalog.
So the model does the only thing available to it: pattern-matches a plausible key name out of the column name and whatever's floating around the conversation. It writes express_delivery
. The data says:
fulfillment.shippingV2.expressDelivery
Snake case or camel case. Flat or nested. Singular or plural. Three independent coin flips, and it needed to win all three.
If a wrong guess threw an error, none of this would matter. The agent would see the error, adjust, try again. That loop works fine — it's most of what agents do all day.
But ->
on a missing key returns NULL
. NULL = 'true'
evaluates to NULL
. NULL
filters the row out. So a wrong guess returns zero rows, which is a pixel-perfect impostor of "no data matches."
That's the whole failure. Not a bad model, not a bad prompt — a severed feedback loop. Every wrong guess looks identical to a correct query on an empty result set, so the model has no signal to self-correct. It reports its guess as a finding, with the serene confidence of a weather presenter. And then somebody puts the number in a deck.
Worth sitting with: you have exactly the same problem. You just have Slack history and a colleague named Dave to compensate. The agent has neither. (Dave, in my experience, has also left the company.)
The obvious move is to hand the model some rows and let it work the shape out:
SELECT details FROM orders LIMIT 20;
This is worse than it looks. Twenty 40KB documents is tens of thousands of tokens spent on a bad inference — the model sees whichever paths happened to occur in those particular twenty rows, with no frequency information, no type consistency check, and no concept of what it missed. Optional fields look mandatory. Rare fields don't exist at all. And you've burned half your context window getting there.
You're shipping the data to the reasoning. The data is four orders of magnitude bigger than the answer.
Make the database do the aggregation and hand the model the conclusion.
jsonb
isn't text — it's a parsed binary structure, and Postgres can iterate it without re-parsing anything. Three primitives are all you need: jsonb_each
, jsonb_array_elements
, and jsonb_typeof
. Wire them into a recursive CTE and the entire walk runs server-side:
WITH RECURSIVE walk AS (
SELECT s.rid, e.key AS path, e.value, 1 AS depth
FROM sample s, LATERAL jsonb_each(s.details) e
WHERE jsonb_typeof(s.details) = 'object'
UNION ALL
SELECT w.rid, w.path || child.suffix, child.value, w.depth + 1
FROM walk w
CROSS JOIN LATERAL (
SELECT '.' || e.key AS suffix, e.value
FROM jsonb_each(w.value) e WHERE jsonb_typeof(w.value) = 'object'
UNION ALL
SELECT '[]', el.value
FROM jsonb_array_elements(w.value) el WHERE jsonb_typeof(w.value) = 'array'
) child
WHERE w.depth < 8
)
SELECT path,
array_agg(DISTINCT jsonb_typeof(value)) AS types,
count(DISTINCT rid) AS occurrences
FROM walk GROUP BY path;
That CROSS JOIN LATERAL
in the recursive term isn't stylistic. SQL allows exactly one self-reference in a recursive term, and objects and arrays are two different descent rules — unioning them inside a lateral subquery is what makes the whole thing legal.
What comes back is one row per distinct path instead of one row per document:
path | types | occurrences
-----------------------------------------+-----------+-------------
fulfillment.shippingV2.enabled | {boolean} | 973
fulfillment.shippingV2.expressDelivery | {boolean} | 3
lineItems[].sku | {string} | 998
status | {string} | 1000
Four lines. A few hundred tokens. The mystery from the top of this post solves itself on sight.
Three things turn that from clever into practical:
Sample by page, not by row. TABLESAMPLE SYSTEM
— or system_rows
if the contrib extension is installed — reads random pages instead of scanning, so cost scales with your sample rather than your table. ORDER BY random()
is a full scan wearing a trench coat; it reads all 52 million rows to hand you a thousand.
Stop on convergence. Profile in batches, count newly-discovered paths per batch, stop when a few consecutive batches turn up nothing new. Production JSON is emitted by a finite amount of application code, so the discovery curve is steep — most tables plateau by row 300–500.
Report your uncertainty. Sampling will miss the key that appears in 3 rows out of 52 million, and the consumer needs to know that:
"sampling": {
"method": "system_rows",
"rows_scanned": 1500,
"estimated_total_rows": 52000000,
"converged": true,
"missing_mass": 0.004,
"note": "frequency_pct is computed over the sample, not the whole table"
}
missing_mass
there is Good–Turing: singleton paths divided by documents sampled. 0.004
means roughly one document in 250 carries a path you haven't seen — comfortably fine to write queries against. 0.20
means the table is heterogeneous and your map isn't trustworthy yet. A profiler that reports "complete" after touching 0.003% of a table is worse than no profiler at all, because it launders a known unknown into an unknown unknown.
Expose that as an MCP tool and the loop closes:
mcp.NewTool("profile_json_columns",
mcp.WithDescription("Infer json/jsonb structure (key paths, types, frequency, examples); block-samples to convergence."),
mcp.WithString("schema", mcp.Required()),
mcp.WithString("table", mcp.Required()),
mcp.WithString("column"),
)
The model calls it, gets the real path map, and writes details #>> '{fulfillment,shippingV2,expressDelivery}'
on the first attempt instead of the fourth. Database does the aggregation, model gets the conclusion, context window survives.
Profile a column and you get one for free. When a single path comes back as "types": ["number", "string"]
, you have both {"amount": 1999}
and {"amount": "19.99"}
sitting in production — two writers, two conventions, one storing integer cents and one storing dollar strings. A latent cast error stacked on a latent 100x pricing bug, discovered by asking an entirely unrelated question.
Frequency reads as a design review too. A path at 100% isn't optional data; it's a column in witness protection, and ALTER TABLE ... GENERATED ALWAYS AS
will let it out. A path under 1% is usually somebody's abandoned experiment.
Your agent isn't lying on purpose. It's answering from an empty catalog, and jsonb
fails silently, so nothing in the loop can distinguish a wrong query from no data. Fix the catalog problem and the lying stops:
jsonb_each
/ jsonb_array_elements
. Gigabytes stay where they are; kilobytes come back.TABLESAMPLE SYSTEM
makes cost a function of the sample, not the table.The same argument holds with no AI anywhere in the picture, incidentally. This is just what \d+
for jsonb
would look like if Postgres shipped one.
Want the full treatment? I wrote a considerably longer version that goes into the storage layer, why the query planner is equally blind (contsel
returns a hardcoded 0.001 for every containment query, regardless of your data), the four non-obvious details hiding in that recursive CTE, why ctid
isn't unique across partitions and will silently corrupt any dedupe built on it, and the statistics behind convergence:
Implementation and MCP wiring: github.com/rasikraj01/psql-json-profiling-mcp
Either way: go find out what's actually in that column. I promise it's weirder than you think.