PostgreSQL's JSONB support goes far beyond basic storage. You can build full document-store patterns — schemas that rival MongoDB and DynamoDB — while retaining SQL joins, transactions, and constraints. JSONB Functions Function Purpose jsonbbuildobject(k,v,...) Build JSONB from key-value pairs jsonbbuildarray(v,...) Build JSONB array jsonbeach(jsonb) Expand top-level keys to rows jsonbarrayelements(jsonb) Expand array to rows jsonbtorecord(jsonb) Convert JSONB to a named record jsonbtypeof(jsonb) Returns JSON type as text jsonbstripnulls(jsonb) Remove null-valued keys jsonbpathquery(jsonb, path) SQL/JSON path query jsonbpretty(jsonb) Pretty-print JSON SQL/JSON Path Language (PostgreSQL 12+) PostgreSQL implements the SQL/JSON path language standard, enabling XPath-like traversal of JSON…
Key Takeaways
SQL/JSON path language provides XPath-like queries inside PostgreSQL.
Generated columns extract JSONB fields into real, indexable columns.
The document-store pattern combines NoSQL flexibility with SQL power.
Use jsonbpathquery for complex filtering, arithmetic, and regex in JSON.