Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

AdvancedBasic SELECT

Querying JSONB properties

The query

SQL
SELECT 
  user_id,
  settings->>'theme' AS theme_name,
  settings#>'{notifications,email}' AS email_enabled
FROM user_preferences
WHERE settings->>'theme' = 'dark';
Tested against PostgreSQL 16

Note

“->> returns value as text. #> gets JSON object at specified path. PostgreSQL handles NoSQL-like documents beautifully.