How to Query JSON Columns in PostgreSQL

PostgreSQL's JSONB type lets you store structured, schema-less data alongside relational columns. It supports indexing, efficient querying, and a rich set of operators.

Extracting Values

-- Sample data: metadata = {"color": "red", "size": "M", "brand": "Acme"}

-- -> returns JSON type
SELECT metadata -> 'color' FROM products;
-- Result: "red" (with quotes, JSON type)

-- ->> returns text
SELECT metadata ->> 'color' FROM products;
-- Result: red (plain text)

-- Nested access: {"address": {"city": "Austin", "state": "TX"}}
SELECT metadata -> 'address' ->> 'city' FROM customers;
-- Result: Austin

Use -> to get a JSON object and ->> to get a plain text value. Chain them for nested access. Always end with ->> when you need the final value as text.

Filtering on JSON Values

-- Filter by a specific key value
SELECT * FROM products
WHERE metadata ->> 'color' = 'red';

-- Containment operator (uses GIN index)
SELECT * FROM products
WHERE metadata @> '{"color": "red", "size": "M"}';

-- Check if a key exists
SELECT * FROM products
WHERE metadata ? 'color';

-- Check if any of the keys exist
SELECT * FROM products
WHERE metadata ?| ARRAY['color', 'weight'];

The @> containment operator is the most efficient way to filter JSONB because it can use a GIN index. Prefer it over ->> comparisons when possible.

Working with JSON Arrays

-- Sample: tags = ["electronics", "sale", "featured"]

-- Access array element by index (0-based)
SELECT tags -> 0 FROM products;
-- Result: "electronics"

-- Expand array into rows
SELECT id, jsonb_array_elements_text(tags) AS tag
FROM products;

-- Filter by array containment
SELECT * FROM products
WHERE tags @> '["sale"]';

Modifying JSONB Data

-- Set a key
UPDATE products
SET metadata = jsonb_set(metadata, '{color}', '"blue"')
WHERE id = 1;

-- Remove a key
UPDATE products
SET metadata = metadata - 'color'
WHERE id = 1;

-- Merge (concatenate) JSONB objects
UPDATE products
SET metadata = metadata || '{"weight": "1.5kg"}'
WHERE id = 1;

Common Use Cases

• Storing flexible product attributes that vary across categories
• Logging API request/response payloads for debugging
• User preferences and settings that evolve over time
• Event data with varying schemas (analytics, audit logs)