Guida PostgreSQL JSONB: Query, Indicizzazione e Ricerca Full-Text

PostgreSQL JSONB (Binary JSON) is one of the most powerful features in modern PostgreSQL. Unlike the JSON type which stores text verbatim, JSONB stores parsed binary data — enabling fast querying, indexing, and efficient storage. This guide covers everything from basic operations to advanced indexing strategies and performance optimization.

JSON vs JSONB: Which to Use?

PostgreSQL has two JSON types: json and jsonb. For almost all use cases, use jsonb.

Creating Tables and Inserting JSONB Data

JSONB columns store any valid JSON value. Keys are sorted and duplicates are removed on storage.

-- Create a table with JSONB column
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    metadata    JSONB,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Insert JSONB data
INSERT INTO products (name, metadata) VALUES
    ('Widget A', '{"price": 9.99, "category": "widgets", "tags": ["sale", "new"], "specs": {"weight": 0.5, "color": "red"}}'),
    ('Gadget B', '{"price": 24.99, "category": "gadgets", "tags": ["popular"], "specs": {"weight": 1.2, "color": "blue"}, "stock": 150}'),
    ('Doohickey C', '{"price": 4.99, "category": "widgets", "tags": ["sale"], "specs": {"weight": 0.1, "color": "green"}, "rating": 4.5}');

Querying JSONB: Operators Reference

PostgreSQL provides rich operators for accessing and filtering JSONB data.

-- Access a top-level key (returns JSONB)
SELECT metadata -> 'price' FROM products;
-- Returns: 9.99, 24.99, 4.99 (as JSONB)

-- Access a key as text (use ->>)
SELECT metadata ->> 'category' FROM products;
-- Returns: 'widgets', 'gadgets', 'widgets' (as text)

-- Nested access
SELECT metadata -> 'specs' -> 'color' FROM products;
SELECT metadata #> '{specs,color}' FROM products;        -- path operator
SELECT metadata #>> '{specs,color}' FROM products;       -- path as text

-- Filter rows by JSONB value
SELECT name FROM products WHERE metadata ->> 'category' = 'widgets';

-- Filter by nested value
SELECT name FROM products WHERE (metadata -> 'specs' ->> 'weight')::float > 0.5;

-- Check if key exists
SELECT name FROM products WHERE metadata ? 'stock';

-- Check if ANY key in array exists
SELECT name FROM products WHERE metadata ?| ARRAY['stock', 'rating'];

-- Check if ALL keys exist
SELECT name FROM products WHERE metadata ?& ARRAY['price', 'category'];

-- Containment: does JSONB contain this sub-object?
SELECT name FROM products WHERE metadata @> '{"category": "widgets"}';

-- Is JSONB contained by?
SELECT name FROM products WHERE '{"category": "widgets"}' <@ metadata;

JSONB Operators Quick Reference

Modifying JSONB Data

Update, add, and remove fields within JSONB columns without replacing the entire value.

-- Update a specific key (PostgreSQL 14+: subscript syntax)
UPDATE products
SET metadata['price'] = '19.99'
WHERE name = 'Widget A';

-- jsonb_set: update nested key (older syntax, all versions)
UPDATE products
SET metadata = jsonb_set(metadata, '{price}', '19.99')
WHERE name = 'Widget A';

-- Update nested path
UPDATE products
SET metadata = jsonb_set(metadata, '{specs,color}', '"black"')
WHERE name = 'Widget A';

-- Add a new key
UPDATE products
SET metadata = metadata || '{"discount": 0.1}'::jsonb
WHERE name = 'Widget A';

-- Remove a key
UPDATE products
SET metadata = metadata - 'stock'
WHERE name = 'Gadget B';

-- Remove multiple keys
UPDATE products
SET metadata = metadata - ARRAY['stock', 'rating'];

-- Remove nested key
UPDATE products
SET metadata = metadata #- '{specs,weight}';

Indexing JSONB for Performance

The right index strategy can make JSONB queries as fast as queries on regular columns. GIN indexes are the workhorse for JSONB, but jsonb_ops and jsonb_path_ops support different operator sets.

-- Default GIN operator class: jsonb_ops
-- Supports ?, ?|, ?&, @>, @?, and @@ on the indexed JSONB column.
CREATE INDEX idx_products_metadata_gin ON products USING GIN (metadata);

-- Non-default GIN operator class: jsonb_path_ops
-- Smaller and often faster for @>, @?, and @@, but it does not support ?, ?|, or ?&.
CREATE INDEX idx_products_metadata_path_ops ON products
    USING GIN (metadata jsonb_path_ops);

-- Expression GIN index for operators applied to a nested JSONB value
CREATE INDEX idx_products_tags_gin ON products
    USING GIN ((metadata -> 'tags'));

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE metadata -> 'tags' ? 'sale';

-- B-tree index on extracted value (for equality/range queries)
CREATE INDEX idx_products_price ON products
    USING BTREE (((metadata ->> 'price')::numeric));

-- Expression index for a nested text field
CREATE INDEX idx_products_color ON products
    USING BTREE ((metadata #>> '{specs,color}'));

-- Check index usage for containment
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE metadata @> '{"category": "widgets"}';

Aggregation and JSONB Functions

PostgreSQL provides powerful functions for aggregating, expanding, and constructing JSONB values.

-- jsonb_agg: aggregate rows into a JSON array
SELECT
    metadata ->> 'category' AS category,
    jsonb_agg(name ORDER BY name) AS product_names,
    COUNT(*) AS count,
    AVG((metadata ->> 'price')::numeric) AS avg_price
FROM products
GROUP BY metadata ->> 'category';

-- jsonb_object_agg: build a JSON object from key-value pairs
SELECT jsonb_object_agg(name, metadata ->> 'price') AS price_map
FROM products;

-- jsonb_array_elements: expand a JSONB array into rows
SELECT
    name,
    jsonb_array_elements_text(metadata -> 'tags') AS tag
FROM products;

-- jsonb_each: expand a JSONB object into key-value rows
SELECT
    name,
    key,
    value
FROM products,
     jsonb_each(metadata -> 'specs');

-- jsonb_build_object: construct JSONB from expressions
SELECT jsonb_build_object(
    'id', id,
    'name', name,
    'price', (metadata ->> 'price')::numeric,
    'in_stock', metadata ? 'stock'
) AS product_summary
FROM products;

Performance Tips

• Create a GIN index on JSONB columns you query frequently. Use the default jsonb_ops class first, then consider jsonb_path_ops for heavy @>, @?, and @@ workloads.
• Extract frequently-queried fields into regular columns with generated columns: GENERATED ALWAYS AS (metadata ->> 'category') STORED.
• Use jsonb instead of json — it is faster for reads (binary format, no re-parsing) and supports indexing.
• Avoid deep nesting. Flat JSONB structures are easier to index and query. Consider normalizing very deep structures.
• Use @> (containment) for filtering on known sub-objects because it can use JSONB GIN indexes. Avoid ->> with LIKE for text search; use full-text search or expression indexes instead.

Frequently Asked Questions