JSON vs JSONB in MySQL vs PostgreSQL: Key Differences and Usage

In PostgreSQL, jsonb stores JSON data in a binary format that is faster to query and index, while json stores it as plain text. MySQL supports only JSON type, which stores JSON in a binary format optimized for quick access but lacks a separate jsonb type like PostgreSQL.

Quick Comparison

This table summarizes the main differences between JSON and JSONB in PostgreSQL and JSON in MySQL.

Feature	PostgreSQL JSON	PostgreSQL JSONB	MySQL JSON
Storage Format	Text (exact JSON text)	Binary (decomposed JSON)	Binary (optimized storage)
Query Performance	Slower, no indexing	Faster, supports indexing	Fast, supports indexing
Supports Indexing	No	Yes (GIN, GiST indexes)	Yes (functional indexes)
Preserves Key Order	Yes	No (keys reordered)	No (keys reordered)
Storage Size	Larger (stores raw text)	Smaller (binary compressed)	Compact binary format
Use Case	When exact JSON text needed	When fast queries and indexing needed	General JSON storage and querying

Key Differences

PostgreSQL offers two JSON types: json and jsonb. The json type stores data as plain text, preserving the exact input including whitespace and key order, but it is slower to query and cannot be indexed efficiently. In contrast, jsonb stores data in a binary format that removes whitespace and reorders keys, which allows faster querying and supports indexing with GIN or GiST indexes.

MySQL provides a single JSON data type that stores JSON in a binary format optimized for quick access and supports indexing through functional indexes. It does not have a separate jsonb type like PostgreSQL. MySQL's JSON type balances storage efficiency and query speed but does not preserve key order.

In summary, jsonb in PostgreSQL is designed for performance and indexing, while json in PostgreSQL is for exact text preservation. MySQL's JSON type is closer to PostgreSQL's jsonb in terms of performance and storage but lacks the dual-type option.

Code Comparison

Here is how you create a table and insert JSON data in MySQL using the JSON type, then query it.

mysql

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  info JSON
);

INSERT INTO products (info) VALUES
('{"name": "Pen", "price": 1.5}'),
('{"name": "Notebook", "price": 3.0}');

SELECT JSON_UNQUOTE(JSON_EXTRACT(info, '$.name')) AS product_name, JSON_UNQUOTE(JSON_EXTRACT(info, '$.price')) AS product_price FROM products WHERE JSON_EXTRACT(info, '$.price') > 2;

Output

product_name | product_price
------------ | -------------
Notebook     | 3.0

PostgreSQL Equivalent

Here is the equivalent PostgreSQL code using jsonb type for better performance and indexing.

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  info jsonb
);

INSERT INTO products (info) VALUES
('{"name": "Pen", "price": 1.5}'),
('{"name": "Notebook", "price": 3.0}');

SELECT info->>'name' AS product_name, info->>'price' AS product_price FROM products WHERE (info->>'price')::numeric > 2;

Output

product_name | product_price
------------ | -------------
Notebook     | 3.0

When to Use Which

Choose PostgreSQL jsonb when you need fast queries, indexing, and efficient storage of JSON data without preserving key order. Use PostgreSQL json if preserving the exact JSON text, including whitespace and key order, is critical.

Choose MySQL JSON for general JSON storage and querying needs, as it offers a good balance of performance and storage but does not differentiate between text and binary JSON types.

Key Takeaways

PostgreSQL jsonb stores JSON in binary for faster queries and indexing, unlike json which stores plain text.

MySQL has a single JSON type that stores JSON in a binary format optimized for performance.

Use PostgreSQL jsonb for performance and indexing; use json for exact text preservation.

MySQL JSON is suitable for most JSON use cases but lacks a separate jsonb type.

Choosing depends on your need for query speed, indexing, and JSON text fidelity.