Skip to content

JSON Helper Tools

Jump to bottom
Temp edited this page Sep 23, 2025 · 1 revision

JSON Helper Tools

Last Updated: September 23, 2025 1:48 PM EST

New in v2.6.0 - Complete JSON Operations Suite

The SQLite MCP Server v2.6.0 introduces 6 specialized JSON helper tools that provide a complete abstraction layer over SQLite's JSON functions. These tools eliminate the complexity of writing raw JSON SQL and provide automatic parameter serialization, intelligent auto-normalization, and enhanced error diagnostics.

🚀 Key Capabilities

  • Direct JSON Operations: Complete abstraction of complex SQLite JSON functions
  • Automatic Parameter Serialization: Pass JavaScript objects directly without manual JSON.stringify()
  • Intelligent Auto-Normalization: Automatic repair of common JSON formatting issues
  • Enhanced Error Diagnostics: Contextual, actionable error messages with security alerts
  • Backward Compatibility: Legacy SQL JSON operations continue to work unchanged
  • Performance Optimized: Efficient JSON path validation and query building

🔧 Available JSON Helper Tools

json_insert - Insert JSON Data with Validation

Insert JSON data with automatic serialization and comprehensive validation:

// Simple example - automatic serialization
json_insert({
  "table": "products",
  "column": "metadata",
  "data": {
    "name": "Laptop",
    "category": "electronics",
    "specs": {"cpu": "Intel i7", "ram": "16GB"}
  }
})

📚 See JSON Helper Tools Masterclass for complete JSON manipulation recipes and advanced use cases.

json_update - Update JSON Fields by Path

Update specific JSON fields with precision targeting:

// Update nested JSON values
json_update({
  "table": "products",
  "column": "metadata",
  "path": "$.specs.ram",
  "value": "32GB",
  "where_clause": "id = 123"
})

json_select - Extract JSON Data with Flexible Output

Extract JSON data with multiple output format options:

// Extract specific paths
json_select({
  "table": "products",
  "column": "metadata", 
  "paths": ["$.name", "$.category", "$.specs.cpu"],
  "where_clause": "active = 1",
  "format": "objects"  // Options: objects, arrays, flat
})

json_query_complex - Advanced JSON Filtering

Advanced JSON filtering and querying operations:

// Complex JSON-based filtering
json_query_complex({
  "table": "products",
  "column": "metadata",
  "filters": [
    {"path": "$.category", "operator": "=", "value": "electronics"},
    {"path": "$.specs.ram", "operator": ">=", "value": "16GB"}
  ],
  "logic": "AND"
})

json_merge - Intelligent JSON Object Merging

Merge JSON objects with configurable conflict resolution:

// Merge with conflict resolution
json_merge({
  "table": "users",
  "column": "profile", 
  "merge_data": {
    "preferences": {"theme": "dark", "notifications": true}
  },
  "strategy": "deep_merge",
  "where_clause": "id = 456"
})

json_validate_security - Security-Focused JSON Validation

Advanced JSON validation with security threat detection:

// Comprehensive security validation
json_validate_security({
  "json_data": '{"user": "admin", "permissions": ["read", "write"]}',
  "check_injection": true,
  "check_xss": true,
  "max_depth": 10
})

🔧 Intelligent Auto-Normalization

The JSON helper tools include intelligent auto-normalization that fixes common formatting issues:

  • Single to Double Quotes: Converts {'key': 'value'} to {"key": "value"}
  • Python Booleans: Converts True/False/None to true/false/null
  • Trailing Commas: Removes invalid trailing commas from objects and arrays
  • Unquoted Keys: Adds missing quotes around object keys
  • Mixed Quote Types: Standardizes to double quotes throughout
// These problematic formats are automatically fixed:
json_insert({
  "table": "users",
  "column": "profile", 
  "data": "{'name': 'John', 'active': True, 'data': None,}"  // Auto-normalized
})
// Becomes: {"name": "John", "active": true, "data": null}

🛡️ Enhanced Error Diagnostics

When JSON operations fail, get intelligent, contextual error messages:

  • Error Categorization: Structural, security, or encoding issues
  • Specific Suggestions: Actionable guidance for each error type
  • Security Alerts: Clear warnings for suspicious patterns
  • Context Information: Shows what auto-normalization was attempted

🚀 Migration from Raw JSON SQL

Before v2.6.0 (Complex SQL):

write_query({
  "query": "UPDATE products SET metadata = json_set(metadata, '$.category', ?, '$.tags', json(?)) WHERE id = ?",
  "params": ["electronics", JSON.stringify(["new", "popular"]), 123]
})

After v2.6.0 (Simple Helper Tools):

json_update({
  "table": "products",
  "column": "metadata",
  "path": "$.category", 
  "value": "electronics",
  "where_clause": "id = 123"
})

🎯 Best Practices

  1. Use Helper Tools for New Development: Take advantage of automatic serialization and validation
  2. Leverage Auto-Normalization: Let the system fix common JSON formatting issues
  3. Enable Security Validation: Use json_validate_security for user-supplied JSON
  4. Batch Operations: Use json_query_complex for multiple JSON-based filters
  5. Consistent Error Handling: Handle enhanced error diagnostics in your application logic

💡 Real-World Examples

E-commerce Product Catalog

// Insert product with complex metadata
json_insert({
  "table": "products",
  "column": "details",
  "data": {
    "name": "Gaming Laptop",
    "brand": "TechCorp",
    "specs": {
      "cpu": "Intel i9",
      "gpu": "RTX 4080",
      "ram": "32GB",
      "storage": "1TB NVMe"
    },
    "features": ["RGB Keyboard", "144Hz Display", "Thunderbolt 4"],
    "warranty": {"duration": "3 years", "type": "comprehensive"}
  }
})

// Update specific specification
json_update({
  "table": "products",
  "column": "details",
  "path": "$.specs.ram",
  "value": "64GB",
  "where_clause": "name = 'Gaming Laptop'"
})

// Query products by specifications
json_query_complex({
  "table": "products",
  "column": "details",
  "filters": [
    {"path": "$.specs.ram", "operator": ">=", "value": "32GB"},
    {"path": "$.specs.gpu", "operator": "LIKE", "value": "%RTX%"}
  ],
  "logic": "AND"
})

User Profile Management

// Merge user preferences
json_merge({
  "table": "users",
  "column": "preferences",
  "merge_data": {
    "theme": "dark",
    "notifications": {
      "email": true,
      "push": false,
      "sms": true
    },
    "privacy": {"profile_visible": false}
  },
  "strategy": "deep_merge",
  "where_clause": "user_id = 12345"
})

// Extract specific preference paths
json_select({
  "table": "users",
  "column": "preferences",
  "paths": ["$.theme", "$.notifications.email", "$.privacy.profile_visible"],
  "where_clause": "active = 1",
  "format": "objects"
})

📚 Related Pages

📝 Note: JSON Helper Tools work seamlessly with SQLite's JSONB binary format for optimal performance and storage efficiency.

Clone this wiki locally