[
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 11/27/25 11:21 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:
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.
was (Author: JIRAUSER305374):
## 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:language=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
{code}
*Example scenario:*
REST API contains the following users:
{code:language=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" }
]
{code}
*SQL Query with nested conditions:*
{code:language=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))
{code}
*DNF Expansion:*
{code}
(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:language=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 }
]
]
}
{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:language=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)
{code}
*Example scenario:*
REST API contains the following users:
{code:language=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" }
]
{code}
*SQL Query with nested conditions:*
{code:language=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))
{code}
*DNF Expansion:*
{code}
(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:language=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 }
]
]
}
{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:language=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)
{code}
*Example scenario:*
REST API contains the following users:
{code:language=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" }
]
{code}
*SQL Query with nested conditions:*
{code:language=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))
{code}
*CNF Expansion:*
{code}
(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:language=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 }
]
]
}
{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:language=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)
{code}
*Example scenario:*
REST API contains the following users:
{code:language=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" }
]
{code}
*SQL Query with nested conditions:*
{code:language=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))
{code}
*CNF Expansion:*
{code}
(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:language=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 }
]
]
}
{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:language=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"
{code}
*Example scenario:*
REST API contains the following users:
{code:language=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" }
]
{code}
*SQL Query with nested conditions:*
{code:language=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))
{code}
*DNF Expansion:*
{code}
(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:language=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 }
]
]
}
{code}
*Result:* Returns users with id: 1 (Bob Smith, 25, IT) and 5 (Alice Smith, 28,
IT)
h4. CNF Example
*OpenAPI specification:*
{code:language=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"
{code}
*Example scenario:*
REST API contains the following users:
{code:language=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" }
]
{code}
*SQL Query with nested conditions:*
{code:language=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))
{code}
*CNF Expansion:*
{code}
(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:language=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 }
]
]
}
{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)
