Your Database MCP Is Lying to You

A developer discovered that database MCP agents can return zero rows for a query without error, making a wrong guess look identical to a correct query on an empty result set. The agent ran `SELECT count() FROM orders WHERE details ->> 'express_delivery' = 'true'` and reported zero orders, but the real answer was 2,317 because the actual key was `fulfillment.shippingV2.expressDelivery`. The developer proposes using recursive CTEs with `jsonb_each`, `jsonb_array_elements`, and `jsonb_typeof` to let the database infer the schema instead of shipping raw rows to the model.

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 https://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.