PostgreSQL JSONB Queries

Create a comprehensive PostgreSQL database implementation for [Application Name] that leverages JSONB columns for flexible, schema-less data storage with efficient querying capabilities.

Core Requirements

Database Schema

Implement tables with JSONB columns for:

• [Table 1 Name]: Store [data type] (e.g., event tracking data with dynamic properties)
• [Table 2 Name]: Store [data type] (e.g., user preferences and metadata)
• [Table 3 Name]: Store [data type] (e.g., product attributes and specifications)

Each table should include:

• Primary key (id)
• JSONB data column
• Timestamps (created_at, updated_at)
• Appropriate indexes for query performance

Query Capabilities

Implement functions or prepared statements for:

1. Field Extraction: Get specific values from JSON (both as JSON and as text)
2. Filtering: Query records based on JSON field values
3. Existence Checks: Verify if specific keys exist
4. Containment Searches: Find records containing specific JSON structures
5. Path Queries: Access nested JSON data efficiently
6. Array Operations: Query JSON arrays within the data

Indexing Strategy

Create optimized indexes:

• GIN indexes on JSONB columns for containment operations (@>, ?, ?&, ?|)
• Expression indexes for frequently queried JSON paths
• Partial indexes for common filter conditions

Update Operations

Provide methods to:

• Update specific fields within JSONB without replacing entire object
• Merge new JSON data with existing data
• Remove specific keys from JSONB objects
• Manipulate JSON arrays (append, remove, update elements)

Performance Optimization

• Use appropriate operator classes for indexes
• Implement efficient query patterns
• Minimize JSON parsing overhead
• Monitor and optimize slow queries

Implementation

Provide complete SQL including:

1. Table Definitions with JSONB columns and constraints
2. Index Creation statements for optimal query performance
3. Sample Insert Statements showing JSON data structure
4. Query Examples demonstrating:
   • Simple field extraction (-> and ->> operators)
   • Nested path access
   • WHERE clause filtering on JSON fields
   • Key existence checks (? operator)
   • Containment queries (@> operator)
   • Array containment checks
5. Update Examples showing:
   • jsonb_set for updating specific paths
   • || operator for merging objects
   • • operator for removing keys
6. Aggregation Queries grouping by JSON fields
7. Complex Queries combining multiple JSON operations

Best Practices to Follow

• Always use JSONB (not JSON) for better performance and indexing
• Create GIN indexes for @>, ?, ?&, ?| operators
• Use expression indexes for frequently accessed paths
• Use ->> (text extraction) in WHERE clauses for better index usage
• Validate JSON structure at application layer before insertion
• Consider normalizing frequently queried fields to regular columns
• Use partial indexes for common filter patterns
• Monitor index usage with EXPLAIN ANALYZE
• Set appropriate statistics targets for JSONB columns

Generate production-ready SQL with proper naming conventions, comments, and error handling.