Using JSON Columns in Relational Databases

1. Why JSON columns are not a contradiction to the relational world

JSON columns solve a problem that long counted as an argument for a complete switch to NoSQL: variable, nested data structures within an otherwise stable relational schema. Instead of adding a new column for every additional, rarely needed attribute or building an elaborate entity attribute value model, a JSON column stores these variable parts directly as a structured document in a single cell, while the rest of the table remains normally relational.

The decisive advantage over an actual switch to a document based NoSQL database: JSON columns run within the same transaction as every other column of the table, benefit from the same ACID guarantees, and can be joined with normal SQL joins to other tables. PostgreSQL, MySQL, and SQL Server all natively support JSON columns today, each with its own operators for reading, writing, and indexing the contained structure.

This article shows how to use JSON columns sensibly, what differences exist between JSON and JSONB, how to efficiently query and index them, and where the limits of this approach lie before a dedicated schema or an actual NoSQL switch would be the better choice.

2. JSON versus JSONB: storage format and performance

PostgreSQL offers two data types for JSON columns: json stores the text exactly as entered, including original formatting and key order, but only validates the syntax when writing. jsonb stores the same data in a binary, decomposed format that keeps neither formatting nor duplicate keys, but is significantly faster to query and index because no repeated parsing pass is needed when reading.

For nearly every practical use case, jsonb is the right choice. The only advantage of json is the slightly faster write, because no conversion to the binary format takes place, and the exact preservation of the original formatting, which only matters if you need to return the document byte identical later. MySQL only has one JSON type, which is already stored internally in binary and optimized form, so a distinction like in PostgreSQL does not apply there.

-- JSONB as a column type in PostgreSQL: binary, indexable, fast to query
CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  name         VARCHAR(255) NOT NULL,
  price        NUMERIC(10,2) NOT NULL,
  attributes   JSONB NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO products (product_id, name, price, attributes) VALUES
  (1, 'Running shoe model X', 89.90, '{"size": 42, "color": "black", "weight_grams": 310}'),
  (2, 'Bluetooth headphones', 59.90, '{"battery_hours": 24, "bluetooth_version": "5.3"}');

-- json (text variant) keeps exact formatting but is slower for queries
-- ALTER TABLE products ADD COLUMN legacy_payload JSON;

3. When JSON columns make sense and when they do not

JSON columns fit well for attributes that rarely appear in WHERE conditions with complex range comparisons, differ strongly between rows, or whose structure changes more often than a schema migration would justify. Typical examples are product attributes in a heterogeneous catalog, configuration objects for feature flags, or response data from external APIs that you want to archive but not fully normalize.

JSON columns are unsuited, however, for data that is frequently evaluated with aggregate functions, referenced by foreign key, or must carry strict constraints. A field like customer_id, used in joins, foreign keys, and indexes, always belongs as its own, typed column in the table, never inside a JSON document, because otherwise referential integrity and efficient joins are lost. The rule of thumb: stable, heavily queried core fields remain classic columns, variable additional attributes move into the JSON column.

4. Querying JSON data: operators and path expressions

PostgreSQL offers several operators for JSON columns: -> extracts a field as a JSON value, ->> extracts the same field as text, and #> respectively #>> allow nested paths over an array of keys. The containment operator @> checks whether a JSON document fully contains another JSON fragment, which is particularly useful for filter queries across several attributes, without having to extract each field individually.

-- Extract a field as text using ->>
SELECT name, attributes->>'color' AS color
FROM products
WHERE attributes->>'color' = 'black';

-- Query a nested path using #>>
SELECT name, attributes#>>'{dimensions,height_cm}' AS height
FROM products
WHERE (attributes#>>'{dimensions,height_cm}')::numeric > 20;

-- Containment operator: does attributes contain this sub document?
SELECT name FROM products
WHERE attributes @> '{"color": "black", "size": 42}';

-- Check key existence with the ? operator
SELECT name FROM products WHERE attributes ? 'battery_hours';

5. Indexing JSON columns with GIN and expression indexes

Without an index, a query on JSON columns scans every row completely, which becomes unacceptably slow on large tables. PostgreSQL offers the GIN index for jsonb columns, Generalized Inverted Index, which can access arbitrary key value pairs within the document with accelerated lookups, massively speeding up the @> operator and existence checks with ?. For frequently queried single fields, an expression index on exactly that path is often more efficient than a general GIN index.

-- GIN index for general containment and existence queries
CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes);

-- Expression index for a frequently filtered single field
-- considerably more compact and faster than GIN for exactly this case
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

-- Test: EXPLAIN shows whether the index is actually used
EXPLAIN ANALYZE
SELECT name FROM products WHERE attributes->>'color' = 'black';
-- Expectation: Index Scan instead of Seq Scan at sufficient table size

6. Validation and constraints for JSON content

A common misunderstanding: JSON columns do not automatically mean any structure whatsoever must be allowed. CHECK constraints can ensure that required fields are present, that values match an expected type, or that a document follows a fixed set of allowed keys. That keeps the flexibility of JSON without fully giving up data integrity.

-- CHECK constraint enforcing required fields in the JSON column
ALTER TABLE products ADD CONSTRAINT attributes_has_required_keys
  CHECK (attributes ? 'color' AND attributes ? 'weight_grams');

-- Type check of a single field via jsonb_typeof
ALTER TABLE products ADD CONSTRAINT weight_is_number
  CHECK (jsonb_typeof(attributes->'weight_grams') = 'number');

-- Test: violates both constraints, gets rejected
INSERT INTO products (product_id, name, price, attributes)
VALUES (99, 'Test product', 10.00, '{"color": "red"}');
-- ERROR: new row for relation "products" violates check constraint
-- "attributes_has_required_keys"

7. Migration: from rigid columns to JSON and back

A typical migration starts with several fixed columns for optional attributes that turn out to be too rigid because new, rare attributes keep being added. The transition to JSON columns happens gradually: a new JSONB column is created, existing values are transferred into the new structure via UPDATE, application code is gradually switched over, and only once all read and write paths are migrated are the old columns removed.

The reverse path is just as important: if it turns out that a specific JSON field now appears in practically every row, is heavily filtered, and would benefit from constraints, extracting it into a dedicated, typed column pays off. PostgreSQL allows this directly via a generated column that automatically extracts the value from the JSON while remaining normally indexable.

-- MySQL: extract a frequently queried JSON field as a generated column
-- STORED materializes the value physically and allows a normal index
ALTER TABLE products
  ADD COLUMN color VARCHAR(30)
  GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color'))) STORED,
  ADD INDEX idx_products_color (color);

-- This generated column behaves like a normal column:
-- indexable, referenceable by foreign key, usable in aggregates,
-- but stays automatically in sync with the source JSON field

8. Differences between PostgreSQL, MySQL, and SQL Server

All three major relational databases support JSON columns, with differing depth of functionality. PostgreSQL with jsonb offers the richest operator and index support, including GIN indexes and its own JSON path language since version 12. MySQL 8 offers a native, binary stored JSON type with functions like JSON_EXTRACT, JSON_SET, and generated columns for indexing, but is somewhat less performant than PostgreSQL for complex path queries.

SQL Server instead stores JSON as ordinary NVARCHAR text and offers functions like JSON_VALUE and JSON_QUERY for extraction, without a dedicated binary JSON type. Indexing there happens via computed columns combined with normal indexes, similar to the MySQL approach with generated columns. Whoever works across platforms should know these differences before relying too heavily on JSON columns as a portable solution.

-- MySQL: JSON functions compared directly to PostgreSQL operators
SELECT
  name,
  JSON_EXTRACT(attributes, '$.color') AS color_raw,
  JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color')) AS color_clean
FROM products
WHERE JSON_EXTRACT(attributes, '$.battery_hours') > 20;

-- JSON_SET updates a single field without rewriting the whole
-- document or deserializing it in application code
UPDATE products
SET attributes = JSON_SET(attributes, '$.weight_grams', 305)
WHERE product_id = 1;

9. JSON columns versus separate table versus NoSQL document

The following table compares the three common modeling options for variable attributes and shows when JSON columns are the most pragmatic choice.

For most use cases with moderately variable attributes, JSON columns are the most pragmatic middle ground: the flexibility of NoSQL documents combined with the transaction and join guarantees of the relational database, without having to operate a second system.

10. Summary

JSON columns give relational databases exactly the modeling freedom that used to almost automatically lead to a NoSQL switch. jsonb in PostgreSQL and the native JSON type in MySQL allow nested, variable structures within a single column, with full ACID guarantees and normal joins to other tables. Operators like ->, ->>, and @> make JSON content directly queryable, GIN indexes and generated columns provide performance even on large tables.

The rule of thumb remains decisive: stable, heavily referenced core fields belong as their own columns in the table, variable additional attributes belong in the JSON column. Whoever draws this line consistently, uses CHECK constraints for required fields, and extracts frequently queried fields into generated columns when needed gets the flexibility of semi-structured data with JSON columns, without giving up the guarantees of the relational world.

11. FAQ: Using JSON Columns in Relational Databases