PostgreSQL JSON

Key JSON Features

-- Different attributes per product
INSERT INTO products (name, attributes) 
VALUES ('Shirt', '{"color": "blue", "size": "M"}');

-- Find by JSON field
SELECT * FROM products 
WHERE attributes->>'color' = 'blue';

-- Index JSON field
CREATE INDEX idx_color 
ON products((attributes->>'color'));

-- Update JSON field
UPDATE products 
SET attributes = jsonb_set(attributes, '{price}', '29.99');

🔹 JSON vs JSONB

PostgreSQL offers two JSON types. JSONB is almost always the better choice because it's faster for queries and supports indexing, though it takes slightly more time to insert.

-- Use JSONB for better performance
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    settings JSONB
);

🔹 Inserting JSON Data

Insert JSON data as text strings. PostgreSQL automatically validates and stores it. You can insert simple objects or complex nested structures with arrays and objects.

-- Create products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    attributes JSONB
);

-- Insert simple JSON
INSERT INTO products (name, attributes) 
VALUES ('T-Shirt', '{"color": "blue", "size": "M", "price": 19.99}');

-- Insert complex nested JSON
INSERT INTO products (name, attributes) 
VALUES ('Laptop', '{
    "brand": "Dell",
    "specs": {
        "cpu": "Intel i7",
        "ram": "16GB",
        "storage": "512GB SSD"
    },
    "tags": ["electronics", "computers"]
}');

🔹 Querying JSON Data

Use -> to get JSON objects and ->> to get text values. These operators let you access nested fields and filter rows based on JSON content.

-- Get JSON field as text
SELECT name, attributes->>'color' as color 
FROM products;

-- Get nested JSON field
SELECT name, attributes->'specs'->>'cpu' as cpu 
FROM products;

-- Filter by JSON field
SELECT * FROM products 
WHERE attributes->>'color' = 'blue';

-- Filter by nested field
SELECT * FROM products 
WHERE attributes->'specs'->>'ram' = '16GB';

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

🔹 Updating JSON Data

Use jsonb_set() to update specific fields within JSON without replacing the entire object. This is useful for modifying one property while keeping others unchanged.

-- Update a JSON field
UPDATE products 
SET attributes = jsonb_set(attributes, '{price}', '24.99')
WHERE name = 'T-Shirt';

-- Update nested field
UPDATE products 
SET attributes = jsonb_set(attributes, '{specs, ram}', '"32GB"')
WHERE name = 'Laptop';

-- Add new field to JSON
UPDATE products 
SET attributes = attributes || '{"discount": 10}'
WHERE attributes->>'color' = 'blue';

-- Remove a field
UPDATE products 
SET attributes = attributes - 'discount';

🔹 JSON Array Operations

Work with JSON arrays using array operators. You can check for array elements, expand arrays into rows, or filter by array contents.

-- Create table with JSON arrays
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags JSONB
);

-- Insert array data
INSERT INTO posts (title, tags) 
VALUES ('PostgreSQL Tutorial', '["database", "sql", "tutorial"]');

-- Check if array contains value
SELECT * FROM posts 
WHERE tags ? 'database';

-- Check if array contains any of these values
SELECT * FROM posts 
WHERE tags ?| array['sql', 'nosql'];

-- Check if array contains all of these values
SELECT * FROM posts 
WHERE tags ?& array['database', 'tutorial'];

-- Expand array to rows
SELECT title, jsonb_array_elements_text(tags) as tag 
FROM posts;

🔹 Indexing JSON Data

Create indexes on JSON fields for fast queries. Use GIN indexes for general JSON queries or expression indexes for specific fields you query frequently.

-- GIN index for general JSON queries
CREATE INDEX idx_attributes ON products USING GIN (attributes);

-- Index specific JSON field
CREATE INDEX idx_color ON products((attributes->>'color'));

-- Index for array containment queries
CREATE INDEX idx_tags ON posts USING GIN (tags);

-- Now queries are fast
SELECT * FROM products WHERE attributes->>'color' = 'blue';
SELECT * FROM posts WHERE tags ? 'database';

🔹 JSON Functions

PostgreSQL provides many functions for working with JSON. These help you transform, aggregate, and manipulate JSON data in powerful ways.

-- Get JSON object keys
SELECT jsonb_object_keys(attributes) FROM products;

-- Convert JSON to text
SELECT jsonb_pretty(attributes) FROM products;

-- Get array length
SELECT name, jsonb_array_length(tags) as tag_count FROM posts;

-- Build JSON from query results
SELECT jsonb_build_object(
    'name', name,
    'color', attributes->>'color'
) FROM products;

-- Aggregate rows into JSON array
SELECT jsonb_agg(name) FROM products;

🔹 When to Use JSON

JSON is great for flexible data, but don't overuse it. Use regular columns for data you query frequently, and JSON for truly dynamic or nested data.