JSONB query builder

The -> operator extracts a JSON value and returns it as jsonb, preserving its type — an object stays an object, an array stays an array, a string stays a JSON string (with quotes). The ->> operator extracts a value and returns it as text, which is what you want when comparing against SQL strings, casting to other types, or displaying results. For example, details -> 'customer' returns the nested object as jsonb so you can chain further operators like -> 'name', while details -> 'customer' ->> 'name' returns the name as a plain text value. A common mistake is using ->> in intermediate steps of a path traversal — details ->> 'customer' -> 'name' fails because ->> returns text, and text does not support the -> operator. Use -> for all intermediate steps and ->> only on the final extraction. For deep paths, the #> and #>> operators are cleaner: details #>> '{customer,name}' is equivalent to details -> 'customer' ->> 'name'. In PostgreSQL 14+, you can also use subscript syntax: details['customer']['name'], which returns jsonb (similar to ->).

The default GIN operator class jsonb_ops creates index entries for every key and value in the JSONB document. It supports containment (@>), existence (?, ?|, ?&), and key/value matching. The alternative jsonb_path_ops creates index entries only for full paths from root to each value, resulting in a significantly smaller index (often 2-3x smaller) and faster containment lookups. However, jsonb_path_ops *only* supports the containment operator @> — it cannot answer existence queries like ? 'key' or ?| array['a','b']. Choose jsonb_path_ops when all your queries use containment patterns like WHERE data @> '{"status": "active"}' and you want the smallest, fastest index. Choose jsonb_ops when you need existence checks or when your query patterns are varied. In many production systems, the best approach is neither — instead, create expression indexes on the specific paths you query most. An expression index like CREATE INDEX ON t ((data ->> 'status')) is far smaller than either GIN variant and supports equality, range queries, and sorting, which GIN indexes cannot do.

There are several approaches depending on what you need. For simple containment checks — "does this array contain this value?" — use the @> operator: WHERE data -> 'tags' @> '["urgent"]'. This is the fastest approach because it uses GIN indexes. For checking if any of several values exist, use the ?| operator: WHERE data -> 'tags' ?| array['urgent', 'critical']. For complex filtering on array elements that are objects, you have two choices. The traditional approach uses jsonb_array_elements: SELECT * FROM orders o, jsonb_array_elements(o.data -> 'items') AS item WHERE (item ->> 'price')::numeric > 100. This unnests the array into rows and filters, but it prevents index usage on the array contents. The modern approach (PostgreSQL 12+) uses jsonpath: WHERE data @? '$.items[*] ? (@.price > 100)'. Jsonpath expressions are more concise and can use GIN indexes in some cases. For aggregation over array elements — such as summing prices — you must use jsonb_array_elements with a lateral join pattern and GROUP BY to collapse results back to one row per document. Always be aware that unnesting large arrays multiplies your row count and can cause performance surprises; check EXPLAIN ANALYZE to verify the planner's estimates.

The json type stores the input text verbatim — it preserves whitespace, key ordering, and duplicate keys. It validates JSON syntax on input but does no further processing. The jsonb type parses the input on write and stores it in a decomposed binary format: whitespace is discarded, key order is normalized, and duplicate keys are deduplicated (last value wins). This difference has major practical implications. Reading individual keys from jsonb is O(log n) because the binary format supports direct key lookup, while json requires reparsing the entire text on every access — O(n). More critically, jsonb supports GIN indexes, containment (@>), existence (?), and jsonpath operators, while json supports almost none of these — you cannot create a GIN index on a json column or use @>. The write cost for jsonb is slightly higher because of the parsing and binary conversion, and it typically uses slightly more storage than json for small documents (due to binary overhead), though for large documents with many keys jsonb can actually be smaller because of deduplication. Use jsonb for virtually all cases. The only reasons to choose json are if you must preserve the exact formatting of the input (such as logging raw API payloads for audit compliance) or if the column is write-heavy and never queried beyond full-document retrieval.