Relational meets semi structured
Modern SQL databases can store JSON in a column and query inside it, blending flexible documents with relational tables. This suits attributes that vary per row, like product specs, without exploding the schema into many nullable columns.
Core operations
- Extraction pulls a value at a path, using operators or functions like JSON EXTRACT or the arrow operators. A path navigates keys and array indexes.
- Typed extraction returns a value as text or number so you can compare or compute with it.
- Existence and containment test whether a key or value is present.
- Construction builds JSON from columns, useful for shaping API responses.
Binary storage matters
Many engines offer a binary JSON type, such as JSONB in Postgres. It parses and stores the document in a decomposed form, so reads skip re parsing and support indexing. Plain text JSON preserves formatting but is slower to query.
Indexing JSON
You can index inside JSON to keep queries fast:
- An expression index on an extracted path speeds equality lookups.
- A generalized inverted index supports containment and key existence searches over whole documents.
Use JSON for genuinely variable data, but promote frequently filtered fields to real columns when they become hot.
Key idea
SQL JSON support lets you store semi structured data and extract, test, and build values by path; binary JSON plus expression or inverted indexes keep those queries fast.