Using JSON in Databases: MySQL, PostgreSQL, and MongoDB

When to Store JSON in a Database

Relational databases store structured data in rigid tables. JSON columns let you store flexible, schema-optional data alongside structured columns — useful for user preferences, event metadata, feature flags, plugin configs, and any data whose shape varies by row.

The tradeoff: JSON columns are harder to query, slower to index, and cannot enforce referential integrity. Use them for data that genuinely varies — not as a shortcut to avoid schema design.

PostgreSQL — jsonb (Recommended)

PostgreSQL has two JSON types: json (stored as text, preserves whitespace) and jsonb (binary, parsed on insert, indexable). Always use jsonb.

Creating a table with a jsonb column:

CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  metadata    JSONB
);

Inserting JSON:

INSERT INTO users (email, metadata) VALUES (
  'ravi@example.com',
  '{"role": "admin", "preferences": {"theme": "dark", "language": "en"}}'
);

Querying nested fields with ->> (returns text):

SELECT email, metadata->>'role' AS role
FROM users
WHERE metadata->>'role' = 'admin';

Querying deeper nesting:

SELECT email
FROM users
WHERE metadata->'preferences'->>'theme' = 'dark';

Updating a single key without replacing the whole object:

UPDATE users
SET metadata = jsonb_set(metadata, '{preferences,theme}', '"light"')
WHERE email = 'ravi@example.com';

Indexing for fast lookups:

-- GIN index enables fast containment queries (@>)
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

-- Query using containment — uses the GIN index
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

MySQL — JSON Column

MySQL added native JSON support in 5.7. Use JSON_EXTRACT() or the -> shorthand to query fields.

Creating and inserting:

CREATE TABLE events (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  payload   JSON NOT NULL
);

INSERT INTO events (payload) VALUES (
  '{"type": "page_view", "url": "/pricing", "userId": "usr_123"}'
);

Querying with -> and ->> operators:

-- -> returns JSON value; ->> returns unquoted string
SELECT payload->>'$.type' AS event_type
FROM events
WHERE payload->>'$.type' = 'page_view';

Generated columns for indexing JSON fields:

MySQL cannot index JSON columns directly, but you can create a generated column and index that:

ALTER TABLE events
  ADD COLUMN event_type VARCHAR(50)
  GENERATED ALWAYS AS (payload->>'$.type') STORED;

CREATE INDEX idx_event_type ON events (event_type);

MongoDB — Native JSON Documents

MongoDB stores data as BSON (Binary JSON). Every document is a JSON object. There are no separate "JSON columns" — the entire document is JSON.

Inserting a document:

db.users.insertOne({
  email: "ravi@example.com",
  role: "admin",
  preferences: { theme: "dark", language: "en" },
  createdAt: new Date()
});

Querying nested fields:

db.users.find({ "preferences.theme": "dark" });

Updating a nested field:

db.users.updateOne(
  { email: "ravi@example.com" },
  { $set: { "preferences.theme": "light" } }
);

Indexing nested fields:

db.users.createIndex({ "preferences.theme": 1 });

Comparison

| Feature | PostgreSQL jsonb | MySQL JSON | MongoDB | |---|---|---|---| | Indexing | GIN index (powerful) | Generated columns | Native field indexes | | Query language | SQL + operators | SQL + JSON_EXTRACT | MongoDB query language | | Schema validation | JSON Schema (PostgreSQL 16+) | None native | Schema validation rules | | Best for | Hybrid structured + flexible | Occasional JSON, mostly SQL | Fully document-oriented |

When NOT to Use JSON Columns

• Relationships: Foreign keys cannot point into a JSON field. Use a proper join table.
• Aggregations: SUM, COUNT, and GROUP BY on JSON fields require extraction — slow and unindexed by default.
• Frequently queried fields: If you query a JSON field in every request, it belongs as a proper column with a B-tree index.
• Required fields: JSON columns cannot enforce NOT NULL at the field level. Use a CHECK constraint or application validation.

The rule of thumb: if you query the field, it should be a column. If you store it and return it as-is, JSON is fine.

Validate your JSON structure before inserting using JSONKit's JSON Schema Validator — it catches type mismatches and missing required fields before they reach the database.