JSON is a text-based format for structured values, including objects, arrays, strings, numbers, booleans, and null. PostgreSQL's json and jsonb types make it possible to combine schemaless documents with relational tables, but that flexibility becomes dangerous when JSON replaces ordinary columns without a clear need. section 5.3 section 3.8
A common mistake is putting an entire customer or sale row inside one JSON field. The design looks flexible, especially when attributes are sparse or unpredictable, but PostgreSQL cannot enforce referential integrity or reliably enforce the shape and types of values inside the document. section 5.3
CREATE TABLE test.accounts (
json_account jsonb NOT NULL
);
CREATE TABLE test.sales (
json_sale jsonb NOT NULL
);
The tempting schema stores accounts and sales as single jsonb values, removing ordinary relational columns from the design. section 5.3
The flexibility trap
The sample documents already vary: one account uses an ISO-style migrated_on date while another uses "March 12, 2019"; sale timestamps also use different textual forms. Account and sale identifiers are embedded values rather than constrained keys, so mismatches can survive until query time. section 5.3
The warning sign is needing SQL JOINs to retrieve data that was deliberately stored as schemaless documents. The resulting query must extract fields, cast text to integer or numeric, navigate nested objects, and reproduce relational constraints manually. 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);
This query demonstrates the operational cost: JSON operators, explicit casts, a join through extracted account_id and id values, and nested-field filtering. section 5.3
Use JSON when incoming attributes are genuinely unknown or sparse, such as ingesting data whose fields and populated values cannot be predicted. Keep stable entities, relationships, types, and frequently joined or filtered values relational; otherwise flexibility shifts complexity into every query. section 5.3
