Json vs jsonb in PostgreSQL: Key Differences and Usage

In PostgreSQL, json stores JSON data as plain text preserving formatting, while jsonb stores it in a binary format that is faster to query and index. Use jsonb for better performance and indexing, and json if you need to preserve exact input formatting.

Quick Comparison

This table summarizes the main differences between json and jsonb in PostgreSQL.

Feature	json	jsonb
Storage format	Text, stores exact JSON input	Binary, stores decomposed JSON
Query performance	Slower, supports indexing with expression indexes	Faster, supports indexing
Preserves whitespace and order	Yes	No, normalizes keys and removes whitespace
Supports indexing	No native indexing support	Yes (GIN, GiST indexes)
Insertion speed	Faster for inserts	Slightly slower due to processing
Use case	When exact JSON text is needed	When querying and indexing JSON data

Key Differences

The json data type stores JSON data as plain text exactly as you input it, including spaces and key order. This means it preserves the original formatting but does not support efficient querying or indexing. When you query json data, PostgreSQL must parse the text each time, which can slow down performance.

On the other hand, jsonb stores JSON data in a binary format that breaks down the JSON into a more efficient internal structure. This allows PostgreSQL to query and index the data much faster. However, jsonb removes insignificant whitespace and does not preserve the order of object keys, normalizing the data for speed.

Because of these differences, jsonb supports advanced features like GIN and GiST indexes, making it ideal for applications that need to filter or search JSON data quickly. Meanwhile, json is useful when you want to keep the exact JSON text, such as for logging or when formatting matters.

Code Comparison

Here is how you insert and query data using the json type in PostgreSQL.

sql

CREATE TABLE example_json (data json);

INSERT INTO example_json VALUES ('{"name": "Alice", "age": 30}');

SELECT data->>'name' AS name FROM example_json;

Output

 name 
-------
 Alice
(1 row)

jsonb Equivalent

Here is the same example using the jsonb type, which supports indexing and faster queries.

sql

CREATE TABLE example_jsonb (data jsonb);

INSERT INTO example_jsonb VALUES ('{"name": "Alice", "age": 30}');

SELECT data->>'name' AS name FROM example_jsonb;

Output

 name 
-------
 Alice
(1 row)

When to Use Which

Choose jsonb when you need to query, filter, or index JSON data efficiently in PostgreSQL. It is the better choice for most applications because of its speed and indexing support.

Choose json only if you need to preserve the exact JSON text formatting, including whitespace and key order, such as for logging or when the original JSON structure must remain unchanged.

Key Takeaways

jsonb is faster and supports indexing, making it ideal for querying JSON data.

json preserves exact input formatting but is slower and lacks native indexing support.

Use jsonb for most applications unless you need to keep the original JSON text.

jsonb removes whitespace and normalizes key order, unlike json.

Insertion is slightly faster with json, but query speed favors jsonb.