JSON in SQL databases

In short: Modern SQL databases can store, query and index JSON documents in columns. This mixes fixed tables with flexible data.

In more detail: PostgreSQL (json, jsonb), MySQL (JSON) and SQLite (JSON functions) offer operators to read and filter fields from the document.

In Depth

• PostgreSQL: jsonb stores in binary, is faster to query and indexable (GIN). data->'name' returns JSON, data->>'name' text, data @> '{"type":"a"}' tests containment.
• When sensible: Rarely used or very varied attributes, raw data from interfaces, settings, events with changing fields.
• When not: For data you often filter, join and that needs consistency. Foreign keys, type checks and constraints do not exist in JSON.
• Compromise: Important fields as real columns, the rest as JSON. Generated columns or indexes on JSON expressions speed up queries.
• Pitfalls: Changing one field often rewrites the whole document. Large JSON values slow tables.
• Alternative: A document database, see NoSQL overview.

See also: PostgreSQL indexes