JSON in Databases — PostgreSQL JSONB, MySQL JSON, and MongoDB

Most production databases now let you store JSON directly in a column and query into it — no separate document store required. But "supports JSON" means very different things depending on the engine: PostgreSQL's JSONB is a fully indexed binary format with a rich operator set, MySQL's JSON type is more limited, and MongoDB treats JSON-like documents (BSON) as its native storage model rather than an add-on. Here's how the three actually compare, with working query examples.

PostgreSQL: JSON vs JSONB

PostgreSQL ships two JSON column types. json stores an exact text copy of the input, preserving whitespace and key order, and re-parses on every read. jsonb stores a decomposed binary representation — it's slightly slower to write (it has to parse and normalize on insert), but significantly faster to query, and it's the type you should default to unless you have a specific reason to keep the original text verbatim.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
('Wireless Mouse', '{"color": "black", "wireless": true, "price": 29.99, "tags": ["electronics", "peripheral"]}');

PostgreSQL's JSONB operators are what make it genuinely pleasant to query:

-- -> returns a JSON value; ->> returns text
SELECT attributes->'color' FROM products;   -- "black" (jsonb)
SELECT attributes->>'color' FROM products;  -- black (text)

-- @> containment: does the column contain this JSON fragment?
SELECT * FROM products WHERE attributes @> '{"wireless": true}';

-- ? key existence
SELECT * FROM products WHERE attributes ? 'tags';

-- #>> path extraction, for nested values
SELECT attributes #>> '{specs,weight}' FROM products;

-- jsonb_array_elements to unnest an array field into rows
SELECT jsonb_array_elements_text(attributes->'tags') FROM products;

Crucially, JSONB columns can be indexed with a GIN index, which makes containment (@>) and key-existence (?) queries fast even on millions of rows:

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

This is the single biggest reason teams reach for PostgreSQL JSONB over MySQL JSON when the workload involves filtering on JSON contents at scale — without a GIN index, JSON queries in either engine degrade to a full table scan.

MySQL: The JSON Column Type

MySQL 5.7+ has a native JSON type that validates input and stores it in an optimized binary form (comparable in spirit to JSONB, though with a narrower operator set):

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES
('Wireless Mouse', '{"color": "black", "wireless": true, "price": 29.99, "tags": ["electronics", "peripheral"]}');

Querying uses path expressions with dedicated functions rather than PostgreSQL-style operators:

-- JSON_EXTRACT (or the -> shorthand) to pull a value
SELECT JSON_EXTRACT(attributes, '$.color') FROM products;
SELECT attributes->'$.color' FROM products;   -- shorthand, same result
SELECT attributes->>'$.color' FROM products;  -- unquoted text result

-- JSON_CONTAINS for containment checks
SELECT * FROM products WHERE JSON_CONTAINS(attributes, 'true', '$.wireless');

-- JSON_TABLE (MySQL 8.0+) to expand a JSON array into rows
SELECT jt.tag
FROM products, JSON_TABLE(attributes, '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')) AS jt;

MySQL supports generated columns as a workaround for indexing a value buried inside JSON — you extract the value into a virtual column, then index that column directly:

ALTER TABLE products
ADD COLUMN wireless_flag BOOLEAN
GENERATED ALWAYS AS (JSON_EXTRACT(attributes, '$.wireless')) VIRTUAL,
ADD INDEX idx_wireless (wireless_flag);

This gets you indexed lookups on specific JSON fields, but it's more manual than PostgreSQL's GIN index over the whole document — you need to know in advance which paths you'll query frequently.

MongoDB: JSON as the Native Model

MongoDB doesn't bolt JSON onto a relational table — documents (technically BSON, a binary superset of JSON with extra types like native dates and integers) are the fundamental storage unit:

db.products.insertOne({
  name: "Wireless Mouse",
  attributes: {
    color: "black",
    wireless: true,
    price: 29.99,
    tags: ["electronics", "peripheral"]
  }
});

Because the whole document is JSON-shaped, queries read naturally without path-extraction functions:

// Find by a nested field directly
db.products.find({ "attributes.wireless": true });

// Find where a tags array contains a value
db.products.find({ "attributes.tags": "electronics" });

// Range query on a nested numeric field
db.products.find({ "attributes.price": { $lt: 50 } });

// Aggregation pipeline to unwind an array field
db.products.aggregate([
  { $unwind: "$attributes.tags" },
  { $group: { _id: "$attributes.tags", count: { $sum: 1 } } }
]);

Indexing nested fields is direct and doesn't require a generated-column workaround:

db.products.createIndex({ "attributes.wireless": 1 });
db.products.createIndex({ "attributes.tags": 1 }); // multikey index for array field

Choosing the Right Fit

• PostgreSQL JSONB — best when most of your data is relational and structured, but a subset of columns genuinely vary in shape (product attributes, user preferences, webhook payloads). You get ACID transactions, joins, and JSON flexibility in the same engine.
• MySQL JSON — a reasonable choice if you're already committed to MySQL and need occasional semi-structured storage, but expect to lean on generated columns for any field you query frequently at scale.
• MongoDB — the right fit when the majority of your data is naturally document-shaped and doesn't fit a fixed relational schema well — content management, catalogs with wildly varying product attributes, event/activity logs.

A common anti-pattern worth naming: using a JSON/JSONB column as a way to avoid schema design entirely. If every row's JSON blob ends up needing the same five keys, those are columns, not JSON — a JSON column earns its place when the shape of the data genuinely varies row to row.

Summary

All three databases can store and query JSON, but the depth of support differs meaningfully. PostgreSQL JSONB offers the richest operator set and best indexing story for a relational database with JSON columns. MySQL JSON is functional but leans on generated columns for indexed lookups. MongoDB treats JSON-shaped documents as the native model, which is the right call when most of your schema is naturally semi-structured rather than the exception. Match the tool to how much of your data actually varies in shape, not to which JSON type sounds most modern.

Preparing a JSON payload to insert into one of these column types? JSON Formatter Hub validates and formats it entirely in your browser — free, no uploads.