JSON vs JSONB differences in PostgreSQL

Introduction

JSON and JSONB are two ways to store JSON data in PostgreSQL. Knowing their differences helps you choose the best one for your needs.

When you want to store JSON data as text and keep the original formatting.

When you want to store JSON data in a way that is faster to query and index.

When you need to update parts of JSON data efficiently.

When you want to save space by removing extra spaces and ordering keys.

When you want to perform complex searches inside JSON data quickly.

Syntax

PostgreSQL

CREATE TABLE example_json (
  data JSON
);

CREATE TABLE example_jsonb (
  data JSONB
);

JSON stores data as plain text, preserving formatting.

JSONB stores data in a binary format, optimized for searching and indexing.

Examples

Insert JSON data as text into a JSON column.

PostgreSQL

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

Insert JSON data into a JSONB column; PostgreSQL converts it to binary format.

PostgreSQL

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

Query JSONB data to find rows where age is 30 and get the name.

PostgreSQL

SELECT data->>'name' FROM example_jsonb WHERE data @> '{"age": 30}';

Sample Program

This example shows creating tables with JSON and JSONB columns, inserting data, and querying each type. JSON query uses text matching, JSONB query uses JSON containment operator.

PostgreSQL

CREATE TABLE people_json (
  info JSON
);

CREATE TABLE people_jsonb (
  info JSONB
);

INSERT INTO people_json (info) VALUES
  ('{"name": "Bob", "city": "NY"}'),
  ('{"name": "Carol", "city": "LA"}');

INSERT INTO people_jsonb (info) VALUES
  ('{"name": "Bob", "city": "NY"}'),
  ('{"name": "Carol", "city": "LA"}');

-- Query JSON data (text search)
SELECT info->>'name' AS name FROM people_json WHERE info::text LIKE '%NY%';

-- Query JSONB data (index search)
SELECT info->>'name' AS name FROM people_jsonb WHERE info @> '{"city": "NY"}';

Important Notes

JSONB supports indexing, which makes queries faster.

JSON preserves the exact text, including spaces and order of keys.

JSONB removes duplicate keys and orders keys internally.

Summary

JSON stores data as plain text, keeping formatting.

JSONB stores data in binary, optimized for speed and indexing.

Use JSONB when you need fast queries and updates on JSON data.