Skip to content
All glossary terms

JSONB

What is JSONB in PostgreSQL, and how is it different from JSON?

jsonb stores JSON in a decomposed binary format that is fast to query and can be indexed. It is the JSON type most applications should use.

PostgreSQL has two JSON types. json stores an exact copy of the input text, while jsonb stores a decomposed binary form: slightly slower to write because of the conversion, but significantly faster to process, and it supports indexing.

jsonb does not keep whitespace, key order or duplicate keys; if a key appears twice, only the last value is kept. Use -> to get a JSON value, ->> to get text, and @> to test whether one document contains another. A GIN index on a jsonb column speeds up containment and key-existence queries.

Example

The example uses the @> operator to find the profile whose city is Osaka and pretty-prints another profile's document.

CREATE TABLE profiles (id int PRIMARY KEY, data jsonb NOT NULL);
INSERT INTO profiles VALUES
  (1, '{"name": "Ana", "city": "Lisbon", "tags": ["admin"]}'),
  (2, '{"name": "Ben", "city": "Osaka"}');

SELECT data->>'name' AS name FROM profiles WHERE data @> '{"city": "Osaka"}';
SELECT jsonb_pretty(data) FROM profiles WHERE id = 1;

Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.

Sources

Official PostgreSQL documentation (in English).

Related terms