PostgreSQL JSON Queries and Operations

PostgreSQL's JSON support enables flexible data modeling. Here's how to query and manipulate JSON data effectively.

JSON vs JSONB#

-- JSON: Stores exact copy, preserves whitespace and order
-- JSONB: Binary format, faster queries, supports indexing

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  profile JSON,      -- Use for exact preservation
  settings JSONB     -- Use for querying (recommended)
);

-- JSONB is generally preferred
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  attributes JSONB DEFAULT '{}'::jsonb,
  metadata JSONB
);

-- Insert JSON data
INSERT INTO products (name, attributes, metadata)
VALUES (
  'Laptop',
  '{"brand": "Apple", "specs": {"ram": 16, "storage": 512}}',
  '{"tags": ["electronics", "computer"], "rating": 4.5}'
);

Basic JSON Queries#

-- Access JSON field (returns JSON)
SELECT attributes->'brand' FROM products;
-- Result: "Apple" (with quotes)

-- Access JSON field as text
SELECT attributes->>'brand' FROM products;
-- Result: Apple (without quotes)

-- Nested access
SELECT attributes->'specs'->'ram' FROM products;
SELECT attributes->'specs'->>'ram' FROM products;

-- Path access
SELECT attributes #> '{specs, ram}' FROM products;
SELECT attributes #>> '{specs, ram}' FROM products;

-- Filter by JSON value
SELECT * FROM products
WHERE attributes->>'brand' = 'Apple';

-- Filter by nested value
SELECT * FROM products
WHERE (attributes->'specs'->>'ram')::int >= 16;

-- Check if key exists
SELECT * FROM products
WHERE attributes ? 'brand';

-- Check if any key exists
SELECT * FROM products
WHERE attributes ?| array['brand', 'model'];

-- Check if all keys exist
SELECT * FROM products
WHERE attributes ?& array['brand', 'specs'];

Array Operations#

-- Access array element (0-indexed)
SELECT metadata->'tags'->0 FROM products;
-- Result: "electronics"

-- Check if array contains value
SELECT * FROM products
WHERE metadata->'tags' ? 'electronics';

-- Array contains all values
SELECT * FROM products
WHERE metadata->'tags' ?& array['electronics', 'computer'];

-- Expand array to rows
SELECT id, jsonb_array_elements_text(metadata->'tags') as tag
FROM products;

-- Array length
SELECT jsonb_array_length(metadata->'tags') FROM products;

-- Filter by array contents
SELECT * FROM products
WHERE metadata @> '{"tags": ["electronics"]}';

Containment and Existence#

-- Contains (left contains right)
SELECT * FROM products
WHERE attributes @> '{"brand": "Apple"}';

-- Contained by (left is contained by right)
SELECT * FROM products
WHERE '{"brand": "Apple", "specs": {"ram": 16}}'::jsonb @> attributes;

-- Complex containment
SELECT * FROM products
WHERE attributes @> '{"specs": {"ram": 16}}';

-- Top-level key existence
SELECT * FROM products
WHERE attributes ? 'brand';

-- Nested key check (use path)
SELECT * FROM products
WHERE attributes #> '{specs}' ? 'ram';

Modifying JSON#

-- Set/update value
UPDATE products
SET attributes = jsonb_set(attributes, '{brand}', '"Dell"')
WHERE id = 1;

-- Set nested value
UPDATE products
SET attributes = jsonb_set(attributes, '{specs, ram}', '32')
WHERE id = 1;

-- Create nested path if missing
UPDATE products
SET attributes = jsonb_set(
  attributes,
  '{specs, gpu}',
  '"NVIDIA RTX 4090"',
  true  -- create_if_missing
)
WHERE id = 1;

-- Remove key
UPDATE products
SET attributes = attributes - 'brand'
WHERE id = 1;

-- Remove nested key
UPDATE products
SET attributes = attributes #- '{specs, ram}'
WHERE id = 1;

-- Remove array element by index
UPDATE products
SET metadata = metadata #- '{tags, 0}'
WHERE id = 1;

-- Concatenate/merge JSON
UPDATE products
SET attributes = attributes || '{"color": "silver", "weight": 1.4}'::jsonb
WHERE id = 1;

-- Insert into array
UPDATE products
SET metadata = jsonb_set(
  metadata,
  '{tags}',
  metadata->'tags' || '"portable"'::jsonb
)
WHERE id = 1;

Aggregation and Transformation#

-- Build JSON object from columns
SELECT jsonb_build_object(
  'id', id,
  'name', name,
  'brand', attributes->>'brand'
) as product_json
FROM products;

-- Build JSON array
SELECT jsonb_agg(name) as product_names
FROM products;

-- Aggregate to object
SELECT jsonb_object_agg(name, attributes)
FROM products;

-- Transform rows to JSON
SELECT row_to_json(products) FROM products;

-- Pretty print
SELECT jsonb_pretty(attributes) FROM products;

-- Get all keys
SELECT DISTINCT jsonb_object_keys(attributes)
FROM products;

-- Expand object to rows
SELECT id, key, value
FROM products, jsonb_each(attributes);

-- Expand with text values
SELECT id, key, value
FROM products, jsonb_each_text(attributes);

Indexing JSON#

-- GIN index for containment queries (@>, ?, ?|, ?&)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- GIN index with jsonb_path_ops (smaller, @> only)
CREATE INDEX idx_products_attributes_path ON products
USING GIN (attributes jsonb_path_ops);

-- Expression index for specific path
CREATE INDEX idx_products_brand ON products
USING BTREE ((attributes->>'brand'));

-- Partial index
CREATE INDEX idx_active_products ON products
USING GIN (attributes)
WHERE (metadata->>'active')::boolean = true;

-- Query using GIN index
EXPLAIN ANALYZE
SELECT * FROM products
WHERE attributes @> '{"brand": "Apple"}';

-- Query using expression index
EXPLAIN ANALYZE
SELECT * FROM products
WHERE attributes->>'brand' = 'Apple';

JSON Path Queries (PostgreSQL 12+)#

-- JSONPath syntax
SELECT * FROM products
WHERE attributes @? '$.specs.ram ? (@ >= 16)';

-- Extract with JSONPath
SELECT jsonb_path_query(attributes, '$.specs.*')
FROM products;

-- Check existence
SELECT jsonb_path_exists(attributes, '$.specs.ram')
FROM products;

-- Query with filter
SELECT jsonb_path_query_array(
  metadata,
  '$.tags[*] ? (@ like_regex "^elec")'
)
FROM products;

-- Complex path queries
SELECT * FROM products
WHERE attributes @? '$.specs ? (@.ram >= 16 && @.storage >= 256)';

Practical Examples#

-- E-commerce product filtering
SELECT * FROM products
WHERE attributes @> '{"brand": "Apple"}'
  AND (attributes->'specs'->>'ram')::int >= 16
  AND metadata->'tags' ? 'laptop';

-- User preferences with defaults
SELECT
  id,
  COALESCE(settings->>'theme', 'light') as theme,
  COALESCE((settings->>'notifications')::boolean, true) as notifications
FROM users;

-- Aggregate product stats by brand
SELECT
  attributes->>'brand' as brand,
  COUNT(*) as count,
  AVG((attributes->'specs'->>'ram')::numeric) as avg_ram
FROM products
WHERE attributes ? 'brand'
GROUP BY attributes->>'brand';

-- Search in nested arrays
SELECT * FROM products
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements_text(metadata->'tags') as tag
  WHERE tag ILIKE '%computer%'
);

-- Build API response
SELECT jsonb_build_object(
  'products', jsonb_agg(
    jsonb_build_object(
      'id', id,
      'name', name,
      'brand', attributes->>'brand',
      'tags', metadata->'tags'
    )
  ),
  'total', COUNT(*)
) as response
FROM products;

Best Practices#

Schema Design:
✓ Use JSONB over JSON
✓ Index frequently queried paths
✓ Consider partial indexes
✓ Don't over-normalize into JSON

Performance:
✓ Use containment operators with GIN
✓ Expression indexes for equality
✓ Avoid full-table JSON parsing
✓ Monitor query plans

Data Integrity:
✓ Use CHECK constraints
✓ Validate JSON structure
✓ Handle NULL vs missing keys
✓ Consider generated columns

PostgreSQL's JSON support enables flexible schemas while maintaining query performance. Use JSONB with GIN indexes for most cases, expression indexes for specific paths, and JSONPath for complex queries. Balance flexibility with structure for maintainable data models.