Query

A Query is a composable, backend-agnostic filter over Follow The Money entities in a file, a stream or a nomenklatura statement store. The same object runs in memory, compiles to SQL, and converts to and from the Aleph / OpenAleph URL params.

Where Query sits in the toolchain

flowchart TD
    subgraph build [Build]
        NODES["M / P / G / C nodes<br/>combined with &amp; | ~"]
        DICT["nested dict"]
        RQL["RQL string<br/>(nested)"]
        PARAMS["Aleph params /<br/>URL query string"]
    end

    Q(["<b>ftmq.Query</b><br/>canonical query IR"])

    NODES -->|where| Q
    DICT -->|from_dict| Q
    Q -->|to_dict| DICT
    RQL -->|from_rql| Q
    Q -->|to_rql| RQL
    PARAMS -->|from_params / from_string| Q
    Q -->|to_params / to_string| PARAMS

    subgraph run [Run against entities]
        MEM["in-memory<br/>memory · level stores<br/>smart_read_proxies (files, streams)"]
        SQLB["SQL · Lake stores<br/>(statement tables)"]
    end

    Q -->|apply / apply_iter| MEM
    Q -->|.sql → SQLAlchemy| SQLB

    PARAMS <-->|same filter grammar| ALEPH["OpenAleph HTTP API<br/>openaleph-search<br/>SearchQueryParser"]

Building a query

A query is built from four node constructors, split by the statement-table column they target:

from ftmq import Query, M, P, G, C

q = Query().where(M(schema="Person"), P(name="Jane"))

P(country="de") matches the literal country property, while G(countries="de") matches any property of that type (nationality, jurisdiction, country, ...). Groups are keyed by name: names, dates, countries, emails, entities, ...

C accepts any key (origin, plus backend columns such as fragment, first_seen, bucket). In memory it reads entity.context[key]; in SQL the same-named statement column, where an unknown column raises QueryError at compile time. Its wire spelling is context.<name> (filter:context.origin=crawl).

schema vs schemata

M(schema=...) is an exact match, M(schemata=...) an is-a match (the schema or any descendant):

Query().where(M(schema="LegalEntity"))     # only entities whose schema is exactly LegalEntity
Query().where(M(schemata="LegalEntity"))   # LegalEntity, Company, Organization, Person, PublicBody, ...
Query().where(M(schema__in=["Person", "Company"]))   # exactly those two

Combining conditions

Nodes compose into arbitrary boolean trees with & (and), | (or) and ~ (not):

~M(schema="Organization")                              # NOT
P(name="Jane") | P(name__ilike="j%")                   # OR
M(schema="Person") & (G(countries="de") | G(countries="at"))   # nested

Query.where combines its nodes with and, as do chained .where() calls:

q = Query().where(M(schema="Payment"), P(date__gte="2024-10"))
q = q.where(G(countries="de") | G(countries="at"))

Trees are canonicalized as they combine (nested same-connector groups merge, duplicate conditions drop), so equivalent queries serialize, hash and compile identically.

What AND means

A condition holds if some statement of the entity satisfies it. In a conjunction:

Conditions that could hold of one statement simultaneously must hold of the same one.

So P(date__gte="2024-10") & P(date__lt="2024-11") is one date in October 2024. Only conjunctions join: alternatives under | stay independent, and ~ negates the joined condition ("no statement is both").

This decides which entities match, not what comes back: a matching entity is assembled from all of its statements (narrow that with select).

Value comparators

Any lookup takes a __<comparator> suffix (default equals):

• eq (default) / not - (not) equals
• gt / gte / lt / lte - greater / lower (or equal)
• in / not_in - value (not) in a list
• like / ilike - (case-insensitive) substring match; notlike / notilike negate it. % and _ in the value match literally
• startswith / endswith
• null - presence: P(deathDate__null=True) matches entities without a deathDate

# payments >= 1000 EUR in October 2024 (one date, see What AND means)
Query().where(M(schema="Payment"), P(amountEur__gte=1000), P(date__gte="2024-10"), P(date__lt="2024-11"))

# all Janes and Joes
Query().where(P(firstName__in=["Jane", "Joe"]))

# exclude a legal form
Query().where(~P(legalForm="gGmbH"))

Reverse lookups (edges)

A reverse lookup is a filter on an entity-typed value:

G(entities="entity-id")     # any entity-typed property pointing at this id (any edge)
P(director="entity-id")     # a specific edge property pointing at this id

Sorting and slicing

Sorting takes a single property reference:

q = Query().order_by(P("name"))                     # ascending
q = Query().order_by(P("date"), ascending=False)    # descending

q = Query()[:100]     # first 100
q = q[10:20]          # next 10
q = q[1]              # the 2nd result (0-indexed)

Any other reference (M("id"), G("dates"), ...), a bare string or an unknown property raises a QueryError. Wire spelling: sort=properties.name:desc in URL params, "order_by": "-properties.name" in to_dict. Entities without the sort property come last in either direction; ties and unsorted slices are ordered by entity id, so limit / offset paging is stable.

Selecting properties

select restricts which properties a matching entity is read with. It never changes which entities match.

q = Query().where(M(schemata="Document")).select(P("title"), P("fileName"))

It takes P / G references (G("countries") keeps every country-typed property); wire spelling select=properties.title, RQL select(properties.title). On a statement backend it filters the statement fetch, so a document's bodyText is not fetched; in memory the entity is pruned to the same fields.

A matching entity always comes back, even with none of the selected properties set. A projected entity has a degraded caption and incomplete edges; filters, sorting and aggregations still see the full entity.

Aggregations are documented on the aggregation page.

Running a query

Filter a stream of entities with apply / apply_iter, or pass the query to smart_read_proxies:

from ftmq import Query, M
from ftmq.io import smart_read_proxies

q = Query().where(M(dataset="my_dataset"), M(schema="Event"))

for proxy in smart_read_proxies("s3://data/entities.ftm.json", query=q):
    assert proxy.schema.name == "Event"

Or use a store view:

from ftmq.store import get_store

store = get_store("sqlite:///followthemoney.store")
view = store.default_view()

for proxy in view.query(q):
    ...

Serialization

Query serializes to a lossless nested dict, an RQL string (nested trees) and flat OpenAleph params.

data = q.to_dict()
assert Query.from_dict(data).to_dict() == data

RQL

RQL carries an arbitrarily nested & | ~ tree in one URL-friendly string (and() / or() / not() around comparisons). Parse with from_rql, emit with to_rql:

q = Query().where(M(schema="Person") & (P(name="jane") | G(countries="de")))
q.to_rql()   # "and(eq(schema,Person),or(eq(properties.name,jane),eq(group.countries,de)))"
Query.from_rql(q.to_rql()).to_dict() == q.to_dict()   # True

Fields use the wire spelling shared by every string surface: properties.<name>, group.<name>, context.<name>; meta fields (schema, dataset, id, ...) and year are bare. Any other bare name is read as a property. Comparators: eq, ne → not, lt / le / gt / ge, in, out → not_in, like / ilike.

The last four rows are aggregations and the select projection, placed next to the filter under the top-level and: and(eq(schema,Payment),aggregate(properties.beneficiary,sum(properties.amountEur))).

to_rql raises QueryError for a comparator with no RQL equivalent (null, startswith, endswith, notlike, notilike), never for the shape of a tree.

OpenAleph

OpenAleph URL params, as a MultiDict or a query string:

q = Query().where(M(schema="Person"), G(countries="de"))
q.to_string()   # "filter:group.countries=de&filter:schema=Person"
Query.from_string("filter:schema=Person&filter:group.countries=de")
Query.from_params({"filter:schema": ["Person"], "filter:group.countries": ["de"]})

exclude: negates the matching filter: (Aleph's must_not): exclude:properties.country=ru keeps every entity not holding ru, including those without a country; a repeated key negates the in list. P(country__not="ru") ("holds a country other than ru") has no param spelling; in RQL it is ne(...) (not_in is out(...)).

The param grammar is flat: to_params / to_string raise QueryError for a cross-field OR or a negated group. from_params / from_string accept every param combination.

Reference

Full reference