`statcatlib.query`¶

Generated from the docstrings. Query Filters shows the filters in use.

query ¶

Composable filter objects for Catalog queries.

Usage:

import statcatlib.query as q

results = (
    store.query(sc.AggregateDataset)
    .filter(q.has_code("GEO", "ON"))
    .filter(q.has_dimensions(["GEO", "SEX", "AGE"]))
    .execute()
)

results = store.query(sc.BaseDataset).filter(q.distribution(format="CSV")).execute()

Filter objects are passed as positional arguments to QueryBuilder.filter(). Multiple positional filters are ANDed together. Within a single distribution() call, all keyword arguments must match the same distribution row (AND semantics). Two separate distribution() calls require two matching distribution rows.

Predicate compilation¶

Each filter implements to_sql(ctx) returning (fragment, params). The ctx is a CompileContext (extended in store.store as _QueryContext with a WHERE accumulator on top of the shared join state).

statcatlib owns CompileContext. Since sdmxlib 0.50, a downstream library passes sdmxlib a SqlRelation, never a predicate, so no compile state is shared between the two.

CompileContext ¶

State threaded through one compilation pass.

Each filter's to_sql(ctx) returns its own WHERE fragment and parameters; the context carries what has to be shared across the filters in a single query — most importantly joins, so a second filter touching the same bridge table reuses the first one's alias instead of emitting a duplicate.

Attributes:

Name	Type	Description
`row_alias`	`str`	The main table's alias in the outer query — `"d"` for `statcat.dataset d`, `"c"` for `sdmx.code c`.
`join_aliases`	`dict[str, str]`	Dedup map for repeated joins. Keyed by an implementation-defined join id (annotation type, bridge table + dimension); the value is the alias assigned the first time that join was added.
`join_sqls`	`list[str]`	Accumulated JOIN fragments, spliced between `FROM` and `WHERE` in the outer query.
`join_params`	`list[object]`	Parameters for those joins, in insertion order.
`schemas`	`dict[str, str]`	Logical-to-physical schema name map. Identity by default (`"sdmx"` -> `"sdmx"`); override to remap.
`registry`	`EditionRegistry \| None`	The edition's sdmxlib registry, when one is open. Filters that delegate to sdmxlib's path language build their relation through it — see `SdmxCodeFilter`. Left `None` by callers that compile filters without a catalog (the unit tests do), which is why it is optional rather than required: only the descent path needs it, and it says so.

schema ¶

schema(logical: str) -> str

Resolve a logical schema name to its physical SQL identifier.

next_alias ¶

next_alias(prefix: str = 'f') -> str

Return a fresh {prefix}{n} alias, unique within this pass.

Correlated subqueries would otherwise collide when two filters target the same table. The counter is per-context, so aliases are deterministic within a query and renumber for the next one. Prefixes like "ef" (exists filter) keep the SQL readable.

MapCondition ¶

A condition on a MAP(VARCHAR, VARCHAR) column.

Used inside ExistsFilter.conditions when the bridge-table column is a DuckDB MAP rather than a scalar. element_at(col, key) is emitted in the SQL instead of a plain col = ?. key is a language tag, normalised by sdmxlib.normalize_tag, so "EN" matches "en".

MapFilter ¶

Filter on a MAP column of the main dataset table.

Unlike ExistsFilter (which targets bridge tables via a subquery), MapFilter adds a direct WHERE clause against d.<column>. key is a language tag, normalised by sdmxlib.normalize_tag.

to_sql ¶

to_sql(ctx: CompileContext) -> tuple[str, list[object]]

Return (sql_fragment, params) for a WHERE clause.

FieldFilter ¶

A simple equality/containment filter on a flat column.

ExistsFilter ¶

A correlated EXISTS subquery filter.

Instances are created by the helper functions below, not directly.

Attributes:

Name	Type	Description
`table`	`str`	The table to run the EXISTS subquery against.
`conditions`	`dict[str, object]`	Column → value pairs that must all be satisfied (AND).
`join`	`str \| None`	Optional extra JOIN clause inside the subquery (e.g. to reach data_service through distributions).
`group_count`	`tuple[str, int] \| None`	When set, appends a `GROUP BY / HAVING COUNT(DISTINCT ...) = n` clause for "all-of" semantics (used by `has_dimensions`).

to_sql ¶

to_sql(ctx: CompileContext) -> tuple[str, list[object]]

Return (sql_fragment, params) for a correlated EXISTS clause.

alias_prefix is used to avoid table-alias collisions when multiple ExistsFilters are combined in one query.

SdmxCodelistFilter ¶

Filter datasets whose SDMX DSD uses codelist_urn in any dimension.

JOINs through sdmx.dataflow → sdmx.dsd_component.

to_sql ¶

to_sql(ctx: CompileContext) -> tuple[str, list[object]]

Return (sql_fragment, params) for a correlated EXISTS clause.

CodeDescent ¶

Descent over a code tree, as an argument to has_code.

Built by descendants_of. Carries what to match, never how to match it — the SQL is SdmxCodeFilter's, and the tree-walking is sdmxlib's.

Attributes:

Name	Type	Description
`root`	`str`	The ancestor code id (e.g. `"CA"`).
`hierarchy_urn`	`str \| None`	A Hierarchy URN to descend. `None` resolves the applicable Hierarchy per dataflow, and falls back to the codelist's own parent chain where none applies.
`include_self`	`bool`	Whether root matches itself. Strict by default, matching sdmxlib's `q.hcode`, whose `ancestors` excludes the node itself.

SdmxCodeFilter ¶

Filter datasets whose SDMX DSD has dimension with code in its codelist.

JOINs through sdmx.dataflow -> sdmx.dsd_component -> sdmx.code. Only matches AggregateDataset records with sdmx_dataflow_urn set.

code is either a literal code id (exact match) or a CodeDescent from descendants_of, which matches any code under a root.

Descent delegates to sl.q.hierarchy_applicability — sdmxlib's view answering "which hierarchy applies to this component, in this dataflow's context", with scoped associations outranking unscoped ones at rank == 1. The projected relation is a sdmxlib.SqlRelation, spliced here as a LEFT JOIN and matched on dataflow_urn. It stays lazy: the descendant set is never enumerated client-side, and neither is the candidate dataflow list.

What statcatlib still decides is the fallback. The applicability view has a row only where a HierarchyAssociation exists; where none does, this filter reads the codelist's intrinsic parent chain from sdmx.code.ancestor_ids. That is a statcatlib policy — "when nothing says otherwise, use the codelist's own tree" — rather than an SDMX rule, which is why it lives here and the rest does not.

to_sql ¶

to_sql(ctx: CompileContext) -> tuple[str, list[object]]

Return (sql_fragment, params) for a correlated EXISTS clause.

SdmxDimensionFilter ¶

Filter datasets whose SDMX DSD has ALL of the listed dimensions.

JOINs through sdmx.dataflow → sdmx.dsd_component. Only matches AggregateDataset records with sdmx_dataflow_urn set.

to_sql ¶

to_sql(ctx: CompileContext) -> tuple[str, list[object]]

Return (sql_fragment, params) for a correlated EXISTS clause.

label_norm_sql ¶

label_norm_sql(expr: str) -> str

Wrap a SQL expr in the accent + punctuation fold used for label search.

descendants_of ¶

descendants_of(
    root: str,
    *,
    hierarchy: HierarchyLike | None = None,
    include_self: bool = False,
) -> CodeDescent

Match any code beneath root in a code tree.

Pass the result to has_code. within is the one-call shortcut and is usually what you want::

q.has_code("REF_AREA", q.descendants_of("CA"))
q.within("REF_AREA", "CA")  # the same filter

Parameters:

Name	Type	Description	Default
`root`	`str`	The ancestor code id (e.g. `"CA"`).	required
`hierarchy`	`HierarchyLike \| None`	A Hierarchy URN string, `sdmxlib.Hierarchy`, or `Ref[Hierarchy]` to descend. `None` resolves the applicable Hierarchy per dataflow through `sdmx.hierarchy_applicability`, falling back to the codelist's own parent chain where none applies.	`None`
`include_self`	`bool`	Whether root matches itself. Strict by default, matching sdmxlib's `q.hcode.ancestors`, which excludes the node itself.	`False`

Returns:

Type	Description
`CodeDescent`	A `CodeDescent` carrying root, the resolved hierarchy URN and
`CodeDescent`	the self-inclusion flag. Pass it to `has_code`.

has_code ¶

has_code(
    dimension: str,
    code: str | CodeDescent,
    *,
    codelist_urn: str | None = None,
) -> SdmxCodeFilter

Filter datasets whose SDMX DSD has dimension with code in its codelist.

JOINs through sdmx.dataflow -> sdmx.dsd_component -> sdmx.code. Only applies to AggregateDataset records ingested via SDMX.

Parameters:

Name	Type	Description	Default
`dimension`	`str`	The dimension ID to match (e.g. `"GEO"`).	required
`code`	`str \| CodeDescent`	Either a literal code id for exact match (e.g. `"ON"`) or a `CodeDescent` from `descendants_of`, which matches any code under a root. Descent stays server-side: the descendant set is never enumerated first.	required
`codelist_urn`	`str \| None`	Optional codelist URN to disambiguate dimensions that share the same ID across different DSDs. When omitted, matches any codelist.	`None`

Examples:

Exact match — find datasets that use the "ON" code on GEO::

store.query(sc.AggregateDataset).filter(q.has_code("GEO", "ON"))

Hierarchy descent against an explicit Hierarchy — find datasets that use any descendant of "CA" in H_GEO_ADMIN::

store.query(sc.AggregateDataset).filter(
    q.has_code("REF_AREA", q.descendants_of("CA", hierarchy=h_admin))
)

Default hierarchy resolution — when hierarchy= is omitted, the applicable Hierarchy is resolved per dataflow via sdmx.hierarchy_applicability (scoped associations outrank unscoped ones), falling back to the codelist's parent chain when no association applies::

store.query(sc.AggregateDataset).filter(q.has_code("REF_AREA", q.descendants_of("CA")))

Returns:

Type	Description
`SdmxCodeFilter`	An `SdmxCodeFilter` for `QueryBuilder.filter`.

within ¶

within(
    dimension: str,
    root_code: str,
    *,
    hierarchy: HierarchyLike | None = None,
    include_self: bool = False,
    codelist_urn: str | None = None,
) -> SdmxCodeFilter

Filter datasets whose DSD has dimension with a code beneath root_code.

The common geography-descent case, in one call. Equivalent to::

q.has_code(
    dimension,
    q.descendants_of(root_code, hierarchy=hierarchy, include_self=include_self),
    codelist_urn=codelist_urn,
)

When hierarchy is None, the applicable Hierarchy is resolved per dataflow via sdmx.hierarchy_applicability (scoped associations outrank unscoped ones), falling back to the codelist's intrinsic parent chain when no association applies.

Parameters:

Name	Type	Description	Default
`dimension`	`str`	The dimension ID (e.g. `"REF_AREA"`).	required
`root_code`	`str`	The ancestor code id (e.g. `"ON"`).	required
`hierarchy`	`HierarchyLike \| None`	A Hierarchy URN string, `sdmxlib.Hierarchy`, or `Ref[Hierarchy]`. `None` triggers per-dataflow resolution.	`None`
`include_self`	`bool`	Whether root_code matches itself. Strict by default — a codelist containing `ON` and nothing under it does not match `within("REF_AREA", "ON")`. This matches sdmxlib's `q.hcode.ancestors`, which excludes the node itself.	`False`
`codelist_urn`	`str \| None`	Optional codelist URN to disambiguate same-named dimensions across DSDs.	`None`

Example

store.query(sc.AggregateDataset).filter( q.within("REF_AREA", "ON", hierarchy=h_geo_statistical_urn) )

Returns:

Type	Description
`SdmxCodeFilter`	An `SdmxCodeFilter` for `QueryBuilder.filter` — the same object
`SdmxCodeFilter`	`has_code` would return for the equivalent `descendants_of`.

has_dimensions ¶

has_dimensions(
    dims: list[str],
) -> SdmxDimensionFilter | _NoOpFilter

Filter datasets whose SDMX DSD has ALL of the listed dimensions.

JOINs through sdmx.dataflow → sdmx.dsd_component. An empty list matches all datasets.

Parameters:

Name	Type	Description	Default
`dims`	`list[str]`	The dimension ids every matching DSD must have.	required

Returns:

Type	Description
`SdmxDimensionFilter \| _NoOpFilter`	The filter, or one that matches everything when dims is empty.

Example

store.query(sc.AggregateDataset).filter(q.has_dimensions(["GEO", "SEX"])).execute()

distribution ¶

distribution(**kwargs: object) -> ExistsFilter

Filter datasets that have a distribution matching all kwargs.

Supported keys: format, media_type, and endpoint_url, which is resolved via a LEFT JOIN to the distribution's data_service. Any other key raises ValueError.

All kwargs within one call must match the same distribution row. For "at least two separate distributions" semantics, call this function twice::

.filter(q.distribution(format="CSV"))
.filter(q.distribution(format="JSON"))

Parameters:

Name	Type	Description	Default
`**kwargs`	`object`	Column values one distribution must all match: `format`, `media_type` or `endpoint_url`.	`{}`

Returns:

Type	Description
`ExistsFilter`	An EXISTS filter over the dataset's distributions.

Example

store.query(sc.BaseDataset).filter(q.distribution(media_type="text/csv")).execute()

has_service ¶

has_service(**kwargs: object) -> ExistsFilter

Filter datasets served by a data service matching all kwargs.

Supported key: endpoint_url. Any other key raises ValueError.

Parameters:

Name	Type	Description	Default
`**kwargs`	`object`	Column values the data service must all match.	`{}`

Returns:

Type	Description
`ExistsFilter`	An EXISTS filter over the dataset's distributions and their services.

Example

store.query(sc.BaseDataset).filter( q.has_service(endpoint_url="https://example.com/sdmx") ).execute()

publishes ¶

publishes(
    *inner_filters: PositionalFilter, **kwargs: object
) -> ExistsFilter

Filter Agents that publish datasets matching the given criteria.

Pass ExistsFilter objects (e.g. has_code, has_dimensions) as positional arguments, and/or dataset-level conditions as keyword arguments (kind, status).

Parameters:

Name	Type	Description	Default
`*inner_filters`	`PositionalFilter`	Filters a published dataset must match.	`()`
`**kwargs`	`object`	Dataset columns a published dataset must match.	`{}`

Returns:

Type	Description
`ExistsFilter`	An EXISTS filter over the agent's datasets.

Example

Agents that publish aggregate datasets about Ontario¶

store.query(sc.Agent).filter( q.publishes(q.has_code("GEO", "ON"), kind="aggregate") ).all()

serves ¶

serves(
    *inner_filters: PositionalFilter, **kwargs: object
) -> ExistsFilter

Filter DataServices that serve datasets matching the given criteria.

Pass ExistsFilter objects as positional arguments and/or dataset-level conditions as keyword arguments (kind, status).

Parameters:

Name	Type	Description	Default
`*inner_filters`	`PositionalFilter`	Filters a served dataset must match.	`()`
`**kwargs`	`object`	Dataset columns a served dataset must match.	`{}`

Returns:

Type	Description
`ExistsFilter`	An EXISTS filter over the service's datasets.

Example

DataServices that serve datasets with a GEO dimension¶

store.query(sc.DataService).filter(q.serves(q.has_dimensions(["GEO"]))).all()

has_spatial ¶

has_spatial(
    label: str, *, lang: str = "en"
) -> ExistsFilter

Filter datasets that have a spatial coverage matching label.

The label is matched against the coverage's own label and, for a region stored by identifier, against the label of its concept, so has_spatial("Terre-Neuve-et-Labrador", lang="fr") finds a CKAN dataset that carries the region code 10. CKAN region text that is not a code carries no language and is stored under und, so it matches with lang="und". To filter on a spatial facet key that is a URI, use has_spatial_uri.

Parameters:

Name	Type	Description	Default
`label`	`str`	The spatial coverage label, matched exactly.	required
`lang`	`str`	The language of label.	`'en'`

Returns:

Type	Description
`ExistsFilter`	An EXISTS filter over the dataset's spatial coverage.

Example

store.query(sc.BaseDataset).filter(q.has_spatial("Canada")).execute()

has_spatial_uri ¶

has_spatial_uri(uri: str) -> ExistsFilter

Filter datasets that have a spatial coverage identified by uri.

A spatial facet key is a region's URI when it has one (Catalog.facets), so a key from facets("spatial") filters back to the datasets it counts.

Parameters:

Name	Type	Description	Default
`uri`	`str`	The region's URI, such as a CKAN `geographic_region` concept URI (GH-83), matched exactly.	required

Returns:

Type	Description
`ExistsFilter`	An EXISTS filter over the dataset's spatial coverage.

Example

key = next(iter(store.facets("spatial")["spatial"])) store.query(sc.BaseDataset).filter(q.has_spatial_uri(key)).execute()

has_keyword ¶

has_keyword(
    keyword: str, *, lang: str = "en"
) -> ExistsFilter

Filter datasets that have a keyword matching keyword.

Parameters:

Name	Type	Description	Default
`keyword`	`str`	The keyword, matched exactly.	required
`lang`	`str`	The language of keyword.	`'en'`

Returns:

Type	Description
`ExistsFilter`	An EXISTS filter over the dataset's keywords.

Example

store.query(sc.BaseDataset).filter(q.has_keyword("employment")).execute()

title_contains ¶

title_contains(text: str, *, lang: str = 'en') -> MapFilter

Filter datasets whose title contains text (case-insensitive).

Parameters:

Name	Type	Description	Default
`text`	`str`	The text to find, anywhere in the title.	required
`lang`	`str`	Which language's title to search.	`'en'`

Returns:

Type	Description
`MapFilter`	A filter on the dataset's title in lang.

Example

store.query(sc.BaseDataset).filter(q.title_contains("GDP")).execute()

description_contains ¶

description_contains(
    text: str, *, lang: str = "en"
) -> MapFilter

Filter datasets whose description contains text (case-insensitive).

Parameters:

Name	Type	Description	Default
`text`	`str`	The text to find, anywhere in the description.	required
`lang`	`str`	Which language's description to search.	`'en'`

Returns:

Type	Description
`MapFilter`	A filter on the dataset's description in lang.

Example

store.query(sc.BaseDataset).filter(q.description_contains("exchange")).execute()

statcatlib.query¶

query ¶

Predicate compilation¶

CompileContext ¶

schema ¶

next_alias ¶

MapCondition ¶

MapFilter ¶

to_sql ¶

FieldFilter ¶

ExistsFilter ¶

to_sql ¶

SdmxCodelistFilter ¶

to_sql ¶

CodeDescent ¶

SdmxCodeFilter ¶

to_sql ¶

SdmxDimensionFilter ¶

to_sql ¶

label_norm_sql ¶

descendants_of ¶

has_code ¶

within ¶

has_dimensions ¶

distribution ¶

has_service ¶

publishes ¶

Agents that publish aggregate datasets about Ontario¶

serves ¶

DataServices that serve datasets with a GEO dimension¶

has_spatial ¶

has_spatial_uri ¶

has_keyword ¶

title_contains ¶

description_contains ¶

`statcatlib.query`¶