postgres-syntax-jsonb

Quick Reference :

PostgreSQL has TWO JSON column types : json (stores the input text verbatim, reparses on every read, cannot be GIN-indexed for containment) and jsonb (stores a decomposed binary form, single parse on insert, supports GIN, B-tree, hash, subscript update). ALWAYS use jsonb unless lossless round-tripping of key order, whitespace, and duplicate keys is a documented requirement (it almost never is). The dominant operator set is -> and ->> for object/array field access (returns jsonb or text respectively), #> and #>> for nested path access, and @> for containment ("does the left payload contain the right one?"). The dominant index is a GIN index ; the choice of opclass between default jsonb_ops and jsonb_path_ops is the highest-leverage performance decision : jsonb_path_ops is 3-5x smaller and faster for pure @> containment queries but DOES NOT support the key-existence operators ?, ?|, ?&.

For value extraction inside SELECT/WHERE, ->> (text) is what callers usually want : WHERE doc->>'status' = 'paid'. This is NOT GIN-indexable as-is : if the same predicate runs a lot, add a B-tree expression index on ((doc->>'status')). For schemaless filtering by shape, WHERE doc @> '{"status":"paid"}' is the GIN-friendly form. v12 added jsonpath (operators @? and @@, functions jsonb_path_query and jsonb_path_match). v17 added JSON_TABLE for relational projection from JSON arrays and JSON_EXISTS / JSON_QUERY / JSON_VALUE SQL/JSON functions.

When To Use This Skill :

ALWAYS use this skill when :

Choosing between json and jsonb column types
Writing queries that extract values from JSON columns (->, ->>, #>, #>>)
Filtering by JSON structure (@> containment, ? key existence, ?| / ?& set existence)
Picking a GIN opclass for a JSONB column (jsonb_ops vs jsonb_path_ops)
Updating a JSONB column in place (jsonb_set, jsonb_insert, subscript update v14+)
Using jsonpath to express complex queries (@?, @@, jsonb_path_query, jsonb_path_match)
Projecting JSON arrays to relational rows (JSON_TABLE, v17+)