Working with JSON

Introduction to JSON in PostgreSQL

PostgreSQL supports two JSON types:

• JSON: stores raw JSON text
• JSONB: binary format, indexed and optimized for querying

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
  ('Laptop', '{"brand": "Dell", "ram": "16GB"}'),
  ('Phone', '{"brand": "Samsung", "ram": "8GB"}');

Querying and Indexing JSONB

Access and filter by key:

SELECT * FROM products
WHERE attributes->>'ram' = '16GB';

Create a GIN index:

CREATE INDEX idx_attr_gin ON products USING GIN (attributes);

Performance tip:

• Default GIN index vs jsonb_path_ops — choose jsonb_path_ops for key-existence queries.

Advanced JSONB Operations

-- Merge two JSONB objects
UPDATE products
SET attributes = attributes || '{"storage": "512GB"}';

-- Set or update nested value
UPDATE products
SET attributes = jsonb_set(attributes, '{specs,battery}', '"5000mAh"');

-- Delete a path
UPDATE products
SET attributes = attributes - 'ram';

-- JSONPath query (Postgres 12+)
SELECT * FROM products
WHERE jsonb_path_exists(attributes, '$.specs.battery ? (@ == "5000mAh")');

Validation and Schema Enforcement

Use constraints or triggers to enforce structure:

-- Simple CHECK for required keys
ALTER TABLE products
ADD CONSTRAINT chk_must_have_brand
CHECK (attributes ? 'brand');

-- PL/pgSQL trigger to validate JSON schema (using jsonschema extension)
CREATE FUNCTION validate_product_json() RETURNS trigger AS $$
BEGIN
  PERFORM jsonschema_validate(attributes, 'product_schema');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_json
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION validate_product_json();

Updatable JSON Views

-- Base table
CREATE TABLE settings (
  id SERIAL PRIMARY KEY,
  config JSONB
);

-- Updatable view
CREATE VIEW user_settings AS
SELECT id, config->>'theme' AS theme
FROM settings;

-- INSTEAD OF trigger
CREATE FUNCTION update_theme() RETURNS trigger AS $$
BEGIN
  UPDATE settings
  SET config = jsonb_set(config, '{theme}', to_jsonb(NEW.theme))
  WHERE id = OLD.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_theme
INSTEAD OF UPDATE ON user_settings
FOR EACH ROW EXECUTE FUNCTION update_theme();