snippetsqlModeratepending

PostgreSQL JSONB queries -- querying and indexing JSON data

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

JSONBGIN indexcontainmentarrow operatorgenerated column

postgresql

Problem

Need to query, filter, and index specific JSON fields stored in PostgreSQL JSONB columns.

Solution

Use JSONB operators and GIN indexes for querying. Generated columns for frequently accessed fields.

Code Snippets

JSONB querying, indexing, and aggregation

SELECT data->>'name' as name, (data->>'age')::int as age
FROM users WHERE data->>'role' = 'admin';

-- Containment query
SELECT * FROM users
WHERE data->'tags' @> '["premium"]'::jsonb;

-- GIN index
CREATE INDEX idx_data ON users USING GIN (data);

-- Index specific path
CREATE INDEX idx_email ON users ((data->>'email'));

-- Aggregate into JSON
SELECT dept, jsonb_agg(jsonb_build_object('name', name, 'salary', salary))
FROM employees GROUP BY dept;

Revisions (0)

No revisions yet.