[
https://issues.apache.org/jira/browse/CALCITE-7300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18041036#comment-18041036
]
Oleg Alekseev edited comment on CALCITE-7300 at 12/5/25 9:47 AM:
-----------------------------------------------------------------
For now I am still evaluating this functionality and possible design options,
so it is not implemented yet.
## REST API Filter Specification Variants
Different REST APIs use different structures for expressing filters. The
adapter automatically detects the supported format from the OpenAPI
specification. Below are the supported variants:
### Variant 1: Simple flat filters (no DNF/CNF support)
**Description:** REST API accepts only simple flat filters where all conditions
are joined by AND with equality operator (=) only. Field names appear as direct
properties in the request body, not nested structures.
**OpenAPI specification:**
```yaml
requestBody:
content:
application/json:
schema:
properties:
page:
type: integer
limit:
type: integer
name:
type: string
description: Filter by name (exact match only)
lastName:
type: string
description: Filter by lastName (exact match only)
age:
type: integer
description: Filter by age (exact match only)
department:
type: string
description: Filter by department (exact match only)
```
**Example scenario:**
REST API contains the following users:
```json
[
{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" }
]
```
**SQL Query (simple AND conditions only):**
```sql
SELECT * FROM users
WHERE name = 'Alice' AND department = 'IT'
```
**Generated REST request body:**
```json
{
"page": 0,
"limit": 100,
"name": "Alice",
"department": "IT"
}
```
**Result:** Returns user with id: 5 (Alice Smith, 28, IT)
**Limitations:**
Queries with OR operators will be rejected or filtered locally:
```sql
SELECT * FROM users
WHERE name = 'Alice' OR name = 'Bob'
```
Queries with non-equality operators will be rejected or filtered locally:
```sql
SELECT * FROM users
WHERE age >= 25
```
**Use case:** This variant is suitable for simple REST APIs that:
- Only filter by exact field values
- Don't support complex logical expressions
- Have limited query capabilities
- Require minimal request payload
---
### Variant 2: DNF with "where" (AND) + "or" (OR groups)
**Description:** REST API accepts:
- `where`: array of filter criteria joined by AND (common conditions from first
DNF group)
- `or`: array of arrays, where each inner array contains criteria joined by
AND, and outer array represents OR
**OpenAPI specification:**
```yaml
requestBody:
content:
application/json:
schema:
properties:
where:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: List of AND filter criteria
or:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: DNF - List of OR groups
```
**Example scenario:**
REST API contains the following users:
```json
[
{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR" },
{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
```
**SQL Query with nested conditions:**
```sql
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
```
**DNF Expansion:**
```
(name = 'Bob' AND age >= 21 AND lastName = 'Smith')
OR (name = 'Bob' AND age >= 21 AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND department = 'IT' AND lastName = 'Smith')
OR (name = 'Alice' AND department = 'IT' AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND age >= 25 AND lastName = 'Smith')
OR (name = 'Alice' AND age >= 25 AND department = 'IT' AND age < 30)
```
**Generated REST request body:**
```json
{
"page": 0,
"limit": 100,
"where": [
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "lastName", "operator": "=", "value": "Smith" }
],
"or": [
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": "<", "value": 30 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": "<", "value": 30 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 25 },
{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 25 },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": "<", "value": 30 }
]
]
}
```
**Result:** Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith,
28, IT)
---
### Variant 3: DNF with "or" only (no "where")
**Description:** REST API accepts:
- `or`: array of arrays (DNF structure), no separate "where" field
**OpenAPI specification:**
```yaml
requestBody:
content:
application/json:
schema:
properties:
or:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: DNF - List of OR groups (each group is ANDed)
```
**Example scenario:**
REST API contains the following users:
```json
[
{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR" },
{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
```
**SQL Query with nested conditions:**
```sql
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
```
**DNF Expansion:**
```
(name = 'Bob' AND age >= 21 AND lastName = 'Smith')
OR (name = 'Bob' AND age >= 21 AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND department = 'IT' AND lastName = 'Smith')
OR (name = 'Alice' AND department = 'IT' AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND age >= 25 AND lastName = 'Smith')
OR (name = 'Alice' AND age >= 25 AND department = 'IT' AND age < 30)
```
**Generated REST request body:**
```json
{
"page": 0,
"limit": 100,
"or": [
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": "<", "value": 30 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": "<", "value": 30 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 25 },
{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 25 },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": "<", "value": 30 }
]
]
}
```
**Result:** Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith,
28, IT)
---
### Variant 4: CNF with "where" (OR) + "and" (AND groups)
**Description:** REST API accepts:
- `where`: array of filter criteria joined by OR (conditions from first CNF
group)
- `and`: array of arrays, where each inner array contains criteria joined by
OR, and outer array represents AND
**OpenAPI specification:**
```yaml
requestBody:
content:
application/json:
schema:
properties:
where:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: List of OR filter criteria
and:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: CNF - List of AND groups (each group contains OR
criteria)
```
**Example scenario:**
REST API contains the following users:
```json
[
{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR" },
{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
```
**SQL Query with nested conditions:**
```sql
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
```
**CNF Expansion:**
```
(name = 'Bob' OR name = 'Alice')
AND (name = 'Bob' OR name = 'Alice' OR age >= 25)
AND (name = 'Bob' OR name = 'Alice' OR department = 'IT')
AND (name = 'Bob' OR department = 'IT' OR age >= 25)
AND (name = 'Alice' OR age >= 21)
AND (name = 'Alice' OR age >= 21 OR department = 'IT')
AND (age >= 21 OR department = 'IT' OR age >= 25)
AND (lastName = 'Smith' OR department = 'IT')
AND (lastName = 'Smith' OR age < 30)
```
**Generated REST request body:**
```json
{
"page": 0,
"limit": 100,
"where": [
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "name", "operator": "=", "value": "Alice" }
],
"and": [
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 25 }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "department", "operator": "=", "value": "IT" }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": ">=", "value": 25 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 21 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "department", "operator": "=", "value": "IT" }
],
[
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": ">=", "value": 25 }
],
[
{ "name": "lastName", "operator": "=", "value": "Smith" },
{ "name": "department", "operator": "=", "value": "IT" }
],
[
{ "name": "lastName", "operator": "=", "value": "Smith" },
{ "name": "age", "operator": "<", "value": 30 }
]
]
}
```
**Result:** Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith,
28, IT)
---
### Variant 5: CNF with "and" (AND groups with OR inside)
**Description:** REST API accepts:
- `and`: array of arrays, where each inner array contains criteria joined by
OR, and outer array represents AND
**OpenAPI specification:**
```yaml
requestBody:
content:
application/json:
schema:
properties:
and:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: CNF - List of AND groups (each group is a list of OR
criteria)
```
**Example scenario:**
REST API contains the following users:
```json
[
{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR" },
{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
```
**SQL Query with nested conditions:**
```sql
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
```
**CNF Expansion:**
```
(name = 'Bob' OR name = 'Alice')
AND (name = 'Bob' OR name = 'Alice' OR age >= 25)
AND (name = 'Bob' OR name = 'Alice' OR department = 'IT')
AND (name = 'Bob' OR department = 'IT' OR age >= 25)
AND (name = 'Alice' OR age >= 21)
AND (name = 'Alice' OR age >= 21 OR department = 'IT')
AND (age >= 21 OR department = 'IT' OR age >= 25)
AND (lastName = 'Smith' OR department = 'IT')
AND (lastName = 'Smith' OR age < 30)
```
**Generated REST request body:**
```json
{
"page": 0,
"limit": 100,
"and": [
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "name", "operator": "=", "value": "Alice" }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 25 }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "department", "operator": "=", "value": "IT" }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": ">=", "value": 25 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 21 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "department", "operator": "=", "value": "IT" }
],
[
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": ">=", "value": 25 }
],
[
{ "name": "lastName", "operator": "=", "value": "Smith" },
{ "name": "department", "operator": "=", "value": "IT" }
],
[
{ "name": "lastName", "operator": "=", "value": "Smith" },
{ "name": "age", "operator": "<", "value": 30 }
]
]
}
```
**Result:** Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith,
28, IT)
---
### Variant 6: Ambiguous "filters" field (requires description hint)
**Description:** REST API uses generic field name like `filters` which doesn't
clearly indicate DNF or CNF structure. The OpenAPI description MUST explicitly
state "DNF" or "CNF".
#### DNF Example
**OpenAPI specification:**
```yaml
requestBody:
content:
application/json:
schema:
properties:
filters:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: "DNF: Array of OR groups, each group contains ANDed
criteria"
```
**Example scenario:**
REST API contains the following users:
```json
[
{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR" },
{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
```
**SQL Query with nested conditions:**
```sql
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
```
**DNF Expansion:**
```
(name = 'Bob' AND age >= 21 AND lastName = 'Smith')
OR (name = 'Bob' AND age >= 21 AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND department = 'IT' AND lastName = 'Smith')
OR (name = 'Alice' AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND age >= 25 AND lastName = 'Smith')
OR (name = 'Alice' AND age >= 25 AND department = 'IT' AND age < 30)
```
**Generated REST request body:**
```json
{
"page": 0,
"limit": 100,
"filters": [
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": "<", "value": 30 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": "<", "value": 30 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 25 },
{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 25 },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": "<", "value": 30 }
]
]
}
```
**Result:** Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith,
28, IT)
#### CNF Example
**OpenAPI specification:**
```yaml
requestBody:
content:
application/json:
schema:
properties:
filters:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: "CNF: Array of AND groups, each group contains ORed
criteria"
```
**Example scenario:**
REST API contains the following users:
```json
[
{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR" },
{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
```
**SQL Query with nested conditions:**
```sql
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
```
**CNF Expansion:**
```
(name = 'Bob' OR name = 'Alice')
AND (name = 'Bob' OR name = 'Alice' OR age >= 25)
AND (name = 'Bob' OR name = 'Alice' OR department = 'IT')
AND (name = 'Bob' OR department = 'IT' OR age >= 25)
AND (name = 'Alice' OR age >= 21)
AND (name = 'Alice' OR age >= 21 OR department = 'IT')
AND (age >= 21 OR department = 'IT' OR age >= 25)
AND (lastName = 'Smith' OR department = 'IT')
AND (lastName = 'Smith' OR age < 30)
```
**Generated REST request body:**
```json
{
"page": 0,
"limit": 100,
"filters": [
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "name", "operator": "=", "value": "Alice" }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 25 }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "department", "operator": "=", "value": "IT" }
],
[
{ "name": "name", "operator": "=", "value": "Bob" },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": ">=", "value": 25 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 21 }
],
[
{ "name": "name", "operator": "=", "value": "Alice" },
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "department", "operator": "=", "value": "IT" }
],
[
{ "name": "age", "operator": ">=", "value": 21 },
{ "name": "department", "operator": "=", "value": "IT" },
{ "name": "age", "operator": ">=", "value": 25 }
],
[
{ "name": "lastName", "operator": "=", "value": "Smith" },
{ "name": "department", "operator": "=", "value": "IT" }
],
[
{ "name": "lastName", "operator": "=", "value": "Smith" },
{ "name": "age", "operator": "<", "value": 30 }
]
]
}
```
**Result:** Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith,
28, IT)
**Note:** If description doesn't contain "DNF" or "CNF", the adapter will throw
an error asking user to provide a FreeMarker template.
---
### Filter Capability Detection
The adapter analyzes the OpenAPI `requestBody` schema to detect filter
capabilities:
1. **Field names:** Presence of `or`, `and`, `where`, or `filters` fields
2. **Array structure:** Single array vs array of arrays
3. **Descriptions:** Keywords "DNF" or "CNF" in field descriptions
4. **Supported operators:** Extracted from `FilterCriterion` schema enum
If the OpenAPI specification doesn't clearly define the filter structure, or if
you need custom request formatting, you can provide a **FreeMarker template**
(.ftl file) to explicitly define the request body format.
---
### Custom OpenAPI Extensions
The adapter supports custom OpenAPI extensions to provide more control over
schema interpretation. All extensions use the `x-calcite-restapi-` prefix to
avoid conflicts with other tools.
#### Available Extensions
##### 1. `x-calcite-restapi-schema-name`
**Location:** `info` section
**Type:** string
**Description:** Specifies a custom schema name instead of using the OpenAPI
title or filename.
**Example:**
```yaml
openapi: 3.0.0
info:
title: User Management API
version: "1.0"
x-calcite-restapi-schema-name: users
```
##### 2. `x-calcite-restapi-table-name`
**Location:** operation level (per endpoint method)
**Type:** string
**Description:** Overrides the default table name (which is derived from
`operationId`).
**Example:**
```yaml
paths:
/users:
post:
operationId: getUserList
x-calcite-restapi-table-name: users
summary: Get users with filters
```
##### 3. `x-calcite-restapi-filter-capabilities`
**Location:** operation level (per endpoint method)
**Type:** object
**Description:** Explicitly defines filter capabilities instead of relying on
automatic detection.
**Properties:**
- `supportsOr` (boolean): API supports OR logic
- `supportsAnd` (boolean): API supports AND logic
- `dnfStyle` (boolean): API uses DNF (Disjunctive Normal Form) structure
- `cnfStyle` (boolean): API uses CNF (Conjunctive Normal Form) structure
- `simpleFlatStyle` (boolean): API uses simple flat filters (field names as
direct properties)
- `allowedOperators` (array of strings): List of supported operators (e.g.,
`["=", ">", "<", ">=", "<=", "!="]`)
**Example:**
```yaml
paths:
/users:
post:
operationId: users
x-calcite-restapi-filter-capabilities:
supportsOr: true
supportsAnd: true
dnfStyle: true
cnfStyle: false
simpleFlatStyle: false
allowedOperators: ["=", ">=", ">", "<", "<=", "!=", "<>"]
```
##### 4. `x-calcite-restapi-pagination`
**Location:** operation level (per endpoint method)
**Type:** object
**Description:** Defines pagination parameters for the endpoint.
**Properties:**
- `pageStart` (integer): Starting page number (default: 1)
- `pageSize` (integer): Number of records per page
**Example:**
```yaml
paths:
/users:
post:
operationId: users
x-calcite-restapi-pagination:
pageStart: 0
pageSize: 100
```
#### Complete Example with All Extensions
```yaml
openapi: 3.0.0
info:
title: User Management API
version: "1.0"
x-calcite-restapi-schema-name: users_db
servers:
- url: http://localhost:8080
paths:
/users:
post:
operationId: getUserList
x-calcite-restapi-table-name: users
x-calcite-restapi-filter-capabilities:
supportsOr: true
supportsAnd: true
dnfStyle: true
allowedOperators: ["=", ">=", ">", "<", "<=", "!="]
x-calcite-restapi-pagination:
pageStart: 1
pageSize: 1000
summary: Get users with DNF filters
requestBody:
required: true
content:
application/json:
schema:
type: object
properties:
page:
type: integer
limit:
type: integer
where:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
or:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
responses:
'200':
description: User list
content:
application/json:
schema:
$ref: '#/components/schemas/UserList'
components:
schemas:
FilterCriterion:
type: object
properties:
name:
type: string
enum: [name, age]
operator:
type: string
enum: ["=", ">=", ">", "<", "<=", "!="]
value:
oneOf:
- type: string
- type: integer
UserList:
type: object
properties:
users:
type: array
items:
$ref: '#/components/schemas/User'
User:
type: object
properties:
name:
type: string
age:
type: integer
```
was (Author: JIRAUSER305374):
For now I am still evaluating this functionality and possible design options,
so it is not implemented yet.
*REST API Filter Specification Variants*
Different REST APIs use different structures for expressing filters. The
adapter automatically detects the supported format from the OpenAPI
specification. Below are the supported variants:
h3. Variant 1: DNF with "where" (AND) + "or" (OR groups)
*Description:* REST API accepts:
* {*}where{*}: array of filter criteria joined by AND (common conditions from
first DNF group)
* {*}or{*}: array of arrays, where each inner array contains criteria joined
by AND, and outer array represents OR
*OpenAPI specification:*
{code:java}
requestBody:
content:
application/json:
schema:
properties:
where:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: List of AND filter criteria
or:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: DNF - List of OR groups
{code}
*Example scenario:*
REST API contains the following users:
{code:java}
[
\{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
\{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
\{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
\{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
\{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
\{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR"
},
\{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
{code}
*SQL Query with nested conditions:*
{code:java}
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
{code}
*DNF Expansion:*
{code:java}
(name = 'Bob' AND age >= 21 AND lastName = 'Smith')
OR (name = 'Bob' AND age >= 21 AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND department = 'IT' AND lastName = 'Smith')
OR (name = 'Alice' AND department = 'IT' AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND age >= 25 AND lastName = 'Smith')
OR (name = 'Alice' AND age >= 25 AND department = 'IT' AND age < 30)
{code}
*Generated REST request body:*
{code:java}
{
"page": 0,
"limit": 100,
"where": [
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "lastName", "operator": "=", "value": "Smith" }
],
"or": [
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": "<", "value": 30 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": "<", "value": 30 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 25 },
\{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 25 },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": "<", "value": 30 }
]
]
}
{code}
*Result:* Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith, 28,
IT)
----
h3. Variant 2: DNF with "or" only (no "where")
*Description:* REST API accepts:
* {*}or{*}: array of arrays (DNF structure), no separate "where" field
*OpenAPI specification:*
{code:java}
requestBody:
content:
application/json:
schema:
properties:
or:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: DNF - List of OR groups (each group is ANDed)
{code}
*Example scenario:*
REST API contains the following users:
{code:java}
[
\{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
\{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
\{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
\{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
\{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
\{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR"
},
\{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
{code}
*SQL Query with nested conditions:*
{code:java}
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
{code}
*DNF Expansion:*
{code:java}
(name = 'Bob' AND age >= 21 AND lastName = 'Smith')
OR (name = 'Bob' AND age >= 21 AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND department = 'IT' AND lastName = 'Smith')
OR (name = 'Alice' AND department = 'IT' AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND age >= 25 AND lastName = 'Smith')
OR (name = 'Alice' AND age >= 25 AND department = 'IT' AND age < 30)
{code}
*Generated REST request body:*
{code:java}
{
"page": 0,
"limit": 100,
"or": [
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": "<", "value": 30 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": "<", "value": 30 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 25 },
\{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 25 },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": "<", "value": 30 }
]
]
}
{code}
*Result:* Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith, 28,
IT)
----
h3. Variant 3: CNF with "where" (OR) + "and" (AND groups)
*Description:* REST API accepts:
* {*}where{*}: array of filter criteria joined by OR (conditions from first
CNF group)
* {*}and{*}: array of arrays, where each inner array contains criteria joined
by OR, and outer array represents AND
*OpenAPI specification:*
{code:java}
requestBody:
content:
application/json:
schema:
properties:
where:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: List of OR filter criteria
and:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: CNF - List of AND groups (each group contains OR
criteria)
{code}
*Example scenario:*
REST API contains the following users:
{code:java}
[
\{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
\{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
\{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
\{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
\{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
\{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR"
},
\{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
{code}
*SQL Query with nested conditions:*
{code:java}
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
{code}
*CNF Expansion:*
{code:java}
(name = 'Bob' OR name = 'Alice')
AND (name = 'Bob' OR name = 'Alice' OR age >= 25)
AND (name = 'Bob' OR name = 'Alice' OR department = 'IT')
AND (name = 'Bob' OR department = 'IT' OR age >= 25)
AND (name = 'Alice' OR age >= 21)
AND (name = 'Alice' OR age >= 21 OR department = 'IT')
AND (age >= 21 OR department = 'IT' OR age >= 25)
AND (lastName = 'Smith' OR department = 'IT')
AND (lastName = 'Smith' OR age < 30)
{code}
*Generated REST request body:*
{code:java}
{
"page": 0,
"limit": 100,
"where": [
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "name", "operator": "=", "value": "Alice" }
],
"and": [
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 25 }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "department", "operator": "=", "value": "IT" }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": ">=", "value": 25 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 21 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "department", "operator": "=", "value": "IT" }
],
[
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": ">=", "value": 25 }
],
[
\{ "name": "lastName", "operator": "=", "value": "Smith" },
\{ "name": "department", "operator": "=", "value": "IT" }
],
[
\{ "name": "lastName", "operator": "=", "value": "Smith" },
\{ "name": "age", "operator": "<", "value": 30 }
]
]
}
{code}
*Result:* Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith, 28,
IT)
----
h3. Variant 4: CNF with "and" (AND groups with OR inside)
*Description:* REST API accepts:
* {*}and{*}: array of arrays, where each inner array contains criteria joined
by OR, and outer array represents AND
*OpenAPI specification:*
{code:java}
requestBody:
content:
application/json:
schema:
properties:
and:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: CNF - List of AND groups (each group is a list of OR
criteria)
{code}
*Example scenario:*
REST API contains the following users:
{code:java}
[
\{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
\{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
\{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
\{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
\{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
\{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR"
},
\{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
{code}
*SQL Query with nested conditions:*
{code:java}
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
{code}
*CNF Expansion:*
{code:java}
(name = 'Bob' OR name = 'Alice')
AND (name = 'Bob' OR name = 'Alice' OR age >= 25)
AND (name = 'Bob' OR name = 'Alice' OR department = 'IT')
AND (name = 'Bob' OR department = 'IT' OR age >= 25)
AND (name = 'Alice' OR age >= 21)
AND (name = 'Alice' OR age >= 21 OR department = 'IT')
AND (age >= 21 OR department = 'IT' OR age >= 25)
AND (lastName = 'Smith' OR department = 'IT')
AND (lastName = 'Smith' OR age < 30)
{code}
*Generated REST request body:*
{code:java}
{
"page": 0,
"limit": 100,
"and": [
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "name", "operator": "=", "value": "Alice" }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 25 }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "department", "operator": "=", "value": "IT" }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": ">=", "value": 25 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 21 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "department", "operator": "=", "value": "IT" }
],
[
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": ">=", "value": 25 }
],
[
\{ "name": "lastName", "operator": "=", "value": "Smith" },
\{ "name": "department", "operator": "=", "value": "IT" }
],
[
\{ "name": "lastName", "operator": "=", "value": "Smith" },
\{ "name": "age", "operator": "<", "value": 30 }
]
]
}
{code}
*Result:* Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith, 28,
IT)
----
h3. Variant 5: Ambiguous "filters" field (requires description hint)
*Description:* REST API uses generic field name like *filters* which doesn't
clearly indicate DNF or CNF structure. The OpenAPI description MUST explicitly
state "DNF" or "CNF".
h4. DNF Example
*OpenAPI specification:*
{code:java}
requestBody:
content:
application/json:
schema:
properties:
filters:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: "DNF: Array of OR groups, each group contains ANDed
criteria"
{code}
*Example scenario:*
REST API contains the following users:
{code:java}
[
\{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
\{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
\{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
\{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
\{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
\{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR"
},
\{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
{code}
*SQL Query with nested conditions:*
{code:java}
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
{code}
*DNF Expansion:*
{code:java}
(name = 'Bob' AND age >= 21 AND lastName = 'Smith')
OR (name = 'Bob' AND age >= 21 AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND department = 'IT' AND lastName = 'Smith')
OR (name = 'Alice' AND department = 'IT' AND age < 30)
OR (name = 'Alice' AND age >= 25 AND lastName = 'Smith')
OR (name = 'Alice' AND age >= 25 AND department = 'IT' AND age < 30)
{code}
*Generated REST request body:*
{code:java}
{
"page": 0,
"limit": 100,
"filters": [
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": "<", "value": 30 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": "<", "value": 30 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 25 },
\{ "name": "lastName", "operator": "=", "value": "Smith" }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 25 },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": "<", "value": 30 }
]
]
}
{code}
*Result:* Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith, 28,
IT)
h4. CNF Example
*OpenAPI specification:*
{code:java}
requestBody:
content:
application/json:
schema:
properties:
filters:
type: array
items:
type: array
items:
$ref: '#/components/schemas/FilterCriterion'
description: "CNF: Array of AND groups, each group contains ORed
criteria"
{code}
*Example scenario:*
REST API contains the following users:
{code:java}
[
\{ "id": 1, "name": "Bob", "lastName": "Smith", "age": 25, "department": "IT"
},
\{ "id": 2, "name": "Alice", "lastName": "Johnson", "age": 22, "department":
"HR" },
\{ "id": 3, "name": "Bob", "lastName": "Brown", "age": 19, "department": "IT"
},
\{ "id": 4, "name": "Martin", "lastName": "Smith", "age": 35, "department":
"Sales" },
\{ "id": 5, "name": "Alice", "lastName": "Smith", "age": 28, "department":
"IT" },
\{ "id": 6, "name": "John", "lastName": "Doe", "age": 30, "department": "HR"
},
\{ "id": 7, "name": "Sarah", "lastName": "Smith", "age": 26, "department":
"IT" }
]
{code}
*SQL Query with nested conditions:*
{code:java}
SELECT * FROM users
WHERE (
(name = 'Bob' AND age >= 21)
OR
(name = 'Alice' AND (department = 'IT' OR age >= 25))
)
AND
(lastName = 'Smith' OR (department = 'IT' AND age < 30))
{code}
*CNF Expansion:*
{code:java}
(name = 'Bob' OR name = 'Alice')
AND (name = 'Bob' OR name = 'Alice' OR age >= 25)
AND (name = 'Bob' OR name = 'Alice' OR department = 'IT')
AND (name = 'Bob' OR department = 'IT' OR age >= 25)
AND (name = 'Alice' OR age >= 21)
AND (name = 'Alice' OR age >= 21 OR department = 'IT')
AND (age >= 21 OR department = 'IT' OR age >= 25)
AND (lastName = 'Smith' OR department = 'IT')
AND (lastName = 'Smith' OR age < 30)
{code}
*Generated REST request body:*
{code:java}
{
"page": 0,
"limit": 100,
"filters": [
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "name", "operator": "=", "value": "Alice" }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 25 }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "department", "operator": "=", "value": "IT" }
],
[
\{ "name": "name", "operator": "=", "value": "Bob" },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": ">=", "value": 25 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 21 }
],
[
\{ "name": "name", "operator": "=", "value": "Alice" },
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "department", "operator": "=", "value": "IT" }
],
[
\{ "name": "age", "operator": ">=", "value": 21 },
\{ "name": "department", "operator": "=", "value": "IT" },
\{ "name": "age", "operator": ">=", "value": 25 }
],
[
\{ "name": "lastName", "operator": "=", "value": "Smith" },
\{ "name": "department", "operator": "=", "value": "IT" }
],
[
\{ "name": "lastName", "operator": "=", "value": "Smith" },
\{ "name": "age", "operator": "<", "value": 30 }
]
]
}
{code}
*Result:* Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith, 28,
IT)
----
h3. Filter Capability Detection
The adapter analyzes the OpenAPI *requestBody* schema to detect filter
capabilities:
# 1. Field names
Presence of {*}or{*}, {*}and{*}, {*}where{*}, or *filters* fields.
# 2. Array structure
Single array vs array of arrays.
# 3. Descriptions
Keywords *"DNF"* or *"CNF"* in field descriptions.
# 4. Supported operators
Extracted from *FilterCriterion* schema enum.
If the OpenAPI specification doesn't clearly define the filter structure, or if
you need custom request formatting, you can provide a *FreeMarker template*
(.ftl file) to explicitly define the request body format.
> Proposal to contribute CalciteRestAPIAdapter
> --------------------------------------------
>
> Key: CALCITE-7300
> URL: https://issues.apache.org/jira/browse/CALCITE-7300
> Project: Calcite
> Issue Type: New Feature
> Reporter: Oleg Alekseev
> Priority: Minor
>
> First version
> The adapter is publicly available on GitHub:
> [https://github.com/oalekseev/CalciteRestAPIAdapter]
> What should be added:
> * Dealing with the format of the data that comes back (csv, xml, json)
> * Javadoc, tests, and user doc
> * Consider support for OpenAPI specifications in the future, as it could
> help unify and automate integration with a wide range of REST services
> Proposal to contribute here
> https://lists.apache.org/thread/jvbpz7rp7w76gqmshtz3y6bhcftk41c5
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
