JSON data types in Postgres: json vs jsonb
Leo Sjöberg • July 23, 2024
Postgres provides two data types for storing JSON data: json and jsonb. Both validate the JSON before storing it, but they handle storage differently. The json type stores data as a single text blob, preserving the exact formatting, including newlines, whitespace, and duplicate keys.
Conversely, jsonb parses the data and stores it in a binary format, allowing for significantly more efficient querying. With json, Postgres needs to parse the JSON data for every query, which can be inefficient.
json data type is closer to Postgres’ jsonb than json. MySQL also uses a binary representation, making queries more efficient.
When to use jsonb
If you’re working with JSON in PostgreSQL, you should generally opt for jsonb. It offers better query performance and can be indexed with a GIN index. The primary drawback of jsonb is its inability to store data that can't be represented by its underlying text data type, such as null bytes (which are valid in JSON). However, this is rarely an issue for most applications.
json columns allow storing null bytes, querying them is still problematic. For instance, a query like data->>foo on data containing a null byte will fail just as it would with jsonb. Therefore, json is only useful if you’re not querying the data!
When to use json
json columns preserve key ordering and duplicate keys, making them suitable for applications that rely on these features or need to present the original formatting to users.
In nearly all other scenarios, jsonb is the better choice. It provides faster and more efficient queries and guarantees your data can be queried. For more details on safely sanitising data to remove null bytes, check out my adventure with null bytes.