This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

JSON in PostgreSQL: JSONB vs JSON, Indexing, and Operations

PostgreSQL's JSON support has matured into a powerful feature set. The jsonb type combined with GIN indexes makes PostgreSQL a competitive document database while retaining all relational capabilities. This article covers the types, operators, indexes, and when JSON in PostgreSQL is the right choice.

JSON vs JSONB

PostgreSQL offers two JSON data types:

| Aspect | json | jsonb | |--------|--------|---------| | Storage | Exact copy of input text | Decomposed binary format | | Duplicate keys | Preserved | Last key wins | | Whitespace | Preserved | Removed | | Key ordering | Preserved | Not preserved | | Indexing | Function-based only | GIN indexes supported | | Parsing overhead | On each access | On insert only |

Always usejsonb for new projects unless you have a specific need for json (such as preserving duplicate keys exactly as input).

CREATE TABLE products (

id BIGSERIAL PRIMARY KEY,

name TEXT NOT NULL,

attributes JSONB NOT NULL DEFAULT '{}'

);

INSERT INTO products (name, attributes) VALUES

('Widget', '{"color": "red", "weight": 1.5, "tags": ["sale", "new"]}'),

('Gadget', '{"color": "blue", "dimensions": {"width": 10, "height": 5}}');

Query Operators

PostgreSQL provides a rich set of JSONB operators:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- -> returns JSONB (preserves types)

SELECT attributes -> 'color' FROM products; -- "red" (jsonb)

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- ->> returns TEXT

SELECT attributes ->> 'color' FROM products; -- red (text)

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Path access

SELECT attributes #> '{dimensions, width}' FROM products;

SELECT attributes #>> '{dimensions, width}' FROM products;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Containment check

SELECT * FROM products WHERE attributes @> '{"color": "red"}';

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Key existence

SELECT * FROM products WHERE attributes ? 'dimensions';

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Multiple key existence

SELECT * FROM products WHERE attributes ?| ARRAY['color', 'size'];

SELECT * FROM products WHERE attributes ?& ARRAY['color', 'weight'];

Indexing JSONB

GIN Index

The default GIN index supports containment (@>), key existence (?), and key/element existence (?|, ?&):

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

GIN with jsonb_path_ops

The jsonb_path_ops operator class creates a more focused index that is faster for containment queries:

CREATE INDEX idx_products_attributes_path ON products

USING GIN (attributes jsonb_path_ops);

The jsonb_path_ops index is typically 1/3 the size of the default GIN index and 2-3x faster for @> queries. However, it does not support ?, ?|, or ?& operators.

B-tree Index for JSONB Fields

For equality and rang

---

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.