JSON in Databases — PostgreSQL JSONB, MongoDB, MySQL & DynamoDB

When to Use JSON Columns

JSON Column Decision Tree

Use JSON Columns For	Use Relational Columns For
User preferences / settings	User email, username, password hash
Feature flags	Foreign keys and relationships
Event metadata / payload	Timestamps, status, amounts
API response caching	Searchable / filterable fields
Schemaless form data	Data with strict validation needs
Third-party webhook payloads	Data used in JOINs

PostgreSQL — JSONB

PostgreSQL's JSONB type is the gold standard for relational databases with JSON support.

Creating a Table

Table with JSONB columnsql

1CREATE TABLE products (
2  id SERIAL PRIMARY KEY,
3  name TEXT NOT NULL,
4  price DECIMAL(10,2) NOT NULL,
5  metadata JSONB DEFAULT '{}'::jsonb,
6  created_at TIMESTAMPTZ DEFAULT now()
7);
8
9INSERT INTO products (name, price, metadata) VALUES
10  ('Laptop', 999.99, '{"brand": "Acme", "specs": {"ram": 16, "storage": "512GB"}, "tags": ["sale", "electronics"]}'),
11  ('Keyboard', 49.99, '{"brand": "TypeCo", "specs": {"layout": "US", "wireless": true}, "tags": ["accessories"]}');

Querying JSONB

PostgreSQL JSONB operatorssql

1-- Extract text value (returns text)
2SELECT name, metadata->>'brand' AS brand FROM products;
3
4-- Extract nested value
5SELECT name, metadata->'specs'->>'ram' AS ram FROM products;
6
7-- Filter by JSON value
8SELECT * FROM products WHERE metadata->>'brand' = 'Acme';
9
10-- Filter by nested value
11SELECT * FROM products WHERE (metadata->'specs'->>'ram')::int >= 16;
12
13-- Check if key exists
14SELECT * FROM products WHERE metadata ? 'brand';
15
16-- Check if array contains value
17SELECT * FROM products WHERE metadata->'tags' ? 'sale';
18
19-- Contains operator (partial match)
20SELECT * FROM products WHERE metadata @> '{"brand": "Acme"}'::jsonb;

Indexing JSONB

GIN and expression indexessql

1-- GIN index: indexes ALL keys and values (general purpose)
2CREATE INDEX idx_products_metadata ON products USING gin (metadata);
3
4-- Expression index: index a specific path (more efficient for targeted queries)
5CREATE INDEX idx_products_brand ON products ((metadata->>'brand'));
6
7-- Partial GIN index on a sub-object
8CREATE INDEX idx_products_tags ON products USING gin ((metadata->'tags'));

JSONB vs JSON

Always use JSONB — not JSON. JSONB is stored in binary format, supports GIN indexes, and is 10-100x faster for queries. The JSON type only stores raw text and cannot be indexed.

MongoDB

MongoDB stores everything as BSON (binary JSON). Every document is a JSON object.

MongoDB CRUD operationsjavascript

1// Insert
2db.products.insertOne({
3  name: "Laptop",
4  price: 999.99,
5  specs: { ram: 16, storage: "512GB" },
6  tags: ["electronics", "sale"]
7});
8
9// Query nested field
10db.products.find({ "specs.ram": { $gte: 16 } });
11
12// Query array contains
13db.products.find({ tags: "sale" });
14
15// Update nested field
16db.products.updateOne(
17  { name: "Laptop" },
18  { $set: { "specs.ram": 32 } }
19);
20
21// Create index on nested field
22db.products.createIndex({ "specs.ram": 1 });

MySQL — JSON Type

MySQL JSON columnsql

1CREATE TABLE orders (
2  id INT AUTO_INCREMENT PRIMARY KEY,
3  customer_id INT NOT NULL,
4  details JSON NOT NULL
5);
6
7-- Insert
8INSERT INTO orders (customer_id, details) VALUES
9  (1, '{"items": [{"name": "Laptop", "qty": 1}], "shipping": "express"}');
10
11-- Query with JSON_EXTRACT
12SELECT id, JSON_EXTRACT(details, '$.shipping') AS shipping FROM orders;
13
14-- Shorthand arrow syntax (MySQL 8+)
15SELECT id, details->>'$.shipping' AS shipping FROM orders;
16
17-- Filter
18SELECT * FROM orders WHERE details->>'$.shipping' = 'express';
19
20-- Generated column for indexing
21ALTER TABLE orders ADD COLUMN shipping VARCHAR(50)
22  GENERATED ALWAYS AS (details->>'$.shipping') STORED;
23CREATE INDEX idx_shipping ON orders (shipping);

DynamoDB

DynamoDB stores items as JSON-like attribute maps. Every item can have different attributes.

DynamoDB JSON documentjavascript

1// DynamoDB stores items in this structure:
2{
3  "PK": { "S": "USER#42" },
4  "SK": { "S": "PROFILE" },
5  "name": { "S": "Alice" },
6  "age": { "N": "28" },
7  "roles": { "L": [{ "S": "admin" }, { "S": "editor" }] },
8  "prefs": {
9    "M": {
10      "theme": { "S": "dark" },
11      "notifications": { "BOOL": true }
12    }
13  }
14}

DynamoDB Marshalling

DynamoDB uses typed attribute maps (S for string, N for number, L for list, M for map). The AWS SDK marshalls/unmarshalls to plain JSON automatically.

Database Comparison

Feature	PostgreSQL JSONB	MongoDB	MySQL JSON	DynamoDB
JSON storage	Binary (JSONB)	Binary (BSON)	Binary (JSON)	Attribute maps
Indexing	GIN + expression	Any path	Generated columns	GSI on attributes
Full-text search	tsvector on JSON	Text index	Limited	No
Schema validation	CHECK constraints	JSON Schema	CHECK (8.0+)	No
JOIN support	✓ Full SQL	✗ ($lookup)	✓ Full SQL	✗
Max document size	No practical limit	16 MB	No practical limit	400 KB
Best for	Hybrid workloads	Document-centric	Existing MySQL apps	Serverless scale

Try These Tools

JSON Flattener

Flatten JSON before inserting into relational columns

Open tool

JSON Path Finder

Find exact paths for database queries

Open tool

JSON Schema Validator

Validate JSON documents before insertion

Open tool

JSON to CSV

Export query results to spreadsheets

Open tool

Try It Yourself

This JSON document would be stored as a PostgreSQL JSONB column. Think about which fields should be relational vs JSON.

Try It Yourself

Which parts go in relational columns? Which stay in JSONB?

Continue Learning

JSON Schema

Define schemas for database validation

Nested JSON

Flatten nested JSON for relational storage

Large JSON Files

Handle large JSON imports into databases

Frequently Asked Questions

Should I use a JSON column or relational columns?

Use relational columns for data you query, filter, or join on frequently. Use JSON columns for flexible/schemaless data, user preferences, metadata, or feature flags that change shape over time.

What is the difference between JSON and JSONB in PostgreSQL?

JSON stores the raw text as-is. JSONB stores a parsed binary representation: it is faster to query, supports indexing (GIN), and removes duplicate keys — but slightly slower to insert.

Can I index JSON fields in PostgreSQL?

Yes. Use GIN indexes on JSONB columns: CREATE INDEX idx ON table USING gin (data). For specific paths, use expression indexes: CREATE INDEX idx ON table ((data->>'email')).

Is MongoDB better than PostgreSQL for JSON?

MongoDB is native document storage — every record is JSON. PostgreSQL has JSONB columns with SQL power. Choose MongoDB for fully schemaless, document-oriented workloads. Choose PostgreSQL for mixed relational + JSON.

How do I migrate from JSON columns to relational columns?

Extract frequently queried JSON fields into dedicated columns using ALTER TABLE ADD COLUMN and UPDATE SET. Keep the JSON column for remaining flexible data. This is a common evolution pattern.