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

Why Store JSON in a Database?

Relational databases excel at structured, predictable data. But real-world applications often deal with data that doesn't fit neatly into fixed columns:

• Semi-structured data — user preferences, feature flags, or form submissions where fields vary per record.
• Flexible schemas — product attributes in an e-commerce system where a laptop has "ram" and "cpu" but a shirt has "size" and "color".
• API response caching — storing third-party API responses verbatim for later processing without designing a table for every possible response shape.
• Event logging — audit trails and analytics events with varying payloads.
• Configuration storage — application settings that change structure over time without requiring schema migrations.

JSON columns give you the flexibility of a document store while keeping the transactional guarantees, joins, and query capabilities of a relational database.

PostgreSQL JSON Support

PostgreSQL has the most mature JSON support of any relational database. It offers two distinct types: json and jsonb.

json vs jsonb: When to Use Each

Rule of thumb: use jsonb almost always. The only reason to use json is if you need to preserve exact formatting, key order, or duplicate keys — which is rare.

Creating Tables with jsonb Columns

-- Create a products table with a jsonb attributes column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    price DECIMAL(10, 2),
    attributes JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert products with different attribute shapes
INSERT INTO products (name, category, price, attributes) VALUES
('MacBook Pro 16"', 'laptops', 2499.00, '{
    "cpu": "M3 Pro",
    "ram_gb": 18,
    "storage_gb": 512,
    "ports": ["USB-C", "HDMI", "MagSafe"],
    "color": "Space Black"
}'),
('Cotton T-Shirt', 'clothing', 29.99, '{
    "size": "L",
    "color": "Navy",
    "material": "100% Cotton",
    "care": ["Machine wash cold", "Tumble dry low"]
}');

Querying JSON in PostgreSQL

PostgreSQL provides several operators for accessing JSON data:

-- -> returns a JSON value (still jsonb type)
SELECT attributes->'cpu' FROM products;
-- Returns: "M3 Pro" (with quotes — it's a JSON string)

-- ->> returns a text value (cast to text)
SELECT attributes->>'cpu' FROM products;
-- Returns: M3 Pro (no quotes — plain text)

-- Navigate nested paths with #> and #>>
SELECT attributes#>'{ports,0}' FROM products;
-- Returns: "USB-C"

-- Filter: find products with a specific attribute value
SELECT name, price
FROM products
WHERE attributes->>'color' = 'Space Black';

-- Filter: find products where ram_gb > 16
SELECT name, price
FROM products
WHERE (attributes->>'ram_gb')::int > 16;

Containment and Existence Operators

-- @> containment: does the jsonb contain this subset?
-- Find all products that have USB-C in their ports array
SELECT name FROM products
WHERE attributes @> '{"ports": ["USB-C"]}';

-- ? existence: does the key exist at the top level?
SELECT name FROM products
WHERE attributes ? 'cpu';

-- ?| any of these keys exist
SELECT name FROM products
WHERE attributes ?| array['cpu', 'ram_gb'];

-- ?& all of these keys exist
SELECT name FROM products
WHERE attributes ?& array['cpu', 'ram_gb', 'storage_gb'];

jsonb_path_query (SQL/JSON Path — PostgreSQL 12+)

-- SQL/JSON path expressions for complex queries
-- Find all port values across all products
SELECT jsonb_path_query(attributes, '$.ports[*]')
FROM products;

-- Find products where any port matches a pattern
SELECT name FROM products
WHERE jsonb_path_exists(attributes, '$.ports[*] ? (@ == "HDMI")');

-- Extract with a filter
SELECT name, jsonb_path_query_first(attributes, '$.ram_gb') AS ram
FROM products
WHERE jsonb_path_exists(attributes, '$.ram_gb ? (@ >= 16)');

GIN Indexes for jsonb

-- Default GIN index: supports @>, ?, ?|, ?& operators
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- GIN with jsonb_path_ops: smaller index, only supports @>
-- Use this if you only need containment queries
CREATE INDEX idx_products_attrs_path ON products
    USING GIN (attributes jsonb_path_ops);

-- Expression index on a specific key (great for frequent filters)
CREATE INDEX idx_products_color ON products
    USING BTREE ((attributes->>'color'));

-- Now this query uses the btree index:
SELECT * FROM products WHERE attributes->>'color' = 'Navy';

Updating jsonb Data

-- Set a single key (creates it if missing, overwrites if present)
UPDATE products
SET attributes = jsonb_set(attributes, '{ram_gb}', '36')
WHERE name = 'MacBook Pro 16"';

-- Add a new nested key
UPDATE products
SET attributes = jsonb_set(attributes, '{display}', '"16.2 inch Retina"')
WHERE name = 'MacBook Pro 16"';

-- Remove a key
UPDATE products
SET attributes = attributes - 'color'
WHERE name = 'MacBook Pro 16"';

-- Append to an array
UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{ports}',
    (attributes->'ports') || '"SD Card"'::jsonb
)
WHERE name = 'MacBook Pro 16"';

-- Merge entire objects (PostgreSQL 16+ has || merge)
UPDATE products
SET attributes = attributes || '{"weight_kg": 2.14, "battery_hrs": 22}'::jsonb
WHERE name = 'MacBook Pro 16"';

MySQL JSON Support

MySQL 5.7+ supports a native JSON data type. The syntax is different from PostgreSQL, but the concepts are similar.

Creating Tables and Inserting JSON

-- Create a table with a JSON column
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert JSON data
INSERT INTO events (event_type, payload) VALUES
('page_view', '{"url": "/products", "user_id": 42, "referrer": "google.com"}'),
('purchase', '{"order_id": 1001, "items": [{"sku": "ABC", "qty": 2}, {"sku": "XYZ", "qty": 1}], "total": 89.97}');

Querying JSON with JSON_EXTRACT and Shorthand

-- JSON_EXTRACT returns a JSON value
SELECT JSON_EXTRACT(payload, '$.user_id') FROM events;
-- Returns: 42

-- Shorthand: column->path (equivalent to JSON_EXTRACT)
SELECT payload->'$.user_id' FROM events;

-- Unquote: column->>path (returns text, like ->> in PostgreSQL)
SELECT payload->>'$.url' FROM events;
-- Returns: /products (no quotes)

-- Access array elements
SELECT payload->'$.items[0].sku' FROM events
WHERE event_type = 'purchase';
-- Returns: "ABC"

-- Filter by JSON value
SELECT * FROM events
WHERE payload->>'$.url' = '/products';

-- Check if a key exists
SELECT * FROM events
WHERE JSON_CONTAINS_PATH(payload, 'one', '$.referrer');

JSON_SET, JSON_INSERT, and JSON_REPLACE

-- JSON_SET: set value (insert or replace)
UPDATE events
SET payload = JSON_SET(payload, '$.browser', 'Chrome')
WHERE id = 1;

-- JSON_INSERT: only insert if the key does NOT exist
UPDATE events
SET payload = JSON_INSERT(payload, '$.user_id', 99)
WHERE id = 1;
-- user_id already exists, so this does nothing

-- JSON_REPLACE: only update if the key DOES exist
UPDATE events
SET payload = JSON_REPLACE(payload, '$.user_id', 43)
WHERE id = 1;

-- JSON_REMOVE: delete a key
UPDATE events
SET payload = JSON_REMOVE(payload, '$.referrer')
WHERE id = 1;

-- JSON_ARRAY_APPEND: add to an array
UPDATE events
SET payload = JSON_ARRAY_APPEND(payload, '$.items', '{"sku": "DEF", "qty": 3}')
WHERE event_type = 'purchase';

JSON_SEARCH and JSON_CONTAINS

-- JSON_SEARCH: find the path to a value (string search only)
SELECT JSON_SEARCH(payload, 'one', 'ABC') FROM events;
-- Returns: "$.items[0].sku"

-- JSON_CONTAINS: check if a JSON document contains a value
SELECT * FROM events
WHERE JSON_CONTAINS(payload, '{"sku": "ABC"}', '$.items');

Virtual Generated Columns for Indexing

-- MySQL can't index JSON directly, but you can create
-- virtual generated columns and index those
ALTER TABLE events
ADD COLUMN event_url VARCHAR(255)
    GENERATED ALWAYS AS (payload->>'$.url') VIRTUAL;

CREATE INDEX idx_event_url ON events (event_url);

-- Now this query uses the index:
SELECT * FROM events WHERE event_url = '/products';

SQLite JSON Support

SQLite added JSON functions in version 3.9.0 (2015). JSON is stored as plain text but can be queried with built-in functions.

-- json_extract: pull values from a JSON string
SELECT json_extract('{"name": "Alice", "age": 30}', '$.name');
-- Returns: Alice

-- Use with table columns
CREATE TABLE settings (
    id INTEGER PRIMARY KEY,
    config TEXT NOT NULL  -- JSON stored as text
);

INSERT INTO settings (config) VALUES ('{"theme": "dark", "lang": "en", "notifications": {"email": true, "push": false}}');

-- Query nested values
SELECT json_extract(config, '$.notifications.email') FROM settings;
-- Returns: 1 (SQLite uses 1/0 for booleans)

-- json_each: iterate over array elements
SELECT value FROM settings, json_each(json_extract(config, '$.notifications'))
WHERE json_each.key = 'email';

json_each and json_tree

-- json_each: iterate over top-level elements of an array or object
-- Useful for unnesting JSON arrays into rows
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    data TEXT NOT NULL
);

INSERT INTO orders (data) VALUES ('{"items": ["apple", "banana", "cherry"]}');

-- Expand array into individual rows
SELECT value
FROM orders, json_each(json_extract(data, '$.items'));
-- Returns:
-- apple
-- banana
-- cherry

-- json_tree: recursively walk the entire JSON structure
SELECT key, value, type, path
FROM orders, json_tree(data)
WHERE type = 'text';

MongoDB: Native JSON Document Storage

MongoDB stores data as BSON (Binary JSON) documents natively, which makes it fundamentally different from relational databases that bolt on JSON support. Here is a brief comparison:

// MongoDB query examples for comparison
// Find documents where status is "active"
db.users.find({ status: "active" })

// Query nested fields with dot notation
db.users.find({ "address.city": "New York" })

// Query array elements
db.products.find({ tags: { $in: ["electronics", "sale"] } })

// Aggregation pipeline
db.orders.aggregate([
    { $match: { status: "completed" } },
    { $group: { _id: "$customer_id", total: { $sum: "$amount" } } },
    { $sort: { total: -1 } }
])

If your entire data model is document-oriented, MongoDB may be the better fit. If you have mostly relational data with some flexible fields, a JSON column in PostgreSQL or MySQL is typically the right choice.

Best Practices for JSON in Databases

When to Use JSON Columns vs Normalized Tables

• Use JSON columns when: the data structure varies per row, you rarely query individual fields, the data comes from external APIs, or the schema changes frequently.
• Use normalized tables when: you need referential integrity (foreign keys), you frequently filter or join on specific fields, the structure is consistent across rows, or you need aggregate queries on specific values.

Indexing Strategies

Without indexes, querying JSON columns requires a full table scan and parsing every row. Choose your indexing strategy based on query patterns:

• GIN index (PostgreSQL) — best for containment queries (@>) and key existence checks. Supports ad-hoc queries on any key.
• Expression/functional index — best when you frequently filter on a specific JSON key. Smaller and faster than GIN for targeted queries.
• Virtual generated column (MySQL) — extracts a JSON value into a virtual column that you can index with a standard B-tree index.

Validate JSON Before Inserting

PostgreSQL and MySQL will reject malformed JSON on insert, but they do not validate the structure or semantics of the data. Use JSON Schema to define what your JSON columns should contain and validate at the application layer before inserting.

-- PostgreSQL: add a CHECK constraint for basic structure validation
ALTER TABLE products
ADD CONSTRAINT valid_attributes CHECK (
    attributes ? 'color'
    AND jsonb_typeof(attributes->'color') = 'string'
);

-- For comprehensive validation, define a JSON Schema
-- and validate in your application code before INSERT

Performance Considerations

• Keep JSON documents small. Large JSON blobs (hundreds of KB) slow down reads because the entire value must be fetched even if you only need one field. PostgreSQL does support TOAST compression for large values, but it is not free.
• Avoid deeply nested structures. More than 3-4 levels of nesting makes queries complex and hard to index efficiently.
• Extract hot fields into regular columns. If you find yourself repeatedly querying the same JSON path, promote that field to a dedicated column with a proper index.
• Use jsonb over json in PostgreSQL. The binary format is faster for reads and supports indexing. The text-based json type re-parses on every access.
• Monitor query plans. Use EXPLAIN ANALYZE to verify that your queries are using indexes and not doing sequential scans.

Common Pitfalls

Over-Using JSON Columns

The biggest mistake is treating a relational database like a document store. If every table has a catch-all data JSONB column, you lose the benefits of relational modeling: type safety, foreign keys, and query optimization. A good guideline is that if a field appears in more than 80% of rows with a consistent type, it should be a regular column.

Querying Performance Traps

-- BAD: casting inside a WHERE clause prevents index use
SELECT * FROM products
WHERE (attributes->>'price')::numeric > 100;
-- This requires scanning and casting every row

-- BETTER: create a functional index on the cast expression
CREATE INDEX idx_products_price ON products
    USING BTREE (((attributes->>'price')::numeric));

-- Or even better: if you query price often, make it a real column
ALTER TABLE products ADD COLUMN price NUMERIC(10,2);

Lack of Referential Integrity

JSON columns cannot have foreign key constraints. If your JSON contains an author_id that references a users table, the database will not enforce that relationship. The referenced user could be deleted while the JSON still points to them. This must be handled in application code.

Schema Drift

Without validation, JSON columns tend to accumulate inconsistencies over time. One row has "color": "red", another has "colour": "blue", and a third has "color": null. Establish a clear schema, validate on insert, and write migration scripts when the schema needs to change.

Ignoring NULL vs Missing Key vs JSON null

-- These three are DIFFERENT in PostgreSQL:

-- 1. SQL NULL: the entire column is null
SELECT * FROM products WHERE attributes IS NULL;

-- 2. Key missing: the jsonb object doesn't have the key
SELECT * FROM products WHERE NOT (attributes ? 'color');

-- 3. JSON null: the key exists but the value is null
SELECT * FROM products WHERE attributes->>'color' IS NULL
    AND attributes ? 'color';

-- Be explicit about which case you're checking for

Quick Reference: JSON Operations Across Databases