Lakehouse Filtering Specification
When using the Tabulation API, row level filtering on the data is achieved using a JSON filter specification.
Each filter is either:
- A logical operator applied to one or more filter expressions (
and,or,not). - A condition operator applied to a dataset variable (
==,in).
Basic Filter Expressions Examples
{"state": {"==": 17}}
or
{"and": [..., ...]}A logical operator must use a list as the value.
A condition operator uses a value type matching the operator.
This summarizes the value types supported:
| Operator | Value Type |
== |
Numeric or String (matching schema/metadata value type) |
in |
List of values (String) |
and, or, not
|
List of filter expressions |
When constructing a condition operator, the values must match the values in the dataset Schema and Metadata (see Crunch Logical Schema ). A common mistake is to use the label. Using the following schema and metadata from the Example files.
| Schema | Metadata |
{
"state": {
"name": "state",
"type": "categorical",
"values": [
1,
...
17,
...
],
...
},
"sex": {
"name": "sex",
"type": "categorical",
"values": [
1,
2
]
},
...
} |
{
"state": {
"label": "state",
"description": "State based on self-reported zipcode",
"notes": "",
"values": [
{
"value": 1,
"label": "AL",
"scale": null,
"date": null,
"selected": null,
"missing": null,
"notes": ""
},
...
{
"value": 17,
"label": "IL",
"scale": null,
"date": null,
"selected": null,
"missing": null,
"notes": ""
},
},
"sex": {
"label": "sex",
"description": "SEX. [ENTER RESPONDENT'S SEX:]",
"notes": "",
"values": [
{
"value": 1,
"label": "Male",
"scale": null,
"date": null,
"selected": null,
"missing": null,
"notes": ""
},
{
"value": 2,
"label": "Female",
"scale": null,
"date": null,
"selected": null,
"missing": null,
"notes": ""
}
]
},
...
} |
Some example filters are:
# State: IL
{"state": {"==": 17}}
# States: AL and IL
{"and": [{"state": {"==": 1}}, {"state": {"==": 17}}]}
# Not State IL
{"not": [{"state": {"==": 17}}]}
# Females in State IL
{"and": [{"sex": {"==": 2}}, {"state": {"==": 17}}]}