LLM Best Practices

JSONB Path

2 min read

View markdown source

Definition

PostgreSQL stores JSON as jsonb (binary-parsed, key-sorted, duplicate-key-eliminated). JSONB supports three query layers:

  1. Operators: -> (get key as jsonb), ->> (get key as text), #> (path as jsonb), #>> (path as text), @> (containment), ? (key exists).
  2. Containment and existence: data @> '{"status":"active"}' — usable with a GIN index.
  3. jsonpath (SQL:2016): jsonb_path_query(data, '$.items[*] ? (@.price > 100)') — a full path language with filters, arithmetic, and accessor chains.

jsonpath functions:

  • jsonb_path_query(jsonb, path): returns a set of matching values.
  • jsonb_path_exists(jsonb, path): returns boolean; useful in WHERE.
  • jsonb_path_match(jsonb, path): returns a single boolean for a predicate path.

jsonpath syntax:

  • $ is the root node.
  • .key accesses an object field.
  • [n] accesses an array index; [*] selects all elements.
  • ? (predicate) is a filter; e.g., $.orders[*] ? (@.total > 500).
  • @ refers to the current node inside a filter.

GIN indexes on the jsonb column accelerate containment (@>) and key-exists (?) operators but do not directly accelerate jsonpath. For jsonpath queries, a GIN index with jsonb_path_ops operator class can accelerate path-contains patterns.

When it applies

Use containment operators (@>) for simple key-value matching on JSONB; they are GIN-indexable and concise. Use jsonpath when queries navigate nested arrays or need filters on intermediate nodes. Avoid deeply nested JSONB for data that is queried relationally; normalize to columns or tables where selectivity matters.

Example

SELECT id FROM events WHERE data @> '{"type": "click", "page": "/home"}';
 
SELECT id FROM orders
WHERE jsonb_path_exists(data, '$.items[*] ? (@.total > 100)');
 
SELECT jsonb_path_query_array(data, '$.items[*].price')
FROM orders WHERE id = 1;
 
CREATE INDEX idx_events_data ON events USING gin(data jsonb_path_ops);
  • gin-index - the index type for accelerating JSONB containment and path queries.
  • query-plan - use EXPLAIN to confirm GIN is used for JSONB queries.
  • btree-index - B-tree can index individual JSONB scalar fields extracted with ->>.
  • postgres - the Postgres deep-dive.

Citing this term

See JSONB Path (llmbestpractices.com/glossary/jsonb-path).

Graph View

Backlinks