In PostgreSQL, what do the jsonb operators ->, ->> and @> return, and when should a WHERE clause use @> instead of ->>?
answer
- one keeps the type, one makes text
- missing key gives NULL, not an error
- text comparison of numbers
- containment matches from the top
- ? looks at the top level only
basics
~20 sThe -> operator returns jsonb, ->> returns text, and @> returns a boolean saying whether the left document contains the right one's keys, values and structure. Use @> for type-aware matches on nested values or array members; ->> to read one scalar.
solid answer
~50 s`answers->'plan'` returns `jsonb` (`"pro"`, quotes included) and `answers->>'plan'` returns `text` (`pro`); `#>` and `#>>` do the same along a path such as `'{respondent,country}'`. A missing key or a wrong structure yields SQL NULL, not an error, and `->>` also turns a JSON `null` into SQL NULL. Because `->>` gives text, `answers->>'nps' > '9'` compares strings, so cast first: `(answers->>'nps')::int`. `@>` asks whether the left document contains the right one: `answers @> '{"tags": ["beta"]}'` matches an array member, `'{"nps": 9}'` matches the number 9 but not the string `"9"`, and nesting must be spelled from the top. `?`, `?|` and `?&` test key existence at the top level only. Use `@>` for nested, array or multi-key filters, which a GIN index on the column can serve; use `->>` for one scalar you compare, sort or index by expression.
code
sql · 6 linesSELECT answers->'plan' AS as_jsonb, -- "pro"
answers->>'plan' AS as_text, -- pro
answers #>> '{respondent,country}' AS country, -- DE
answers->>'nps' > '9' AS text_cmp, -- false for 10
(answers->>'nps')::int > 9 AS int_cmp -- true for 10
FROM (VALUES ('{"plan": "pro", "nps": 10, "respondent": {"country": "DE"}}'::jsonb)) AS t(answers);go deeper
Recall that -> keeps jsonb, ->> gives text, and @> tests whether one document contains another. Know that a missing key yields NULL rather than an error.
Explain why text comparison of ->> results misorders numbers, why a value compared with -> needs JSON quotes, and that existence and containment both anchor at the top level.
Match the operator to the index: @> with a GIN index on the column, ->> with a B-tree on that exact expression, and show how a mismatch silently loses the index.
Set team conventions for querying documents: which keys are read as typed scalars, which filters use containment, and how key typos that return NULL get caught in review or tests.
## Extraction operators PostgreSQL offers four extraction operators that work on both `json` and `jsonb`. They differ in what they return: | Operator | Right operand | Returns | Example on `answers` | Result | |---|---|---|---|---| | `->` | key (`text`) or array index (`int`) | `jsonb` | `answers->'plan'` | `"pro"` | | `->>` | key (`text`) or array index (`int`) | `text` | `answers->>'plan'` | `pro` | | `#>` | path (`text[]`) | `jsonb` | `answers #> '{respondent,country}'` | `"DE"` | | `#>>` | path (`text[]`) | `text` | `answers #>> '{respondent,country}'` | `DE` | Array indexes start at zero and negative integers count from the end, so `answers->'tags'->0` is the first tag. `jsonb` also supports subscripting, `answers['respondent']['country']`, which returns `jsonb` like `->`. ## NULL instead of errors The extraction operators do not fail on a document of the wrong shape. A missing key, an index past the end, or a key lookup on an array all return **SQL NULL**. In addition, `->>` and `#>>` turn a JSON `null` into SQL NULL, while `->` returns the `jsonb` value `null`, which is not SQL NULL: `answers->'nps' IS NULL` is false when the document holds `"nps": null`, and true when the key is absent. This tolerance suits a survey-builder whose forms change over time, since old submissions simply lack newer fields. It also hides typos: `answers->>'pln'` returns NULL for every row without complaint. ## Comparing extracted values The return type decides how a comparison behaves: 1. `answers->>'nps' > '9'` compares **text**, so `'10'` sorts before `'9'` and a score of 10 fails the test. Cast first: `(answers->>'nps')::int > 9`. 2. `answers->'plan' = 'pro'` resolves the literal as `jsonb`, and `pro` without JSON quotes is not valid JSON, so the query fails with `invalid input syntax for type json`. Write `answers->'plan' = '"pro"'` or `answers->>'plan' = 'pro'`. 3. `answers->>'respondent'` returns a nested object as JSON text; keep it as `jsonb` with `->` if you will navigate further. ## Containment and existence The `jsonb`-only operator `@>` asks whether the left document **contains** the right one: every key and value on the right must appear on the left, at the same place in the structure. - `answers @> '{"plan": "pro"}'` matches a top-level key with that value. - `answers @> '{"respondent": {"country": "DE"}}'` matches a nested value; `answers @> '{"country": "DE"}'` does not, because containment starts at the top level. - `answers @> '{"tags": ["beta"]}'` matches when `beta` is one of the tags; order and duplicates inside arrays do not matter. - `answers @> '{"nps": 9}'` matches the number 9 but not the string `"9"`, because containment compares JSON types. `<@` is the reverse direction. The existence operators look at the **top level only**: `answers ? 'plan'` is true when `plan` is a top-level key (or a string element of a top-level array); `?|` means any of a `text[]` and `?&` means all of them. `answers ? 'country'` is false when `country` lives inside `respondent`. ## Which one belongs in the WHERE clause - Use **`@>`** to match one or more keys, nested values or array members with their JSON types. One GIN index on the column can serve many different `@>` filters; which GIN operator class to pick is an indexing question of its own. - Use **`->>` with a cast** when you need one scalar for a range test, a sort or a join, such as `(answers->>'nps')::int >= 9`, and support it with a B-tree index on exactly that expression. - A mismatch defeats the index: a GIN index on `answers` does not help `answers->>'plan' = 'pro'`, and an expression index on `(answers->>'plan')` does not help `answers @> '{"plan": "pro"}'`. ```sql -- Pro-plan respondents in Germany who tagged themselves beta SELECT id, (answers->>'nps')::int AS nps FROM form_responses WHERE answers @> '{"plan": "pro", "respondent": {"country": "DE"}, "tags": ["beta"]}' ORDER BY (answers->>'nps')::int DESC; ```
- How do -> and ->> treat a JSON null differently, and why does that matter for IS NULL checks?`->>` returns SQL NULL both for a missing key and for `"nps": null`, so `IS NULL` cannot tell them apart. `->` returns the `jsonb` value `null` for an explicit null and SQL NULL only for a missing key. Use `answers ? 'nps'` to test presence, or `jsonb_typeof(answers->'nps')`, which returns the string `null` for a JSON null and SQL NULL for a missing key.
- Why is one @> filter often preferred over several ->> comparisons combined with AND?A single `@>` with a multi-key document expresses the whole condition, compares JSON types correctly, and one GIN index on the column serves any combination of keys. Chained `->>` comparisons compare text, need a cast for numbers, and each key needs its own expression index to be indexable.
- ->> returns jsonb, so you can keep navigating from its result
- A missing key makes -> raise an error
- answers->>'nps' > '9' compares the scores as numbers
- The ? operator finds a key at any nesting depth
- @> matches a nested pair without spelling out its parent keys