Pain points of querying Ontology data with SQL:

Problem 1: Type-agnostic queries need verbose JSON functions
  SQL:   SELECT JSON_EXTRACT(properties, '$.name') FROM entity_common
         WHERE entity_type = 'Person'
         AND JSON_EXTRACT(properties, '$.age') > 30
  OQL:   FETCH Person WHERE age > 30

Problem 2: Graph traversal requires multi-layer JOINs
  SQL:   SELECT ... FROM entity_common ec1
         JOIN entity_edge ee1 ON ...
         JOIN entity_common ec2 ON ...
         JOIN entity_edge ee2 ON ...
         JOIN entity_common ec3 ON ...  -- 3 hops = 5 JOINs
  OQL:   TRAVERSE Person -> WorksAt -> Company -> LocatedIn -> City

Problem 3: Metrics need nested subqueries
  SQL:   SELECT *, (SELECT COUNT(*) FROM entity_edge
         WHERE source_id = ec.entity_id AND edge_type = 'Manages')
         AS direct_reports FROM entity_common ec ...
  OQL:   FETCH Person WITH METRIC direct_reports

Problem 4: Time travel requires versioned JOINs
  SQL:   Extremely complex Iceberg time-travel syntax
  OQL:   FETCH Person AT TIME '2024-01-01T00:00:00Z'

OQL Design Goal Matrix:

┌─────────────────────┬──────────────────────────────────┐
│ Goal                 │ Implementation                    │
├─────────────────────┼──────────────────────────────────┤
│ Ontology-native      │ Entity Type as query entry point  │
│                      │ Direct property refs, no JSON fn  │
├─────────────────────┼──────────────────────────────────┤
│ Built-in traversal   │ TRAVERSE statement + path exprs   │
│                      │ Supports 1-N hop traversal        │
├─────────────────────┼──────────────────────────────────┤
│ Metric expansion     │ WITH METRIC clause auto-expands   │
│                      │ Metric defs decoupled from query  │
├─────────────────────┼──────────────────────────────────┤
│ Time travel          │ AT TIME / AT BRANCH clauses       │
│                      │ Transparently maps to Iceberg     │
├─────────────────────┼──────────────────────────────────┤
│ Translatable to SQL  │ Compiles to Doris SQL for exec    │
│                      │ Retains all OLAP optimizations    │
└─────────────────────┴──────────────────────────────────┘

(* OQL Top-Level Grammar - EBNF Notation *)

oql_statement
    ::= fetch_statement
      | traverse_statement
      | aggregate_statement
      | timeline_statement
      | diff_statement
      | mutate_statement
      ;

(* === FETCH Statement: Entity Queries === *)
fetch_statement
    ::= 'FETCH' entity_type_ref
        [ 'WHERE' predicate_expr ]
        [ 'WITH' with_clause ( ',' with_clause )* ]
        [ 'AT' temporal_clause ]
        [ 'IN' world_ref ]
        [ 'ORDER' 'BY' order_expr ( ',' order_expr )* ]
        [ 'LIMIT' integer [ 'OFFSET' integer ] ]
    ;

(* === TRAVERSE Statement: Graph Traversal === *)
traverse_statement
    ::= 'TRAVERSE' entity_ref
        traverse_path+
        [ 'WHERE' predicate_expr ]
        [ 'WITH' with_clause ( ',' with_clause )* ]
        [ 'DEPTH' integer [ '..' integer ] ]
        [ 'LIMIT' integer ]
    ;

(* === AGGREGATE Statement: Analytics === *)
aggregate_statement
    ::= 'AGGREGATE' entity_type_ref
        'BY' group_expr ( ',' group_expr )*
        'COMPUTE' agg_expr ( ',' agg_expr )*
        [ 'WHERE' predicate_expr ]
        [ 'HAVING' predicate_expr ]
        [ 'AT' temporal_clause ]
        [ 'IN' world_ref ]
        [ 'ORDER' 'BY' order_expr ( ',' order_expr )* ]
        [ 'LIMIT' integer ]
    ;

(* === TIMELINE Statement: Event Timeline === *)
timeline_statement
    ::= 'TIMELINE' entity_ref
        [ 'EVENTS' event_type_list ]
        [ 'FROM' datetime_expr 'TO' datetime_expr ]
        [ 'WHERE' predicate_expr ]
        [ 'ORDER' 'BY' 'event_time' ( 'ASC' | 'DESC' ) ]
        [ 'LIMIT' integer ]
    ;

(* === DIFF Statement: Branch Comparison === *)
diff_statement
    ::= 'DIFF' entity_type_ref
        'BETWEEN' branch_ref 'AND' branch_ref
        [ 'WHERE' predicate_expr ]
    ;

(* === Predicate Expressions === *)
predicate_expr
    ::= comparison_expr
      | predicate_expr 'AND' predicate_expr
      | predicate_expr 'OR' predicate_expr
      | 'NOT' predicate_expr
      | '(' predicate_expr ')'
      | exists_expr
      | contains_expr
      | search_expr
      | similar_expr
    ;

comparison_expr
    ::= property_ref comparator value_expr
    ;

comparator
    ::= '=' | '!=' | '>' | '>=' | '<' | '<='
      | 'IN' | 'NOT' 'IN'
      | 'LIKE' | 'NOT' 'LIKE'
      | 'IS' 'NULL' | 'IS' 'NOT' 'NULL'
      | 'BETWEEN' value_expr 'AND' value_expr
    ;

(* Full-text search *)
search_expr
    ::= 'SEARCH' '(' property_ref ',' string_literal ')'
    ;

(* Semantic similarity *)
similar_expr
    ::= 'SIMILAR' '(' string_literal ',' float_literal ')'
      | 'SIMILAR' '(' vector_literal ',' float_literal ')'
    ;

(* Existence check *)
exists_expr
    ::= 'EXISTS' '(' traverse_path [ 'WHERE' predicate_expr ] ')'
    ;

(* Array containment *)
contains_expr
    ::= property_ref 'CONTAINS' value_expr
      | property_ref 'CONTAINS' 'ANY' '(' value_list ')'
      | property_ref 'CONTAINS' 'ALL' '(' value_list ')'
    ;

(* === Property References === *)
property_ref
    ::= identifier                     (* top-level property *)
      | identifier '.' identifier      (* nested property *)
      | identifier '[' integer ']'     (* array index *)
    ;

(* === Graph Traversal Path === *)
traverse_path
    ::= '->' edge_type_ref [ '(' predicate_expr ')' ] '->' entity_type_ref
      | '<-' edge_type_ref [ '(' predicate_expr ')' ] '<-' entity_type_ref
      | '--' edge_type_ref [ '(' predicate_expr ')' ] '--' entity_type_ref
    ;

(* === Metrics and Computed Properties === *)
with_clause
    ::= 'METRIC' metric_name_list
      | 'COMPUTED' computed_prop_list
      | 'EDGES' edge_summary_list
    ;

(* === Aggregate Functions === *)
agg_expr
    ::= agg_function '(' property_ref ')' [ 'AS' alias ]
    ;

agg_function
    ::= 'COUNT' | 'SUM' | 'AVG' | 'MIN' | 'MAX'
      | 'PERCENTILE' | 'STDDEV' | 'VARIANCE'
      | 'COUNT_DISTINCT' | 'TOPN'
      | 'TIME_BUCKET'
    ;

(* === Temporal Clause === *)
temporal_clause
    ::= 'TIME' datetime_expr
      | 'BRANCH' branch_name
      | 'SNAPSHOT' snapshot_id
    ;

(* === World Reference === *)
world_ref
    ::= 'WORLD' world_name
    ;

FETCH is OQL's most fundamental query, analogous to SQL SELECT:

┌───────────────────────────────────────────────────────────┐
│                    FETCH Statement Structure                │
│                                                            │
│  FETCH <EntityType>                                        │
│    WHERE <conditions>          <- Filter predicates         │
│    WITH METRIC <metrics>       <- Metric expansion          │
│    AT TIME <timestamp>         <- Time travel               │
│    IN WORLD <world>            <- World scope               │
│    ORDER BY <fields>           <- Sorting                   │
│    LIMIT <n> OFFSET <m>        <- Pagination                │
└───────────────────────────────────────────────────────────┘

-- Basic query: Find all engineers over 30
FETCH Person
WHERE department = 'Engineering' AND age > 30
ORDER BY hire_date DESC
LIMIT 20;

-- Nested property query
FETCH Person
WHERE address.city = 'San Francisco' AND skills CONTAINS 'Python';

-- Full-text + semantic search combo
FETCH Document
WHERE SEARCH(content, 'risk assessment report')
  AND SIMILAR('machine learning models for credit risk', 0.8)
ORDER BY _score DESC
LIMIT 10;

-- Query with metric expansion
FETCH Person
WHERE department = 'Engineering'
WITH METRIC direct_reports, total_projects, avg_performance_score
ORDER BY direct_reports DESC;

-- Time travel query
FETCH Person
WHERE department = 'Engineering'
AT TIME '2023-06-01T00:00:00Z'
IN WORLD 'production';

-- Existence condition
FETCH Company
WHERE EXISTS(-> Employs -> Person WHERE role = 'CTO')
  AND industry = 'Technology';

TRAVERSE executes graph traversals, replacing multi-table JOINs:

┌───────────────────────────────────────────────────────────┐
│                  TRAVERSE Statement Structure               │
│                                                            │
│  TRAVERSE <start_entity>                                   │
│    -> <EdgeType> -> <EntityType>     <- Forward traversal   │
│    <- <EdgeType> <- <EntityType>     <- Reverse traversal   │
│    -- <EdgeType> -- <EntityType>     <- Bidirectional       │
│    WHERE <conditions>                <- Path conditions      │
│    DEPTH 1..3                        <- Traversal depth      │
│    LIMIT <n>                         <- Result limit         │
└───────────────────────────────────────────────────────────┘

-- Single-hop: Which company does a person work at
TRAVERSE Person('person-001')
  -> WorksAt -> Company;

-- Two-hop: All projects of a company's employees
TRAVERSE Company('company-001')
  <- WorksAt <- Person
  -> WorksOn -> Project;

-- Conditional traversal: Only senior employees
TRAVERSE Company('company-001')
  <- WorksAt(weight > 0.8) <- Person
WHERE level = 'Senior'
  -> Manages -> Person;

-- Variable depth: Org chart tree
TRAVERSE Person('ceo-001')
  -> Manages -> Person
DEPTH 1..5;

-- Bidirectional: Find connected entities
TRAVERSE Device('device-001')
  -- ConnectedTo -- Device
  -- LocatedAt -- Location
DEPTH 1..3
LIMIT 50;

-- Path pattern matching: Supply chain tracing
TRAVERSE Product('prod-001')
  <- SuppliedBy <- Supplier
  <- ManufacturedBy <- Factory
  -> LocatedIn -> Region
WHERE Region.country = 'China';

-- Headcount and average age by department
AGGREGATE Person
BY department
COMPUTE COUNT(*) AS headcount,
        AVG(age) AS avg_age,
        MAX(salary) AS max_salary
WHERE is_active = true
ORDER BY headcount DESC;

-- Event counts by time bucket
AGGREGATE Event
BY TIME_BUCKET(event_time, '1 HOUR') AS hour_bucket,
   event_type
COMPUTE COUNT(*) AS event_count,
        COUNT_DISTINCT(entity_id) AS unique_entities
WHERE severity IN ('error', 'critical')
  AND event_time BETWEEN '2024-06-01' AND '2024-06-30'
ORDER BY hour_bucket ASC;

-- Nested aggregation: TopN
AGGREGATE Transaction
BY source_account
COMPUTE SUM(amount) AS total_amount,
        COUNT(*) AS tx_count,
        TOPN(target_account, 5) AS top_targets
WHERE transaction_date >= '2024-01-01'
HAVING total_amount > 1000000
ORDER BY total_amount DESC
LIMIT 100;

-- Complete entity timeline
TIMELINE Person('person-001')
FROM '2024-01-01' TO '2024-06-30'
ORDER BY event_time DESC
LIMIT 100;

-- Filter specific event types
TIMELINE Device('device-001')
EVENTS Alert, StatusChange, Measurement
FROM '2024-06-01' TO '2024-06-30'
WHERE severity IN ('error', 'critical')
ORDER BY event_time DESC;

-- Timeline with correlation tracking
TIMELINE Order('order-001')
EVENTS StatusChange
WHERE correlation_id = 'trace-abc-123'
ORDER BY event_time ASC;

# metrics-registry/person-metrics.yaml
metrics:
  - name: direct_reports
    entity_type: Person
    description: "Number of direct reports"
    type: count
    definition:
      edge_type: Manages
      direction: outbound
      count: targets

  - name: total_projects
    entity_type: Person
    description: "Total projects involved in"
    type: count
    definition:
      edge_type: WorksOn
      direction: outbound
      count: targets

  - name: avg_performance_score
    entity_type: Person
    description: "Average performance score"
    type: aggregate
    definition:
      event_type: PerformanceReview
      field: payload.score
      function: avg
      time_range: last_12_months

  - name: alert_frequency
    entity_type: Device
    description: "Alert frequency (per day)"
    type: rate
    definition:
      event_type: Alert
      time_range: last_30_days
      unit: per_day

Metric Expansion Compilation Process:

Input OQL:
  FETCH Person WHERE department = 'Eng'
  WITH METRIC direct_reports, total_projects

Step 1: Look up metric definitions
  direct_reports -> COUNT(edge WHERE Manages outbound)
  total_projects -> COUNT(edge WHERE WorksOn outbound)

Step 2: Generate subqueries
  ┌─────────────────────────────────────────────┐
  │ SELECT ec.*,                                 │
  │   (SELECT COUNT(*)                           │
  │    FROM entity_edge ee                       │
  │    WHERE ee.source_id = ec.entity_id         │
  │      AND ee.edge_type = 'Manages'            │
  │      AND ee.is_deleted = FALSE               │
  │   ) AS direct_reports,                       │
  │   (SELECT COUNT(*)                           │
  │    FROM entity_edge ee                       │
  │    WHERE ee.source_id = ec.entity_id         │
  │      AND ee.edge_type = 'WorksOn'            │
  │      AND ee.is_deleted = FALSE               │
  │   ) AS total_projects                        │
  │ FROM entity_common ec                        │
  │ WHERE ec.entity_type = 'Person'              │
  │   AND JSON_EXTRACT(ec.properties,            │
  │       '$.department') = 'Eng'                │
  └─────────────────────────────────────────────┘

Step 3: Optimize
  - Subqueries -> LEFT JOIN aggregation (for large datasets)
  - Check for pre-computed materialized views
  - Check computed_props cache

OQL vs Other Query Languages:

┌──────────────┬──────────┬──────────┬──────────┬──────────┐
│ Feature       │   OQL    │   SQL    │  Cypher  │ GraphQL  │
├──────────────┼──────────┼──────────┼──────────┼──────────┤
│ Type-aware    │ Native   │ Manual   │ Labels   │ Schema   │
│ Graph traverse│ TRAVERSE │ Multi-   │ Native   │ Nested   │
│               │          │ JOIN     │          │          │
│ Aggregation   │ AGGREGATE│ Native   │ Weak     │ Weak     │
│ Full-text     │ SEARCH   │ Extension│ Plugin   │ None     │
│ Vector search │ SIMILAR  │ Extension│ None     │ None     │
│ Time travel   │ AT TIME  │ None     │ None     │ None     │
│ Branch diff   │ DIFF     │ None     │ None     │ None     │
│ Metric expand │ METRIC   │ Subquery │ None     │ None     │
│ Event timeline│ TIMELINE │ Manual   │ Path     │ None     │
│ Compiles to   │ SQL      │ N/A      │ No       │ Custom   │
│   SQL         │          │          │          │          │
│ OLAP optim.   │ Inherits │ Native   │ Weak     │ Weak     │
└──────────────┴──────────┴──────────┴──────────┴──────────┘

Same query in four languages:

Requirement: "Find all people managed by John, and the projects they work on"

OQL (4 lines):
  TRAVERSE Person('john')
    -> Manages -> Person
    -> WorksOn -> Project;

SQL (15 lines):
  SELECT p2.*, proj.*
  FROM entity_common p1
  JOIN entity_edge e1 ON e1.source_id = p1.entity_id
    AND e1.edge_type = 'Manages'
  JOIN entity_common p2 ON p2.entity_id = e1.target_id
  JOIN entity_edge e2 ON e2.source_id = p2.entity_id
    AND e2.edge_type = 'WorksOn'
  JOIN entity_common proj ON proj.entity_id = e2.target_id
  WHERE p1.entity_id = 'john'
    AND p1.world_id = 'main'
    AND e1.world_id = 'main'
    AND p2.world_id = 'main'
    AND e2.world_id = 'main'
    AND proj.world_id = 'main';

Cypher (6 lines):
  MATCH (p1:Person {id: 'john'})
    -[:Manages]->(p2:Person)
    -[:WorksOn]->(proj:Project)
  RETURN p2, proj;

GraphQL (12 lines):
  query {
    person(id: "john") {
      manages {
        name
        worksOn {
          name
          status
        }
      }
    }
  }

-- The pipe operator |> enables chained processing
FETCH Person
WHERE department = 'Engineering'
|> TRAVERSE -> WorksOn -> Project
|> AGGREGATE BY Project.status
   COMPUTE COUNT(*) AS project_count;

-- Equivalent to nested queries but more readable

-- Use LET to bind intermediate results
LET engineers = FETCH Person WHERE department = 'Engineering';
LET high_performers = engineers WHERE performance_score > 90;

TRAVERSE high_performers
  -> Manages -> Person
WITH METRIC direct_reports;

-- Pattern matching: Find "triangle" relationships
MATCH PATTERN
  (a:Person) -> Knows -> (b:Person),
  (b:Person) -> Knows -> (c:Person),
  (c:Person) -> Knows -> (a:Person)
WHERE a.department != b.department
  AND b.department != c.department
LIMIT 100;

OQL Value Type System:

Primitive Types:
├── String          "hello"
├── Integer         42
├── Float           3.14
├── Boolean         true / false
├── DateTime        '2024-06-15T10:30:00Z'
├── Date            '2024-06-15'
├── Duration        '30 DAYS' / '2 HOURS'
└── Null            NULL

Composite Types:
├── Array           [1, 2, 3] / ['a', 'b']
├── Map             {key: value}
├── Vector          VECTOR([0.1, 0.2, ...])
└── GeoPoint        GEO(31.2, 121.5)

Reference Types:
├── EntityRef       Person('id-001')
├── EdgeRef         Edge('edge-001')
├── WorldRef        WORLD('main')
└── BranchRef       BRANCH('feature-x')

-- String functions
FETCH Person WHERE LOWER(name) LIKE '%john%';
FETCH Person WHERE LENGTH(description) > 100;

-- Math functions
FETCH Device WHERE ABS(temperature - 25.0) < 2.0;

-- Date functions
FETCH Person WHERE YEAR(hire_date) = 2023;
FETCH Event WHERE event_time > NOW() - INTERVAL '7 DAYS';

-- Array functions
FETCH Person WHERE ARRAY_LENGTH(skills) >= 3;
FETCH Person WHERE ARRAY_OVERLAP(skills, ['Python', 'Java']);

-- Geo functions
FETCH Store WHERE GEO_DISTANCE(location, GEO(31.2, 121.5)) < 5000;

OQL provides friendly error messages:

Input: FETCH Perso WHERE age > 30
Error: Unknown entity type 'Perso'. Did you mean 'Person'?
     FETCH Perso WHERE age > 30
           ^^^^^
     Available types: Person, Company, Device, Document

Input: FETCH Person WERE age > 30
Error: Expected 'WHERE', found 'WERE'. Did you mean 'WHERE'?
     FETCH Person WERE age > 30
                  ^^^^

Input: FETCH Person WHERE ages > 30
Error: Unknown property 'ages' on type 'Person'. Did you mean 'age'?
     Available properties: age (Int), name (String), email (String)...