cel2sql

cel2sql

cel2sql converts CEL (Common Expression Language) Github Reoi to SQL condition. It is specifically targeting PostgreSQL standard SQL.

Latest Release - v2.6.0

🚀 Major New Features:

• 🔥 JSON/JSONB Comprehensions Support: Full support for CEL comprehensions on JSON/JSONB arrays
• Advanced JSON Array Operations: Support for exists(), all(), exists_one() on JSON/JSONB arrays
• Numeric JSON Field Casting: Automatic casting of numeric JSON fields (e.g., (score)::numeric)
• Nested JSON Array Access: Support for comprehensions on nested JSON arrays (e.g., settings.permissions)
• JSON Type Safety: Null and type checks for JSON/JSONB comprehensions using jsonb_typeof()

Previous Features (v2.5.0):

• 🔥 CEL Comprehensions Support: Full support for all(), exists(), exists_one(), filter(), and map() comprehensions
• PostgreSQL UNNEST Integration: Comprehensions are converted to efficient PostgreSQL SQL using UNNEST() and array functions
• Nested Comprehensions: Support for complex nested comprehensions like employees.exists(e, e.skills.exists(s, s == 'Go'))
• Array Field Access: Direct comprehensions on array fields in PostgreSQL schemas

Previous Features (v2.4.0):

• Comprehensive JSON/JSONB Support: Native PostgreSQL JSON path operations
• Dynamic Schema Loading: Load table schemas directly from PostgreSQL databases
• Enhanced Type System: Improved PostgreSQL type mappings and array support
• Testcontainer Integration: Full test coverage with real PostgreSQL databases

Key Improvements:

• PostgreSQL-optimized SQL generation (single quotes, proper functions)
• JSON field access: user.preferences.theme → user.preferences->>'theme'
• Array operations: size(array) → ARRAY_LENGTH(array, 1)
• String operations: contains() → POSITION(...) > 0
• CEL comprehensions: list.all(x, x > 0) → NOT EXISTS (SELECT 1 FROM UNNEST(list) AS x WHERE NOT (x > 0))
• All tests pass with comprehensive integration coverage

Usage

import (
    "context"
    "fmt"
    
    "github.com/spandigital/cel2sql/v2"
    "github.com/spandigital/cel2sql/v2/pg"
    "github.com/spandigital/cel2sql/v2/sqltypes"
    "github.com/google/cel-go/cel"
)

// PostgreSQL table schema definition
employeeSchema := pg.Schema{
    {Name: "name", Type: "text", Repeated: false},
    {Name: "hired_at", Type: "timestamp with time zone", Repeated: false},
    {Name: "age", Type: "integer", Repeated: false},
    {Name: "active", Type: "boolean", Repeated: false},
}

// Prepare CEL environment
env, _ := cel.NewEnv(
    cel.CustomTypeProvider(pg.NewTypeProvider(map[string]pg.Schema{
        "Employee": employeeSchema,
    })),
    cel.Variable("employee", cel.ObjectType("Employee")),
)

// Convert CEL to SQL
ast, _ := env.Compile(`employee.name == "John Doe" && employee.hired_at >= current_timestamp() - duration("24h")`)
sqlCondition, _ := cel2sql.Convert(ast)

fmt.Println(sqlCondition) // employee.name = 'John Doe' AND employee.hired_at >= CURRENT_TIMESTAMP - INTERVAL '1 DAY'

Dynamic Schema Loading

cel2sql supports dynamically loading table schemas from a PostgreSQL database:

import (
    "context"
    "fmt"
    
    "github.com/spandigital/cel2sql/v2"
    "github.com/spandigital/cel2sql/v2/pg"
    "github.com/spandigital/cel2sql/v2/sqltypes"
    "github.com/google/cel-go/cel"
)

func main() {
    ctx := context.Background()
    
    // Create a type provider with database connection
    provider, err := pg.NewTypeProviderWithConnection(ctx, "postgres://user:pass@localhost/mydb?sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer provider.Close()
    
    // Load table schema dynamically from database
    err = provider.LoadTableSchema(ctx, "employees")
    if err != nil {
        panic(err)
    }
    
    // Use the loaded schema in CEL environment
    env, err := cel.NewEnv(
        cel.CustomTypeProvider(provider),
        cel.Variable("employee", cel.ObjectType("employees")),
    )
    if err != nil {
        panic(err)
    }
    
    // Convert CEL to SQL using the dynamically loaded schema
    ast, issues := env.Compile(`employee.name == "John Doe" && employee.age > 30`)
    if issues != nil && issues.Err() != nil {
        panic(issues.Err())
    }
    
    sqlCondition, err := cel2sql.Convert(ast)
    if err != nil {
        panic(err)
    }
    
    fmt.Println(sqlCondition)
    // Output: employee.name = 'John Doe' AND employee.age > 30
}

This approach is particularly useful when:

• Database schemas change frequently
• You want to avoid manually defining schemas
• Working with multiple tables with different structures
• Building dynamic query builders

Type Conversion

JSON/JSONB Support

cel2sql provides comprehensive support for PostgreSQL JSON and JSONB columns:

// Example with JSON/JSONB fields
userSchema := pg.Schema{
    {Name: "name", Type: "text", Repeated: false},
    {Name: "preferences", Type: "jsonb", Repeated: false},
    {Name: "profile", Type: "json", Repeated: false},
}

env, _ := cel.NewEnv(
    cel.CustomTypeProvider(pg.NewTypeProvider(map[string]pg.Schema{
        "User": userSchema,
    })),
    cel.Variable("user", cel.ObjectType("User")),
)

// CEL expressions automatically convert to PostgreSQL JSON path operations
ast, _ := env.Compile(`user.preferences.theme == "dark"`)
sqlCondition, _ := cel2sql.Convert(ast)
fmt.Println(sqlCondition) // user.preferences->>'theme' = 'dark'

// Nested JSON access
ast, _ = env.Compile(`user.profile.settings.notifications == "enabled"`)
sqlCondition, _ = cel2sql.Convert(ast)
fmt.Println(sqlCondition) // user.profile->>'settings'->>'notifications' = 'enabled'

Supported JSON Operations:

• Field access: user.preferences.theme → user.preferences->>'theme'
• Nested access: user.profile.settings.key → user.profile->>'settings'->>'key'
• Works with both json and jsonb column types
• Automatically detects JSON columns and applies proper PostgreSQL syntax

Supported CEL Operators/Functions

Standard SQL Types/Functions

cel2sql supports time related types bellow.

• DATE
• TIME
• DATETIME

cel2sql contains time related functions bellow.

• current_date()
• current_time()
• current_datetime()
• current_timestamp()
• interval(N, date_part)

CEL Comprehensions

cel2sql now supports CEL comprehensions for working with lists and arrays. Comprehensions are converted to PostgreSQL-compatible SQL using UNNEST() and various array functions.

Supported Comprehension Types

Examples

Simple Array Comprehensions

// Check if all numbers are positive
cel: [1, 2, 3, 4, 5].all(x, x > 0)
sql: NOT EXISTS (SELECT 1 FROM UNNEST(ARRAY[1, 2, 3, 4, 5]) AS x WHERE NOT (x > 0))

// Check if any number is greater than 3
cel: [1, 2, 3, 4, 5].exists(x, x > 3)
sql: EXISTS (SELECT 1 FROM UNNEST(ARRAY[1, 2, 3, 4, 5]) AS x WHERE x > 3)

// Filter even numbers
cel: [1, 2, 3, 4, 5].filter(x, x % 2 == 0)
sql: ARRAY(SELECT x FROM UNNEST(ARRAY[1, 2, 3, 4, 5]) AS x WHERE MOD(x, 2) = 0)

// Double all numbers
cel: [1, 2, 3, 4, 5].map(x, x * 2)
sql: ARRAY(SELECT x * 2 FROM UNNEST(ARRAY[1, 2, 3, 4, 5]) AS x)

PostgreSQL Schema-based Comprehensions

// Define schema with array fields
schema := pg.Schema{
    {Name: "name", Type: "text", Repeated: false},
    {Name: "age", Type: "bigint", Repeated: false},
    {Name: "skills", Type: "text", Repeated: true}, // Array field
}

// CEL expressions with comprehensions
cel: employees.all(e, e.age >= 18)
sql: NOT EXISTS (SELECT 1 FROM UNNEST(employees) AS e WHERE NOT (e.age >= 18))

cel: employees.filter(e, e.age > 30).map(e, e.name)
sql: ARRAY(SELECT e.name FROM UNNEST(ARRAY(SELECT e FROM UNNEST(employees) AS e WHERE e.age > 30)) AS e)

cel: emp.skills.exists(s, s == 'Go')
sql: EXISTS (SELECT 1 FROM UNNEST(emp.skills) AS s WHERE s = 'Go')

Nested Comprehensions

// Check if any employee has Go skills (nested comprehension)
cel: employees.exists(e, e.skills.exists(s, s == 'Go'))
sql: EXISTS (SELECT 1 FROM UNNEST(employees) AS e WHERE EXISTS (SELECT 1 FROM UNNEST(e.skills) AS s WHERE s = 'Go'))

// Filter employees with all high scores
cel: employees.filter(e, e.scores.all(s, s >= 80))
sql: ARRAY(SELECT e FROM UNNEST(employees) AS e WHERE NOT EXISTS (SELECT 1 FROM UNNEST(e.scores) AS s WHERE NOT (s >= 80)))

Working with Composite Types

// Define nested schema
addressSchema := pg.Schema{
    {Name: "city", Type: "text", Repeated: false},
    {Name: "country", Type: "text", Repeated: false},
}

employeeSchema := pg.Schema{
    {Name: "name", Type: "text", Repeated: false},
    {Name: "address", Type: "composite", Schema: addressSchema},
}

// CEL with nested field access
cel: employees.filter(e, e.address.city == 'New York')
sql: ARRAY(SELECT e FROM UNNEST(employees) AS e WHERE e.address.city = 'New York')

cel: employees.map(e, e.address.city)
sql: ARRAY(SELECT e.address.city FROM UNNEST(employees) AS e)

Performance Considerations

• UNNEST with large arrays: PostgreSQL's UNNEST() function is efficient but consider indexing strategies for large datasets
• Nested comprehensions: May generate complex SQL; consider restructuring data or using materialized views for frequently accessed patterns
• Map operations: Return new arrays which may use memory; consider streaming for large results

Usage in Practice

package main

import (
    "fmt"
    "github.com/google/cel-go/cel"
    "github.com/spandigital/cel2sql/v2"
    "github.com/spandigital/cel2sql/v2/pg"
)

func main() {
    // Define PostgreSQL schema
    schema := pg.Schema{
        {Name: "id", Type: "bigint", Repeated: false},
        {Name: "name", Type: "text", Repeated: false},
        {Name: "skills", Type: "text", Repeated: true},
        {Name: "scores", Type: "bigint", Repeated: true},
    }

    provider := pg.NewTypeProvider(map[string]pg.Schema{"Employee": schema})

    env, _ := cel.NewEnv(
        cel.CustomTypeProvider(provider),
        cel.Variable("employees", cel.ListType(cel.ObjectType("Employee"))),
    )

    // Compile and convert CEL comprehension to SQL
    ast, _ := env.Compile(`employees.filter(e, e.scores.all(s, s >= 80)).map(e, e.name)`)
    sqlCondition, _ := cel2sql.Convert(ast)
    
    fmt.Println(sqlCondition)
    // Output: ARRAY(SELECT e.name FROM UNNEST(ARRAY(SELECT e FROM UNNEST(employees) AS e WHERE NOT EXISTS (SELECT 1 FROM UNNEST(e.scores) AS s WHERE NOT (s >= 80)))) AS e)
}