Even JSONB In Postgres Needs Schemas
summary
Postgres stores JSON efficiently with JsonB but still needs enforced shapes: uses GIN indexes for fast contains queries, jsonb_typeof to assert root types, and jsonb_array_elements plus aggregates like bool_and inside SQL functions to validate every array element. CHECK constraints call immutable validation functions or, if available, the pg_jsonschema extension (Rust) to match a JSON Schema. A generator can emit those SQL validators when extensions aren’t allowed.
AI-written summary · about
chapters (9)
- 0:00Intro
- 2:19Handling complex or unknown data
- 3:58PostgreSQL JSON support
- 9:39Things can go wrong
- 11:52Finding the type
- 13:00Check constraints
- 14:29Structure
- 17:50Functions to the rescue
- 19:57JSON schemas
related talks
49mShould I use JSON in PostgreSQL?
05mPostgreSQL JSON JSONB Making the Right Choice for Your Application
50mUsing JSON with Postgres 9 3
48mPostgres and Json - How to get the best of them?
25mJSON in PostgreSQL - evil data type or just needs to be tamed?
48mThe road to new SQL/JSON features
14mJSON data structure – the hidden gem inside your database engine
35mLeveraging PostgreSQL 17's JSONB for Enhanced Data Modeling