[
https://issues.apache.org/jira/browse/CALCITE-7300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18041036#comment-18041036
]
Oleg Alekseev commented on CALCITE-7300:
----------------------------------------
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: 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 2: 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 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:**
```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 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:**
```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 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".
#### 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.
> 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)
