PostgreSQL JSONBガイド：クエリ、インデックス、全文検索

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.

-- GIN index: most useful for JSONB (supports ?, ?|, ?&, @>, <@)
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- GIN index on a specific key (for equality queries on that key)
CREATE INDEX idx_products_category ON products
    USING GIN ((metadata -> 'category'));

-- B-tree index on extracted value (for 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
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

• Always create a GIN index on JSONB columns you query frequently. Without an index, JSONB queries require sequential scans.
• 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 — it uses the GIN index. Avoid ->> with LIKE for text search; use full-text search instead.

Frequently Asked Questions