In PostgreSQL, how do jsonpath queries through jsonb_path_query, @? and @@ work, and what changes between lax and strict mode?
answer
- $ is the document, @ the item
- filters with variables from vars
- set-returning versus boolean forms
- lax unwraps arrays, hides errors
- strict raises structural errors
basics
~20 sA jsonpath such as $.ratings[*] ? (@.score < 3) selects items from a jsonb document: jsonb_path_query returns them as rows, @? tests whether any exist, and @@ evaluates a predicate. Lax mode, the default, unwraps arrays and hides structural errors; strict raises them.
solid answer
~50 s`jsonpath` is a type holding a parsed SQL/JSON path: `$` is the document, `.key` and `[*]` navigate, and a filter `? (@.score < $min)` keeps the items for which the predicate holds, with `$min` supplied through the `vars` argument as a `jsonb` object instead of being spliced into the string. `jsonb_path_query(answers, path, vars)` returns every match as a row; `jsonb_path_query_first`, `jsonb_path_query_array`, `jsonb_path_exists` and `jsonb_path_match` are the single-value, array and boolean forms. The operators `answers @? '$.ratings[*] ? (@.score < 3)'` (does the path return anything?) and `answers @@ '$.nps < 7'` (the predicate's result) can use a GIN index on the column. In lax mode, the default, a missing key yields no item and arrays are unwrapped; `strict` raises errors such as `JSON object does not contain key`, which the `silent` argument suppresses. Datetime comparisons that need a time zone require the `_tz` variants.
code
sql · 8 lines-- Responses whose nps holds a string, boolean, object or JSON null
SELECT id, answers->'nps' AS bad_nps
FROM form_responses
WHERE answers @? '$.nps ? (@.type() != "number")';
-- Lax versus strict on a missing key
SELECT jsonb_path_query('{"a": {"b": 1}}', 'lax $.a.c'); -- no rows
SELECT jsonb_path_query('{"a": {"b": 1}}', 'strict $.a.c'); -- ERROR: JSON object does not contain key "c"go deeper
Recall that a jsonpath starts at $, uses .key and [*] to navigate, and that @? asks whether a path finds anything in the document.
Explain filters with @ and variables passed through vars, the difference between @? and @@, and what lax mode's unwrapping and error suppression do compared with strict.
Write the operator form so a GIN index can apply, use strict mode or silent deliberately when validating data, and use the _tz variants knowing they depend on TimeZone.
Decide where path logic belongs: every deep jsonpath query encodes an undocumented schema, so keep the document structure documented and versioned as forms evolve.
## The jsonpath type `jsonpath` is a PostgreSQL data type that holds a **parsed SQL/JSON path expression**. A path is written as a string literal and parsed into a binary form. Its building blocks: - `$` is the document being queried (the context item). - `.key` and `."key with spaces"` access members; `[0]`, `[last]` and `[*]` access array elements, counting from zero. - `? (predicate)` is a **filter** that keeps only the items for which the predicate is true; inside it, `@` is the current item. - `$name` is a **variable** whose value comes from the `vars` argument (or `PASSING` in the SQL/JSON functions). - Methods such as `.type()`, `.size()`, `.double()` and `.datetime()`, and the `like_regex` filter. A path such as `$.ratings[*] ? (@.score < $min).field` reads: take every element of `ratings`, keep those whose `score` is below `$min`, return their `field`. ## Functions and operators | Call | Returns | Use | |---|---|---| | `jsonb_path_query(target, path, vars, silent)` | `setof jsonb` | every matching item as a row | | `jsonb_path_query_first(...)` | `jsonb` | the first match, or NULL | | `jsonb_path_query_array(...)` | `jsonb` | all matches as one JSON array | | `jsonb_path_exists(...)` or `target @? path` | `boolean` | does the path return any item? | | `jsonb_path_match(...)` or `target @@ path` | `boolean` | the result of a predicate check | Two kinds of path exist. A standard path such as `$.ratings[*] ? (@.score < 3)` returns items, so it belongs with `@?` and `jsonb_path_exists`. A **predicate check expression** such as `$.nps < 7`, a PostgreSQL extension to the standard, returns a single `true`, `false` or `null`, so it belongs with `@@` and `jsonb_path_match`; `@@` returns NULL when the path result is not a single boolean. The operators, not the functions, are what a GIN index on the column can serve, so write `@?` and `@@` in WHERE clauses. ## Variables instead of string building The `vars` argument is a `jsonb` object whose fields become path variables. The threshold below is data, never part of the path text, so a user-supplied value cannot change the path's structure, for the same reason SQL uses bind parameters: ```sql SELECT r.id, jsonb_path_query_array(r.answers, '$.ratings[*] ? (@.score < $min).field', jsonb_build_object('min', 3)) AS low_fields FROM form_responses r WHERE r.form_id = 42 AND r.answers @? '$.ratings[*] ? (@.score < 3)'; ``` ## Lax and strict mode Every path runs in one of two modes, chosen by a `lax` or `strict` prefix; **lax is the default**. 1. **Lax** adapts the path to the data: a missing key produces no item instead of an error, a member accessor applied to an array is applied to each element (automatic unwrapping), and comparisons unwrap arrays. A survey whose older submissions lack a field simply yields no match. 2. **Strict** requires the path to match the structure exactly. `strict $.respondent.phone` on a document without `phone` raises `JSON object does not contain key "phone"`, and applying a member accessor to an array raises `jsonpath member accessor can only be applied to an object`. 3. Lax unwrapping has side effects: combined with the `.**` any-depth accessor it can return the same value twice, so the manual recommends `.**` only in strict mode. ## Errors, silent and time zones - The `silent` argument (default false) suppresses the same errors that `@?` and `@@` always suppress: missing fields or elements, unexpected item types, and datetime and numeric errors. - Comparing date/time values that need a time zone conversion, such as a date against a timestamp with time zone, raises `cannot convert value ... without time zone usage` in the plain functions. The `_tz` variants (`jsonb_path_query_tz`, `jsonb_path_exists_tz` and the rest) allow it and are therefore STABLE rather than IMMUTABLE, since the result depends on the `TimeZone` setting. - String literals inside a path use double quotes: `$.plan ? (@ == "pro")`. ## Where jsonpath fits - `->` and `->>` read one known key; `@>` matches a fixed shape; **jsonpath** handles conditions over array elements, ranges, regular expressions and computed comparisons that containment cannot express, such as any rating below 3. - The SQL/JSON functions `JSON_EXISTS`, `JSON_QUERY`, `JSON_VALUE` and `JSON_TABLE` take the same path language with their own error-handling clauses.
- Why can WHERE jsonb_path_exists(answers, '$.ratings[*] ? (@.score < 3)') ignore a GIN index that answers @? '...' would use?An index is matched to an indexable operator applied to the indexed column. The GIN operator classes for `jsonb` list `@?` and `@@`, but `jsonb_path_exists` is a plain function call the planner cannot map to that operator. Write the operator form in WHERE clauses; keep the functions for SELECT lists or when you need `vars` and `silent`.
- What is the difference between @? and @@ when given the same predicate?`@?` asks whether a standard path returns any item, so it fits `$.ratings[*] ? (@.score < 3)`. `@@` returns the result of a predicate check expression such as `$.nps < 7`, and returns NULL when the path does not produce a single boolean. Feeding a predicate to `@?` is always true, because a predicate always returns a value.
- jsonpath arrays are 1-based like SQL arrays
- strict is the default mode, so missing keys raise errors
- @@ tells you whether a standard path found any items
- Concatenating user input into the path string is as safe as vars
- jsonb_path_exists in a WHERE clause uses a GIN index just like @?