# Your Database MCP Is Lying to You > Source: > Published: 2026-07-25 20:26:41+00:00 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.