🗄️ JSONB Operations

Advanced PostgreSQL NoSQL capabilities with relational power

📦

Total Documents

1,247

JSON & JSONB Records

⚡

Query Performance

2.3ms

Avg. Response Time

🔍

Indexed Fields

GIN Indexes Active

📊

Data Categories

Document Types

⚙️ JSONB Operations

🔍 Simple JSON Extraction

Extract specific fields from JSONB documents

SELECT name, data->>'email' as email FROM users;

🔗 Nested Object Queries

Navigate through complex nested JSON structures

SELECT name FROM products WHERE data->'specs'->>'cpu' = 'Intel i7';

📋 Containment Operations

Check if JSON contains specific key-value pairs

SELECT * FROM orders WHERE data @> '{"status": "completed"}';

🔢 Array Operations

Work with JSON arrays and elements

SELECT * FROM products WHERE data->'tags' ? 'electronics';

✏️ Update JSON Fields

Modify specific fields within JSONB documents

UPDATE users SET data = jsonb_set(data, '{profile,age}', '30');

➕ Add New Properties

Dynamically add new fields to existing documents

UPDATE products SET data = data || '{"featured": true}';

🗑️ Remove Properties

Delete specific keys from JSONB documents

UPDATE users SET data = data - 'temporary_field';

🔄 Merge Objects

Combine multiple JSON objects efficiently

SELECT data || additional_data as merged FROM documents;

📊 JSON Aggregations

Aggregate data from JSON fields

SELECT data->>'category', COUNT(*) FROM products GROUP BY data->>'category';

📈 Statistical Analysis

Perform statistical operations on JSON numeric fields

SELECT AVG((data->>'price')::numeric) FROM products;

🔢 Array Aggregations

Aggregate array elements across documents

SELECT jsonb_array_elements_text(data->'tags') as tag FROM products;

🗂️ Dynamic Grouping

Group by dynamically extracted JSON values

SELECT data->>'department', jsonb_agg(data) FROM employees GROUP BY 1;

🚀 GIN Index Creation

Create high-performance indexes for JSONB data

CREATE INDEX idx_data_gin ON products USING GIN (data);

🎯 Partial Indexes

Index specific JSON paths for optimal performance

CREATE INDEX idx_category ON products ((data->>'category'));

📊 Query Performance

Analyze query execution plans for JSONB operations

EXPLAIN ANALYZE SELECT * FROM products WHERE data @> '{"featured": true}';

⚡ Optimization Tips

Best practices for JSONB performance optimization

-- Use specific operators and proper indexing strategies

📋 Query Results

🔍

Ready to Execute

Select an operation above and click "Execute" to see results

💾 Sample Data Structure

👤 User Profile

{
  "id": 1,
  "name": "John Doe",
  "email": "john@example.com",
  "profile": {
    "age": 28,
    "location": "New York",
    "preferences": {
      "theme": "dark",
      "notifications": true,
      "language": "en"
    }
  },
  "skills": ["JavaScript", "Python", "PostgreSQL"],
  "projects": [
    {
      "name": "E-commerce App",
      "status": "completed",
      "technologies": ["React", "Node.js"]
    }
  ]
}

📦 Product Catalog

{
  "id": 101,
  "name": "Gaming Laptop",
  "category": "electronics",
  "specs": {
    "cpu": "Intel i7",
    "ram": "16GB",
    "storage": "512GB SSD",
    "gpu": "RTX 3070"
  },
  "price": 1299.99,
  "tags": ["gaming", "laptop", "high-performance"],
  "availability": {
    "in_stock": true,
    "quantity": 15,
    "warehouse": "US-East"
  },
  "reviews": [
    {
      "rating": 5,
      "comment": "Excellent performance",
      "date": "2024-01-15"
    }
  ]
}

📊 Analytics Event

{
  "event_id": "evt_123",
  "timestamp": "2024-01-15T10:30:00Z",
  "user_id": 12345,
  "event_type": "page_view",
  "properties": {
    "page": "/products",
    "referrer": "google.com",
    "device": {
      "type": "desktop",
      "os": "Windows 11",
      "browser": "Chrome"
    }
  },
  "metadata": {
    "session_id": "sess_456",
    "ip": "192.168.1.1",
    "location": {
      "country": "US",
      "city": "San Francisco"
    }
  }
}

🏗️ Database Schema

👤 users

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- GIN index for JSONB operations
CREATE INDEX idx_users_data_gin ON users USING GIN (data);

-- Specific path indexes
CREATE INDEX idx_users_location ON users ((data->'profile'->>'location'));
CREATE INDEX idx_users_skills ON users USING GIN ((data->'skills'));

📦 products

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    data JSONB NOT NULL,
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Optimized indexes for common queries
CREATE INDEX idx_products_data_gin ON products USING GIN (data);
CREATE INDEX idx_products_category ON products ((data->>'category'));
CREATE INDEX idx_products_tags ON products USING GIN ((data->'tags'));
CREATE INDEX idx_products_price_range ON products (price) WHERE price BETWEEN 100 AND 10000;

📊 analytics_events

CREATE TABLE analytics_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type VARCHAR(100) NOT NULL,
    user_id INTEGER,
    data JSONB NOT NULL,
    timestamp TIMESTAMP DEFAULT NOW()
);

-- Time-based partitioning for performance
CREATE INDEX idx_events_timestamp ON analytics_events (timestamp);
CREATE INDEX idx_events_data_gin ON analytics_events USING GIN (data);
CREATE INDEX idx_events_type_time ON analytics_events (event_type, timestamp);
CREATE INDEX idx_events_user_time ON analytics_events (user_id, timestamp);

🚀 GIN Indexes

Generalized Inverted Indexes are perfect for JSONB containment operations (@>, ?, ?&, ?|)

-- Full document indexing
CREATE INDEX idx_data_gin ON table_name USING GIN (jsonb_column);

-- Specific path indexing
CREATE INDEX idx_specific_path ON table_name USING GIN ((jsonb_column->'path'));

-- Multiple paths
CREATE INDEX idx_multi_path ON table_name USING GIN ((jsonb_column->'path1'), (jsonb_column->'path2'));

🎯 B-Tree Indexes

Traditional indexes for extracted scalar values and range queries

-- Extracted value indexing
CREATE INDEX idx_extracted_value ON table_name ((jsonb_column->>'key'));

-- Composite indexes
CREATE INDEX idx_composite ON table_name ((jsonb_column->>'key1'), (jsonb_column->>'key2'));

-- Partial indexes with conditions
CREATE INDEX idx_partial ON table_name ((jsonb_column->>'status')) 
WHERE jsonb_column->>'active' = 'true';

📊 Performance Tips

Use GIN indexes for containment operations (@>, ?, ?&)
Extract frequently queried paths to B-tree indexes
Consider partial indexes for filtered datasets
Monitor index usage with pg_stat_user_indexes
Use EXPLAIN ANALYZE to verify index usage

🔍 Search Functions

-- Advanced JSONB search function
CREATE OR REPLACE FUNCTION search_jsonb(
    search_table TEXT,
    search_column TEXT,
    search_criteria JSONB
) RETURNS TABLE (
    id INTEGER,
    data JSONB,
    match_score NUMERIC
) AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT id, %I, 
         CASE WHEN %I @> %L THEN 1.0
              WHEN %I ? ANY(SELECT jsonb_object_keys(%L)) THEN 0.5
              ELSE 0.0 END as match_score
         FROM %I 
         WHERE %I @> %L OR %I ? ANY(SELECT jsonb_object_keys(%L))
         ORDER BY match_score DESC',
        search_column, search_column, search_criteria,
        search_column, search_criteria,
        search_table,
        search_column, search_criteria, search_column, search_criteria
    );
END;
$$ LANGUAGE plpgsql;

🛠️ Data Manipulation Functions

-- Deep merge JSONB objects
CREATE OR REPLACE FUNCTION jsonb_deep_merge(a JSONB, b JSONB) 
RETURNS JSONB AS $$
BEGIN
    RETURN (
        SELECT jsonb_object_agg(key, value)
        FROM (
            SELECT key, 
                   CASE 
                       WHEN jsonb_typeof(a->key) = 'object' 
                            AND jsonb_typeof(b->key) = 'object'
                       THEN jsonb_deep_merge(a->key, b->key)
                       ELSE COALESCE(b->key, a->key)
                   END as value
            FROM (
                SELECT key FROM jsonb_object_keys(a) key
                UNION
                SELECT key FROM jsonb_object_keys(b) key
            ) keys
        ) merged
    );
END;
$$ LANGUAGE plpgsql;

-- Extract nested arrays
CREATE OR REPLACE FUNCTION extract_nested_arrays(data JSONB, path TEXT[])
RETURNS JSONB[] AS $$
BEGIN
    RETURN ARRAY(
        SELECT jsonb_array_elements(data #> path)
    );
END;
$$ LANGUAGE plpgsql;

📊 Analytics Functions

-- Calculate JSONB statistics
CREATE OR REPLACE FUNCTION jsonb_stats(
    table_name TEXT,
    column_name TEXT
) RETURNS TABLE (
    total_documents BIGINT,
    unique_keys BIGINT,
    avg_document_size NUMERIC,
    most_common_keys TEXT[]
) AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'WITH stats AS (
            SELECT 
                COUNT(*) as total_docs,
                AVG(octet_length(%I::text)) as avg_size,
                jsonb_object_keys(%I) as keys
            FROM %I
        ), key_counts AS (
            SELECT keys, COUNT(*) as key_count
            FROM stats
            GROUP BY keys
            ORDER BY key_count DESC
            LIMIT 10
        )
        SELECT 
            (SELECT total_docs FROM stats LIMIT 1)::BIGINT,
            COUNT(DISTINCT keys)::BIGINT,
            (SELECT avg_size FROM stats LIMIT 1),
            array_agg(keys ORDER BY key_count DESC)
        FROM key_counts',
        column_name, column_name, table_name
    );
END;
$$ LANGUAGE plpgsql;