Hybrid data sources (JSON + database)

Hybrid sources represent one of ObjectQuel's unique capabilities - the ability to query JSON files as if they were database tables. This feature allows you to:

• Unify Data Sources: Combine database entities with external JSON data in single queries
• Flexible Integration: Work with APIs, configuration files, or static data without importing into your database
• Rapid Prototyping: Test with mock data before implementing full database schemas
• External System Integration: Query third-party data sources using familiar ObjectQuel syntax
• Hybrid Relationships: Create relationships between database entities and JSON data

JSON sources support ObjectQuel query operations including filtering and sorting:

// Filter by properties
$results = $entityManager->executeQuery("
    range of product is json_source('catalog/products.json')
    retrieve (product) where product.category = :category
    sort by product.price asc
", ['category' => 'electronics']);

// Complex filtering with multiple conditions
$results = $entityManager->executeQuery("
    range of item is json_source('inventory/items.json')
    retrieve (item) where item.price > :minPrice
    and item.inStock = true
    sort by item.name
", ['minPrice' => 25.00]);

// Property selection from JSON
$results = $entityManager->executeQuery("
    range of product is json_source('data/products.json')
    retrieve (product.name, product.price) where product.id = :productId
", ['productId' => 15]);

JSON File Structure

JSON files should contain an array of objects with consistent structure:

[
  {
    "id": 1,
    "name": "Wireless Headphones",
    "price": 99.99,
    "category": "electronics",
    "inStock": true,
    "tags": ["wireless", "audio", "bluetooth"]
  },
  {
    "id": 2,
    "name": "Gaming Mouse",
    "price": 59.99,
    "category": "electronics",
    "inStock": false,
    "tags": ["gaming", "precision", "rgb"]
  },
  {
    "id": 3,
    "name": "Coffee Mug",
    "price": 12.99,
    "category": "kitchen",
    "inStock": true,
    "tags": ["ceramic", "dishwasher-safe"]
  }
]

JSONPath Prefiltering

ObjectQuel uses the softcreatr/jsonpath library to provide powerful JSONPath prefiltering capabilities. The second parameter of json_source() accepts a JSONPath expression that filters the JSON data before it reaches the ObjectQuel query processor:

// Filter to only electronics products before querying
$results = $entityManager->executeQuery("
    range of product is json_source('catalog/products.json', '$.products[?(@.category == \"electronics\")]')
    retrieve (product) where product.price > :minPrice
", ['minPrice' => 50.00]);

// Extract nested data structures
$results = $entityManager->executeQuery("
    range of order is json_source('orders/daily.json', '$.orders[*].items[*]')
    retrieve (order) where order.quantity > :minQty
", ['minQty' => 2]);

// Filter by array conditions
$results = $entityManager->executeQuery("
    range of user is json_source('users/profiles.json', '$.users[?(@.roles[*] == \"admin\")]')
    retrieve (user) where user.active = true
");

JSONPath Expression Examples

Common JSONPath patterns for prefiltering JSON data:

Complex JSON Structure Handling

JSONPath prefiltering is particularly useful for complex, nested JSON structures:

{
  "metadata": {
    "version": "1.0",
    "timestamp": "2025-06-04T10:30:00Z"
  },
  "data": {
    "products": [
      {
        "id": 1,
        "name": "Laptop",
        "category": "electronics",
        "specifications": {
          "cpu": "Intel i7",
          "ram": "16GB"
        },
        "availability": {
          "inStock": true,
          "quantity": 5
        }
      }
    ],
    "categories": [
      {
        "id": "electronics",
        "name": "Electronics",
        "active": true
      }
    ]
  }
}

// Extract only products with sufficient stock
$results = $entityManager->executeQuery("
    range of product is json_source('complex_catalog.json', '$.data.products[?(@.availability.quantity > 0)]')
    retrieve (product) where product.category = :category
", ['category' => 'electronics']);

// Get active categories only
$results = $entityManager->executeQuery("
    range of cat is json_source('complex_catalog.json', '$.data.categories[?(@.active == true)]')
    retrieve (cat) where cat.name = :name
", ['name' => 'Electronics']);

Performance Benefits of Prefiltering

JSONPath prefiltering provides significant performance advantages:

• Reduced Memory Usage: Only relevant data is loaded into ObjectQuel's query processor
• Faster Query Processing: Smaller datasets result in faster WHERE clause evaluation
• Selective Loading: Extract specific parts of complex nested structures

// Without prefiltering - loads entire 10MB file
$results = $entityManager->executeQuery("
    range of log is json_source('logs/massive_log.json')
    retrieve (log) where log.level = 'ERROR'
");

// With prefiltering - only loads error entries
$results = $entityManager->executeQuery("
    range of log is json_source('logs/massive_log.json', '$[?(@.level == \"ERROR\")]')
    retrieve (log) where log.timestamp > :since
", ['since' => '2025-06-01']);

Combining Database and JSON Sources

The real power of hybrid sources becomes apparent when combining database entities with JSON data. Currently, JSON sources are always left joined with database entities. Support for cross and inner joins will be added in future releases and will be automatically inferred from the query context:

// Join database orders with JSON product catalog
$results = $entityManager->executeQuery("
    range of order is App\\Entity\\OrderEntity
    range of product is json_source('external/product_catalog.json')
    retrieve (order, product) where order.productId = product.id
    and order.status = :status
    sort by order.orderDate desc
", ['status' => 'pending']);

// Cross-reference customer data with JSON preferences
$results = $entityManager->executeQuery("
    range of customer is App\\Entity\\CustomerEntity
    range of pref is json_source('preferences/user_preferences.json')
    retrieve (customer, pref) where customer.customerId = pref.userId
    and pref.notifications = true
");

JSON Source Relationships

You can establish relationships between database entities and JSON sources using WHERE clauses to link the data:

// Link orders to external product information
$results = $entityManager->executeQuery("
    range of order is App\\Entity\\OrderEntity
    range of product is json_source('api_cache/products.json')
    retrieve (order, product.name, product.manufacturer)
    where order.productSku = product.sku
    and order.customerId = :customerId
", ['customerId' => 12345]);

// Multi-level relationships with JSON
$results = $entityManager->executeQuery("
    range of user is App\\Entity\\UserEntity
    range of profile is json_source('profiles/user_profiles.json')
    range of settings is json_source('settings/app_settings.json')
    retrieve (user, profile.displayName, settings.colorScheme)
    where user.userId = profile.user_id
    and profile.theme = settings.theme_name
    and user.active = true
");

Note: The via syntax for establishing relationships with JSON sources is not currently supported but may be added in future releases.

Advanced JSON Operations

JSON sources support advanced ObjectQuel features including pattern matching:

// Pattern matching in JSON
$results = $entityManager->executeQuery("
    range of log is json_source('logs/access_logs.json')
    retrieve (log) where log.userAgent = 'Mozilla*'
    and log.responseCode = 200
");

// Array field querying
$results = $entityManager->executeQuery("
    range of product is json_source('data/products.json')
    retrieve (product) where 'wireless' in product.tags
");

JSON Source Performance Considerations

When working with JSON sources, consider these performance optimization strategies:

• File Size: Keep JSON files reasonably sized (under 10MB) for optimal performance
• Indexing: For large JSON datasets, consider importing into database tables with proper indexes
• Memory Usage: Large JSON files are loaded entirely into memory during query processing

Error Handling

When working with JSON sources, implement proper error handling for common scenarios:

try {
    $results = $entityManager->executeQuery("
        range of data is json_source('external/api_data.json')
        retrieve (data) where data.status = 'active'
    ");
} catch (QuelException $e) {
    // Handle file not found, invalid JSON, or query errors
    if (str_contains($e->getMessage(), 'json_source')) {
        // Fallback to default data or log error
        error_log("JSON source error: " . $e->getMessage());
        $results = []; // or provide default data
    } else {
        throw $e; // Re-throw other query errors
    }
}

Best Practices

Follow these guidelines when implementing hybrid sources:

• Consistent Schema: Maintain consistent JSON object structures within each file
• File Organization: Use logical directory structures for JSON data organization
• Validation: Validate JSON file integrity before querying
• Documentation: Document JSON schema expectations for team members
• Backup Strategy: Include JSON data files in your backup and version control systems
• Security: Validate file paths to prevent directory traversal attacks

Hybrid sources provide unprecedented flexibility in data integration, enabling ObjectQuel applications to seamlessly work with diverse data sources while maintaining a consistent, powerful query interface.