What's the difference between -> and ->>?

In Postgres, -> returns JSON (still typed as json/jsonb). ->> returns text. 'pro' as JSON is the string "pro" including quotes when compared as text. ->> 'plan' = 'pro' is the comparison you wanted.

Does this happen on MySQL and SQL Server?

Same idea, different functions. MySQL JSON_EXTRACT returns quoted JSON; JSON_UNQUOTE(JSON_EXTRACT(...)) is the text. SQL Server JSON_VALUE returns a scalar; JSON_QUERY returns an object. Using the object form in a text compare misses.

Why did describe_table not save me?

It shows the column is jsonb. It does not show the keys inside or which extractor to use. Ask for a sample payload or jsonb_object_keys before filtering.

Is this LIKE wildcards again?

No. LIKE % is over-matching. This is under-matching: the value is there, the types don't compare equal. Opposite smell, same 'filter looks right' family.

Should I cast the JSON to text?

Don't cast the whole object. Extract the key as text (>> or JSON_VALUE), then compare. Casting jsonb to text includes braces and quotes and will not equal 'pro'.

Postgres JSON -> vs ->> Filter | Quotes

You ask for "events where the plan in the payload is pro." The AI writes payload->'plan' = 'pro' and gets zero rows. You can see "plan": "pro" in the JSON. -> returns JSON. 'pro' is text. Those are not equal. The quoted JSON value "pro" is not the string pro.

Extract as text, then compare

Postgres:

-- misses: json/jsonb compared to text
WHERE payload->'plan' = 'pro'

-- hits: text = text
WHERE payload->>'plan' = 'pro'

-> is for walking into an object (payload->'user'->>'email'). ->> is for the leaf you want to filter or display as a string.

MySQL:

-- quoted JSON
JSON_EXTRACT(payload, '$.plan') = 'pro'          -- often misses
JSON_UNQUOTE(JSON_EXTRACT(payload, '$.plan')) = 'pro'
-- or
payload->>'$.plan' = 'pro'                       -- MySQL 5.7.13+

SQL Server: JSON_VALUE(payload, '$.plan') for a scalar; JSON_QUERY for an object. Don't compare JSON_QUERY to 'pro'.

This is the JSON cousin of text id vs integer — the value is there, the type of the comparison is wrong. No error in Postgres for jsonb = text in some shapes; you just get no rows.

How to catch it

SELECT
  payload->'plan'  AS as_json,
  payload->>'plan' AS as_text
FROM events
LIMIT 5;

If as_json prints "pro" (with quotes) and as_text prints pro, your filter used the wrong arrow.

Also confirm the key exists — payload ? 'plan' in Postgres — before assuming the extractor is the bug. Models invent key names the same way they invent columns (schema guessing).

The prompt that heads this off

On JSON/JSONB columns, use -> to walk into objects and ->> (or
JSON_VALUE / JSON_UNQUOTE) for the leaf you filter or display.
Don't compare a JSON value to a text literal. Show a sample
extract of both forms if a filter returns zero rows.

The short version

The data is in the JSON. The filter compared a quoted JSON string to an unquoted one. Change the last arrow to ->> (or the UNQUOTE/VALUE equivalent) before you decide the key is wrong.

Read-only MCP gateway: mcpserver.design.

Related reading: