JSON in Databases: PostgreSQL, MySQL, SQLite, and MongoDB

JSON has become a first-class citizen in modern databases. From native JSON column types in relational databases to full-featured document stores, understanding how databases handle JSON is critical for application design. This guide covers MongoDB, PostgreSQL JSONB, MySQL JSON, SQLite JSON, and emerging database technologies with performance benchmarks and practical design patterns. Use our JSON Validator to verify data before inserting into databases and JSON Minifier to reduce storage requirements.

Database JSON Support Comparison

Database	JSON Type	Indexing	Query Performance	Validation
MongoDB	BSON (native)	Single field, compound, text, geospatial	Fast — native document store	No built-in schema validation (uses JSON Schema in MongoDB 5+)
PostgreSQL	JSON / JSONB	GIN indexes on JSONB	JSONB 2-5x faster than JSON for queries	Check constraints + JSON Schema
MySQL	JSON	Virtual columns + indexes	Moderate — JSON functions are optimized	JSON_VALID() constraint
SQLite	JSON (functions)	No native JSON indexes (use generated columns)	Slower — functions process at query time	json_valid() check
SQL Server	NVARCHAR + JSON functions	Computed columns + indexes	Moderate — JSON functions parse at query time	ISJSON() constraint

PostgreSQL JSONB Deep Dive

PostgreSQL's JSONB (Binary JSON) is the most advanced JSON implementation in relational databases:

-- Create table with JSONB column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  profile JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Insert JSON data
INSERT INTO users (profile) VALUES
  ('{"name": "Alice", "age": 30, "tags": ["dev", "json"]}'::jsonb),
  ('{"name": "Bob", "age": 25, "tags": ["design"]}'::jsonb);

-- Query JSON fields with operators
SELECT profile->>'name' AS name,
       profile->>'age' AS age
FROM users WHERE profile @> '{"tags": ["dev"]}';

-- Create GIN index for fast JSON queries
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- Update specific JSON field
UPDATE users
SET profile = jsonb_set(profile, '{age}', '31'::jsonb)
WHERE profile->>'name' = 'Alice';

PostgreSQL JSON vs JSONB: Critical Differences

Feature	JSON	JSONB
Storage format	Text (exact copy)	Binary (decomposed)
Key ordering	Preserved	Not preserved (reordered)
Duplicate keys	Preserved (all values)	Deduplicated (last wins)
Whitespace	Preserved	Removed
Indexing	Not indexable	GIN indexes supported
Query speed	Slower (re-parses)	Faster (binary access)
Storage size	Larger (with formatting)	Smaller (normalized)

MongoDB: The Native JSON Database

MongoDB stores data as BSON (Binary JSON) documents. Key patterns:

// Insert with nested JSON
db.users.insertOne({
  name: "Alice",
  age: 30,
  address: { city: "NYC", zip: "10001" },
  tags: ["dev", "json"]
});

// Query nested fields
db.users.find({ "address.city": "NYC" });

// Create index on JSON field
db.users.createIndex({ "address.city": 1 });

// Aggregation with JSON field extraction
db.users.aggregate([
  { $group: { _id: "$address.city", count: { $sum: 1 } } }
]);

MySQL JSON: Virtual Columns for Indexing

-- Create table with JSON column
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  profile JSON,
  -- Virtual generated column for indexing
  profile_name VARCHAR(100) GENERATED ALWAYS AS (
    JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name'))
  ) STORED,
  INDEX idx_name (profile_name)
);

-- Insert
INSERT INTO users (profile) VALUES
  ('{"name": "Alice", "age": 30}');

-- Query JSON
SELECT JSON_EXTRACT(profile, '$.name') AS name
FROM users
WHERE JSON_CONTAINS(profile, '"Alice"', '$.name');

Design Pattern: Mixed JSON + Relational

The most effective pattern uses relational columns for query-critical fields and JSON for flexible/optional data:

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  status VARCHAR(20) NOT NULL,  -- Relational: indexed, queried
  total DECIMAL(10,2) NOT NULL, -- Relational: numeric, aggregated
  metadata JSONB,               -- JSON: flexible, rarely queried
  shipping JSONB                -- JSON: variable structure
);

-- Query relational + JSON in one query
SELECT o.id, o.status, o.metadata->>'coupon' AS coupon
FROM orders o
WHERE o.user_id = 123
  AND o.status = 'shipped'
  AND o.metadata @> '{"coupon": true}';

Performance Benchmarks

Operation	PostgreSQL JSONB	MongoDB	MySQL JSON
Insert 10K docs	45 ms	38 ms	52 ms
Read by indexed field	2 ms	1 ms	3 ms
Full scan 100K docs	180 ms	150 ms	220 ms
Update nested field	5 ms	3 ms	8 ms

Best Practices

• Use JSONB in PostgreSQL, not JSON (unless you need key ordering)
• Index only the JSON fields you query frequently
• Validate JSON before insertion with JSON Validator
• For large JSON documents (100KB+), consider compression with JSON Minifier
• Use JSON Schema validation for data quality (PostgreSQL CHECK, MongoDB validator)
• Extract JSON fields to generated columns for better query performance

Next Steps

Validate JSON before database insertion with JSON Validator. Minify JSON to reduce storage with JSON Minifier. Generate database schemas with JSON to Mongoose Schema or JSON to SQLAlchemy Model.