An antipattern is a recurring choice that appears reasonable but creates harmful consequences. In PostgreSQL, mistakes may remain acceptable at small scale yet become costly, risky, or difficult to correct in production. section 1.2
Relational JSON is one PostgreSQL antipattern: storing strongly relational data entirely inside JSON fields. JSON is valuable for unforeseeable attributes and ingestion, but becomes harmful when ordinary relational data requires joins, casts, and application-enforced rules. section 5.3
Recognize the antipattern when:
- A design uses JSON for data that fits a relational pattern well. section 5.3
- Queries repeatedly extract JSON values, cast them to scalar types, and join on them. section 5.3
- The database cannot enforce referential integrity or reliably ensure value types inside the stored documents. section 5.3
- A flexible schema creates difficult queries that are easy to write incorrectly or make perform badly. section 5.3
Relational JSON in customer and sales data
Accounts and sales are stored as one jsonb value per row. Retrieving customer names for qualifying sales requires extracting identifiers, casting them, joining JSON fields, comparing numeric values, and inspecting nested migration data. The design maximizes flexibility at the cost of relational safeguards and query simplicity. section 5.3
CREATE TABLE test.accounts (
json_account jsonb NOT NULL
);
CREATE TABLE test.sales (
json_sale jsonb NOT NULL
);
Shows the accounts and sales tables whose complete rows are stored in jsonb columns. section 5.3
SELECT a.json_account ->> 'name' AS "Name"
FROM test.accounts a
JOIN test.sales s
ON (s.json_sale ->> 'account_id')::int = (a.json_account ->> 'id')::int
WHERE (a.json_account ->> 'balance')::numeric < 20000
AND (s.json_sale ->> 'amount')::numeric > 10000
AND (NOT a.json_account['migrated_account'] ->> 'system' = 'legacy'
OR a.json_account -> 'migrated_account' IS NULL);
Shows the extraction, casting, join, filtering, and nested-field checks required by the JSON design. section 5.3
